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 DB9093A40D7 for ; Tue, 9 Nov 2004 00:03:49 +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 90385-01 for ; Tue, 9 Nov 2004 00:03:45 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 9939D3A408C for ; Tue, 9 Nov 2004 00:03:46 +0000 (GMT) Received: (qmail 7579 invoked from network); 9 Nov 2004 01:04:02 +0100 Received: from unknown (HELO musicbox) (boutiquenumerique-lists@192.168.0.2) by boutiquenumerique.com with SMTP; 9 Nov 2004 01:04:02 +0100 Date: Tue, 09 Nov 2004 01:04:25 +0100 To: pgsql-performance@postgresql.org Subject: Re: vacuum analyze slows sql query References: <20041108231951.23927.qmail@web52105.mail.yahoo.com> From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <20041108231951.23927.qmail@web52105.mail.yahoo.com> User-Agent: Opera M2/7.54 (Linux, build 751) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/151 X-Sequence-Number: 9105 >> Lets say for a second that you manage to trick it into using index scan, >> and then you actually call the function with one of the values that >> returns 1,000s of rows. Probably it will take 10-100 times longer than >> if it used a seq scan. > I don't know if it matters (I suspect that it does) but I am using > LIMIT 1 in the sub-query/stored function. All I need is one single > row meeting any of the criteria laid out in the stored procedure to > establish an offer_id is "pending". So, in your case if you LIMIT the index scan will always be fast, and the seq scan will be catastrophic, because you don't need to retrieve all the rows, but just one. (IMHO the planner screws these LIMIT clauses becauses it expects the data to be randomly distributed in the first page while in real life it's not). You could use EXIST to test the existence of a subquery (after all, thats its purpose), or you could : When SELECT ... FROM table WHERE stuff=value LIMIT 1 obstinately uses a seq scan, spray a little order by : When SELECT ... FROM table WHERE stuff=value ORDER BY stuff LIMIT 1 the ORDER BY will make the planner think "I could use the index to order"...