From: mfw127@mail.usask.ca (Mike Winter)
Subject: Large Query Question. (Slow Select while using 'IN')
Date: 3 Dec 2002 08:30:45 -0800
Organization: http://groups.google.com/
Lines: 36
Message-ID: <5cc2efa0.0212030830.57d5e01c@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

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)
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.