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 email@example.com | (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