WIP: cross column correlation ...

From: PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
To: pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Boszormenyi Zoltan <zb(at)cybertec(dot)at>
Subject: WIP: cross column correlation ...
Date: 2011-02-22 19:48:47
Message-ID: 95E9B329-CE40-495D-B954-B1FB9B6ADDA4@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

hello everbody,

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:

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.

many thanks,


Attachment Content-Type Size
cross-column-v5.patch application/octet-stream 38.2 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Dimitri Fontaine 2011-02-22 21:18:43 Re: How to extract a value from a record using attnum or attname?
Previous Message Daniel Farina 2011-02-22 19:45:30 Re: Sync Rep v17