On Sat, 2005-07-02 at 15:56 -0400, Bruce Momjian wrote: > Seems you have managed to combine inheritance, check constraints, and > partial index into table partitioning. It is nice it requires no new > syntax. Here is an example from your tests: > > DROP TABLE pm cascade; > CREATE TABLE pm > ( dkey INT NOT NULL > ); > > CREATE TABLE p1 ( CHECK (dkey BETWEEN 10000 AND 19999)) INHERITS (pm); > CREATE TABLE p2 ( CHECK (dkey BETWEEN 20000 AND 29999)) INHERITS (pm); > CREATE TABLE p3 ( CHECK (dkey BETWEEN 30000 AND 39999)) INHERITS (pm); > > So, in this case, a SELECT from pm would pull from the three base > tables, and those tables can be located on different tablespaces, and > the backend will only look in child tables that might contain rows basd > on the check constraints. It is that last phrase that is the new > functionality here.
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. > 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. > 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? > > 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. > > 3. And should we extend RelOptInfo to include constraint information? > > Is the problem that you have to do multiple lookups without it? > > 4. Do we want to integrate the test suite also? > > No, once this thing works, I don't see it getting broken frequently. We > usually don't test the optimizer, but we could add a single test for one > row in each table. > > > 5. Presumably a section under Performance tips would be appropriate to > > document this feature? (As well as section in run-time parameters). > > Yep. > > I am surprised no one else has commented on it, which I think means your > code is ready for the queue. Do you want to adjust it based on this > feedback or should I apply and you can adjust it later. I think that it means I did not explain myself well enough. :-) Best Regards, Simon Riggs ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq