Simon Riggs wrote:
> Yes, dead on. Thank you for this elegant summary. The main idea was
> originally Hannu Krosing's, I believe, with suggestion from Tom to
> enhance the partial index machinery to this end.
> So a query such as
>       select * from pm where dkey = 25000
> will have an EXPLAIN that completely ignores p1 and p3, since these can
> be provably excluded from the plan without effecting the result.
> I see the "no syntax" version as the first step towards additional
> functionality that would require additional syntax.

OK, makes sense.

> > Oh, why would someone want to set enable_constraint_exclusion to false?
> The included functionality performs the exclusion at plan time. If a
> query was prepared for later execution, it *could* return the wrong
> answer when the plan was executed at a later time since plans are not
> invalidated when constraints change. So, in general, this should be set
> to false except for circumstances where the user can guarantee no such
> mistake would be made.

Ah, so there is a small additional restriction (changing constraints on
planned queries) that this would affect.

> > You had a few questions:
> > 
> > > Main questions:
> > > 1. How should we handle the case where *all* inherited relations are
> > > excluded? (This is not currently covered in the code).
> > 
> > I assume this means we don't return any rows.  Why it is an issue?
> A code question only. No issue, just how should the code look?

Ah, so there is no sequential/index scan on anything then.  Don't we
have another case like this in the code?

> > > 2. Should this feature be available for all queries or just inherited
> > > relations?
> > 
> > I don't see why other queries should not use this.  Our TODO already
> > has:
> > 
> >     * Use CHECK constraints to influence optimizer decisions
> >     
> >       CHECK constraints contain information about the distribution of values
> >       within the table. This is also useful for implementing subtables where
> >       a tables content is distributed across several subtables.
> > 
> > and this looks like what you are doing.  However, again, I see the
> > constraint as just informing whether there might be any rows in the
> > table.  Am I missing something?  Are you thinking views with UNION could
> > benefit from this?
> In general, it seems you might want this. In normal use check
> constraints tend to be on minor columns, not key columns. Queries that
> would be provably able to exclude tables based upon this would be
> strange queries.
> i.e. 
> select count(distinct item_pk) from warehouse where quantity < 0 
> is not a very common query. So we would pay the overhead of checking for
> exclusion for all queries when only a few wierd ones would ever take
> advantage of it. Sounds like a poor trade-off to me.
> IMHO, the only time you might expect to see benefit is when you have
> many similar tables that are partitioned by design into pieces that lend
> themselves to exclusion. If you specifically designed a set of tables
> and used UNION to bring them together, then I can see that you would
> want it then also.... but is there any benefit in supporting two
> different ways of achieving the same basic design: partitioned table.

I think you are probably right with the GUC at this stage.  As the
feature is expanded in 8.2, we can then turn it on automatically and
remove the GUC.

  Bruce Momjian                        |               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to