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 8408711F6B32 for ; Fri, 7 Apr 2006 11:34:56 -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 46185-03 for ; Fri, 7 Apr 2006 11:34:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 6F2BB11F6B0F for ; Fri, 7 Apr 2006 11:34:53 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k37EYrKV011740; Fri, 7 Apr 2006 10:34:53 -0400 (EDT) To: Brian Herlihy cc: pgsql-performance@postgresql.org Subject: Re: Query planner is using wrong index. In-reply-to: <20060407064108.49532.qmail@web52315.mail.yahoo.com> References: <20060407064108.49532.qmail@web52315.mail.yahoo.com> Comments: In-reply-to Brian Herlihy message dated "Fri, 07 Apr 2006 16:41:08 +1000" Date: Fri, 07 Apr 2006 10:34:53 -0400 Message-ID: <11739.1144420493@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.132 required=5 tests=[AWL=0.132] X-Spam-Score: 0.132 X-Spam-Level: X-Archive-Number: 200604/129 X-Sequence-Number: 18213 Brian Herlihy writes: > Before I go, I have a question - From discussions on the Postgresql irc > channel, and from reading the TODO list on the website, I am under the > impression that there are no plans to allow optimizer hints, such as "use index > table_pkey". Is this really true? I personally don't think it's a good idea: the time spent in designing, implementing, and maintaining a usable hint system would be significant, and IMHO the effort is better spent on *fixing* the optimizer problems than working around them. There are also good arguments about how hints wouldn't be future-proof --- for instance, the recent addition of bitmap indexscan capability would've obsoleted an awful lot of hints, had anyone had any on their queries. We'd then be faced with either turning off the hints or being forced by them to adopt inferior plans. The direction I'd like to see us go to solve your problem is maintaining cross-column statistics. It's not practical to store joint stats for every set of columns, but the existence of an index on (p2,p3) ought to cue us that p2/p3 stats would be worth having. regards, tom lane