From: Mike Winter <mike.winter@*nospam**frontlogic.com>
To: pgsql-sql@postgresql.org
Subject: Question about slow Select when using 'IN'.
Date: Fri, 29 Nov 2002 16:23:58 -0600 (CST)
Message-ID: <Pine.LNX.4.33L2.0211291623260.18672-100000@frontlogic.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike.winter@*nospam**frontlogic.com
Subject: Re: Question about slow Select when using 'IN'.
In-Reply-To: <Pine.LNX.4.33L2.0211291623260.18672-100000@frontlogic.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox