X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C140D55E75 for ; Thu, 17 Mar 2005 18:15:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26235-02 for ; Thu, 17 Mar 2005 18:15:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 43AA156854 for ; Thu, 17 Mar 2005 18:15:37 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2HIFWch025833; Thu, 17 Mar 2005 13:15:32 -0500 (EST) To: Manfred Koizar Cc: David Brown , pgsql-performance@postgresql.org Subject: Re: multi-column index In-reply-to: References: <42385A1B.9090305@ibcp.fr> <200503161009.24713.josh@agliodbs.com> <4238CE87.1040904@bigpond.net.au> <19588.1111029553@sss.pgh.pa.us> Comments: In-reply-to Manfred Koizar message dated "Thu, 17 Mar 2005 09:51:36 +0100" Date: Thu, 17 Mar 2005 13:15:32 -0500 Message-ID: <25832.1111083332@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/365 X-Sequence-Number: 11070 Manfred Koizar writes: > On Wed, 16 Mar 2005 22:19:13 -0500, Tom Lane wrote: >> calculate the correlation explicitly for each index > May be it's time to revisit an old proposal that has failed to catch > anybody's attention during the 7.4 beta period: > http://archives.postgresql.org/pgsql-hackers/2003-08/msg00937.php > I'm not sure I'd store index correlation in a separate table today. > You've invented something better for functional index statistics, AFAIR. Well, the original motivation for calculating correlations on columns was that historically, you didn't need to re-ANALYZE after creating an index: the stats on the base table were already in place. So the idea was to have the correlations already available whether or not the index existed. This works fine for plain indexes on single columns ;-). We didn't realize (or at least I didn't) how poorly the per-column stats apply to multi-column indexes. I am coming around to the view that we really do need to calculate index-specific correlation numbers, and that probably does need a special table ... or maybe better, add a column to pg_index. The column in pg_statistic is useless and should be removed, because there isn't any need for per-column correlation. Now, as to the actual mechanics of getting the numbers: the above link seems to imply reading the whole index in index order. Which is a hugely expensive proposition for a big index, especially one that's grown rather than been built recently --- the physical and logical orderings of the index will be different. (Hm, maybe we need a stat about the extent of disorder within the index itself?) We need a way to get the number from a small sample of pages. The idea I was toying with was to recalculate the index keys for the sample rows that ANALYZE already acquires, and then compare/sort those. This is moderately expensive CPU-wise though, and it's also not clear what "compare/sort" means for non-btree indexes. If we could get a correlation estimate by probing only a small fraction of the index pages, that would work, but in a disordered index I'm not sure how you figure out what you're looking at. regards, tom lane