we have spent some time in finally attacking cross column correlation. as this is an issue which keeps bugging us for a couple of applications (some years). this is a WIP patch which can do:
special cross column correlation specific syntax:
CREATE CROSS COLUMN STATISTICS ON tablename (field, ...);
DROP CROSS COLUMN STATISTICS ON tablename (field, ...);
we use specific syntax because we simply cannot keep track of all possible correlations in the DB so the admi can take care of things explicitly. some distant day somebody might want to write a mechanism to derive the desired stats automatically but this is beyond the scope of our project for now.
as far as the patch is concerned:
it is patched nicely into clauselist_selectivity(), but has some rough edges, even when a cross-col stat is found, the single col selectivities are still counted ( = lovering the selectivity even more), this is a TODO.
this patch adds the grammar and the start of planner integration with a static selectivity value for now, the previous discussion about cross-column statistics can be continued and perhaps comes to fruition soon.
how does it work? we try to find suitable statistics for an arbitrary length list of conditions so that the planner can use it directly rather than multiplying all the selectivities. this should make estimates a lot more precise.
the current approach can be extended to work with expressions and well as "straight" conditions.
goal: to make cross column correlation work for 9.2 ...
the purpose of this mail is mostly to get the race for a patch going and to see if the approach as such is reasonable / feasible.
pgsql-hackers by date
|Next:||From: Dimitri Fontaine||Date: 2011-02-22 21:18:43|
|Subject: Re: How to extract a value from a record using attnum or attname?|
|Previous:||From: Daniel Farina||Date: 2011-02-22 19:45:30|
|Subject: Re: Sync Rep v17|