Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 68ABF475B99 for ; Fri, 29 Nov 2002 17:16:12 -0500 (EST) Received: from frontlogic.com (gw.frontlogic.com [207.195.92.149]) by postgresql.org (Postfix) with SMTP id 4CFBA475461 for ; Fri, 29 Nov 2002 17:16:11 -0500 (EST) Received: (qmail 24124 invoked by uid 614); 29 Nov 2002 22:23:58 -0000 Received: from localhost (sendmail-bs@127.0.0.1) by localhost with SMTP; 29 Nov 2002 22:23:58 -0000 Date: Fri, 29 Nov 2002 16:23:58 -0600 (CST) From: Mike Winter X-X-Sender: To: Subject: Question about slow Select when using 'IN'. Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/5 X-Sequence-Number: 10593 Hi all, I hope someone can help me out. I'm doing single-table select statements on a large table and I could use some help in speeding it up. My query is of the form: SELECT col, count(col) FROM tab WHERE id IN (3, 4,7,2, ...) GROUP BY COL ORDER BY count for a very large number of rows. I have an index on id, so the explain looks like: Aggregate (cost=12.12..12.14 rows=1 width=5) -> Group (cost=12.12..12.13 rows=4 width=5) -> Sort (cost=12.12..12.12 rows=4 width=5) -> Index Scan using col_id_idx2, col_id_idx2, col_id_idx2, col_id_idx2 on tab (cost=0.00..12.08 rows=4 width=5) So, it does a separate index scan for each row in the IN statement, which takes forever. How do I force the query parser to emulate the behaviour displayed by this query: SELECT col, count(col) FROM tab WHERE (0 = id % 5) GROUP BY COL ORDER BY count Aggregate (cost=3.75..3.86 rows=2 width=5) -> Group (cost=3.75..3.81 rows=21 width=5) -> Sort (cost=3.75..3.75 rows=21 width=5) -> Index Scan using col_id_idx2 on tab (cost=0.00..3.29 rows=21 width=5) Which only does one index scan for an equivelant number of records. Thanks for any help. Please cc to my e-mail.