Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CDC7E64FE07 for ; Tue, 18 Nov 2008 12:38:22 -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 06241-08 for ; Tue, 18 Nov 2008 12:38:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (unknown [82.208.29.208]) by mail.postgresql.org (Postfix) with ESMTP id 3302464FCC7 for ; Tue, 18 Nov 2008 12:38:09 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id 2A5C07AC25D; Tue, 18 Nov 2008 17:38:08 +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 lT7tBPsVGI3U; Tue, 18 Nov 2008 17:38:08 +0100 (CET) Received: from anastacia.gransy.com (anastacia.gransy.com [89.187.130.100]) by mail.gransy.com (Postfix) with ESMTP id 9B6997AC161; Tue, 18 Nov 2008 17:38:06 +0100 (CET) Received: from sq.gransy.com (localhost [127.0.0.1]) by anastacia.gransy.com (Postfix) with ESMTP id 00309282C4E; Tue, 18 Nov 2008 17:38:45 +0100 (CET) Received: from 89.102.139.23 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Tue, 18 Nov 2008 17:38:45 +0100 (CET) Message-ID: <64433.89.102.139.23.1227026325.squirrel@sq.gransy.com> In-Reply-To: References: Date: Tue, 18 Nov 2008 17:38:45 +0100 (CET) Subject: Re: Performance and IN clauses From: tv@fuzzy.cz To: "Matthew Wakeling" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.10a MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-2 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200811/157 X-Sequence-Number: 31888 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 > On Tue, 18 Nov 2008, Kynn Jones wrote: >> Also, assuming that the optimal way to write the query depends on the >> length of $node_list, how can I estimate the >> "critical length" at which I should switch from one form of the query to >> the other? > > In the past, I have found the fastest way to do this was to operate on > groups of a bit less than a thousand values, and issue one query per > group. Of course, Postgres may have improved since then, so I'll let more > knowledgable people cover that for me. > > Matthew > > -- > Heat is work, and work's a curse. All the heat in the universe, it's > going to cool down, because it can't increase, then there'll be no > more work, and there'll be perfect peace. -- Michael Flanders > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >