Re: Constraint Exclusion (Partitioning) - Initial Review - Mailing list pgsql-patches

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                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073