X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E03E311F6936 for ; Thu, 6 Apr 2006 08:47:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28278-05 for ; Thu, 6 Apr 2006 08:47:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx1.ipf.is (benz.ipf.is [85.197.192.156]) by postgresql.org (Postfix) with ESMTP id 5916B11F6972 for ; Thu, 6 Apr 2006 08:47:41 -0300 (ADT) Received: (qmail 15705 invoked by uid 1010); 6 Apr 2006 11:46:43 +0000 Received: from 85.197.216.186 by benz (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88/1235. spamassassin: 3.1.1. perlscan: 1.25-st-qms. Clear:RC:1(85.197.216.186):SA:0(-4.1/4.5):. Processed in 0.296651 secs); 06 Apr 2006 11:46:43 -0000 X-Antivirus-IPF-Mail-From: gnari@hive.is via benz X-Antivirus-IPF: 1.25-st-qms (Clear:RC:1(85.197.216.186):SA:0(-4.1/4.5):. Processed in 0.296651 secs Process 15698) Received: from dsl-216-186.hive.is (HELO ?192.168.1.34?) (85.197.216.186) by mx1.ipf.is with SMTP; 6 Apr 2006 11:46:43 +0000 Subject: Re: Query planner is using wrong index. From: Ragnar To: Brian Herlihy Cc: pgsql-performance@postgresql.org In-Reply-To: <20060406092711.8725.qmail@web52315.mail.yahoo.com> References: <20060406092711.8725.qmail@web52315.mail.yahoo.com> Content-Type: text/plain Date: Thu, 06 Apr 2006 11:49:56 +0000 Message-Id: <1144324196.32289.91.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200604/107 X-Sequence-Number: 18191 On fim, 2006-04-06 at 19:27 +1000, Brian Herlihy wrote: > --- Ragnar wrote: > > > On fim, 2006-04-06 at 12:35 +1000, Brian Herlihy wrote: > > ... > > > PRIMARY KEY (p1, p2, p3) ... > > > > > > I have also created an index on (p2, p3), as some of my lookups are on > > > these only. ... > > > db=# explain select * from t where p2 = 'fairly_common' and p3 = > > > 'fairly_common'; > > please show us an actual EXPLAIN ANALYZE > > > I would like the query planner to use the primary key for all of these > > lookups. > > > > have you tested to see if it would actualy be better? > > > Yes, the primary key is far better. I gave it the ultimate test - I dropped > the (p2, p3) index. It's blindingly fast when using the PK, I have problems understanding exactly how an index on (p1,p2,p3) can be faster than and index on (p2,p3) for a query not involving p1. can you demonstrate this with actual EXPLAIN ANALYZES ? something like: EXPLAIN ANALYZE select * from t where p2 = ? and p3 = ?; BEGIN; DROP INDEX p2p3; EXPLAIN ANALYZE select * from t where p2 = ? and p3 = ?; ROLLBACK; maybe your p2p3 index needs REINDEX ? > My options seem to be > - Fudge the analysis results so that the selectivity estimate changes. I > have tested reducing n_distinct, but this doesn't seem to help. > - Combine the columns into one column, allowing postgres to calculate the > combined selectivity. > - Drop the (p2, p3) index. But I need this for other queries. > > None of these are good solutions. So I am hoping that there is a better way to > go about this! I think we must detemine exactly what the problem is before devising complex solutions gnari