Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 66F0A64FC5E for ; Thu, 20 Nov 2008 21:35:39 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 63266-08 for ; Thu, 20 Nov 2008 21:35:36 -0400 (AST) X-Greylist: delayed 00:25:58.502716 by SQLgrey-1.7.6 Received: from mail.gransy.com (nathalia.gransy.com [82.208.29.194]) by mail.postgresql.org (Postfix) with ESMTP id DBEF764FC46 for ; Thu, 20 Nov 2008 21:35:35 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id 7FEB47AC00D; Fri, 21 Nov 2008 02:09:32 +0100 (CET) Received: from mail.gransy.com ([127.0.0.1]) by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id x+INxW6BHo2G; Fri, 21 Nov 2008 02:09:32 +0100 (CET) Received: from [192.168.1.1] (ip-89-102-139-23.karneval.cz [89.102.139.23]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gransy.com (Postfix) with ESMTP id 4EFC87AC008; Fri, 21 Nov 2008 02:09:32 +0100 (CET) Message-ID: <49260A50.9040904@fuzzy.cz> Date: Fri, 21 Nov 2008 02:09:36 +0100 From: Tomas Vondra User-Agent: Thunderbird 2.0.0.9 (X11/20080213) MIME-Version: 1.0 To: Mark Roberts CC: Matthew Wakeling , pgsql-performance@postgresql.org Subject: Re: Performance and IN clauses References: <64433.89.102.139.23.1227026325.squirrel@sq.gransy.com> <1227050965.30763.7754.camel@localhost> In-Reply-To: <1227050965.30763.7754.camel@localhost> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/178 X-Sequence-Number: 31909 Mark Roberts napsal(a): > On Tue, 2008-11-18 at 17:38 +0100, tv@fuzzy.cz wrote: >> I bet there is no 'critical' length - this is just another case of >> index >> scan vs. seqscan. The efficiency depends on the size of the table / >> row, >> amount of data in the table, variability of the column used in the IN >> clause, etc. >> >> Splitting the query with 1000 items into 10 separate queries, the >> smaller >> queries may be faster but the total time consumed may be actually >> higher. >> Something like >> >> 10 * (time of small query) + (time to combine them) > (time of large >> query) >> >> If the performance of the 'split' solution is actually better than the >> original query, it just means that the planner does not use index scan >> when it actually should. That means that either >> >> (a) the planner is not smart enough >> (b) it has not current statistics of the table (run ANALYZE on the >> table) >> (c) the statistics are not detailed enough (ALTER TABLE ... SET >> STATICTICS) >> (d) the cost variables are not set properly (do not match the hardware >> - >> decreate index scan cost / increase seq scan cost) >> >> regards >> Tomas > > I know that it's much faster (for us) to run many smaller queries than > one large query, and I think that it's primarily because of your reason > a. Most of our problems come from Pg misunderstanding the results of a > join and making a bad plan decision. Batching dramatically reduces the > liklihood of this. As I already said - even the smartest planner won't work without correct input data. Have you tried fixing the points (b), (c) and (d)? Fixing them might improve the planner performance so that you don't need the batchning at all. regards Tomas