Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 5AC709FB402; Fri, 13 Apr 2007 20:24:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 30677-05; Fri, 13 Apr 2007 20:24:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 79E759FB3F9; Fri, 13 Apr 2007 20:24:13 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-221-161.bk12-dsl.surnet.cl [201.221.221.161]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l3DNOEt6016700; Fri, 13 Apr 2007 16:24:15 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id DC2E1329F6; Fri, 13 Apr 2007 19:24:09 -0400 (CLT) Date: Fri, 13 Apr 2007 19:24:09 -0400 From: Alvaro Herrera To: Tom Lane Cc: pgsql-hackers@postgreSQL.org, PostgreSQL Performance , Steve Subject: Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance) Message-ID: <20070413232409.GI27255@alvh.no-ip.org> Mail-Followup-To: Tom Lane , pgsql-hackers@postgreSQL.org, PostgreSQL Performance , Steve References: <10117.1176420763@sss.pgh.pa.us> <10355.1176422445@sss.pgh.pa.us> <10503.1176423606@sss.pgh.pa.us> <12360.1176436194@sss.pgh.pa.us> <26399.1176504501@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <26399.1176504501@sss.pgh.pa.us> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Fri, 13 Apr 2007 16:24:16 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/302 X-Sequence-Number: 24179 Tom Lane wrote: > One idea I thought about was to sort by index scan cost, using > selectivity only as a tiebreaker for cost, rather than the other way > around as is currently done. This seems fairly plausible because > indexscans that are cheaper than other indexscans likely return fewer > rows too, and so selectivity is already accounted for to some extent --- > at least you can't have an enormously worse selectivity at lower cost, > whereas Steve's example proves it doesn't work the other way. But I'm > worried about breaking the reasoning about redundant indexes that's > mentioned in the comments. > > Another alternative that would respond to the immediate problem is to > maintain the current sort order, but as we come to each index, consider > using that one alone, and throw away whatever AND we might have built up > if that one alone beats the AND-so-far. This seems more conservative, > as it's unlikely to break any cases that work well now, but on the other > hand it feels like plastering another wart atop a structure that's > already rather rickety. > > Has anyone got any thoughts about the best way to do this? How about doing both: sort the index by index scan cost; then pick the first index on the list and start adding indexes when they lower the cost. When adding each index, consider it by itself against the already stacked indexes. If the cost is lower, put this index at the top of the list, and restart the algorithm (after the sorting step of course). I think the concern about condition redundancy should be attacked separately. How about just comparing whether they have common prefixes of conditions? I admit I don't understand what would happen with indexes defined like (lower(A), B, C) versus (A, B) for example. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support