pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about slow Select when using 'IN'.
2+ messages / 2 participants
[nested] [flat]
* Question about slow Select when using 'IN'.
@ 2002-11-29 22:23 Mike Winter <mike.winter@*nospam**frontlogic.com>
0 siblings, 1 reply; 2+ messages in thread
From: Mike Winter @ 2002-11-29 22:23 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Question about slow Select when using 'IN'.
@ 2002-12-03 07:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mike Winter <mike.winter@*nospam**frontlogic.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-12-03 07:06 UTC (permalink / raw)
To: Mike Winter <mike.winter@*nospam**frontlogic.com>; +Cc: pgsql-sql
Mike Winter <mike.winter@*nospam**frontlogic.com> writes:
> 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)
The planner obviously does not think this is a large table (the cost
estimates correspond to very small numbers of pages). I wonder
whether you have ever VACUUMed or ANALYZEd the table.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-12-03 07:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-29 22:23 Question about slow Select when using 'IN'. Mike Winter <mike.winter@*nospam**frontlogic.com>
2002-12-03 07:06 ` Tom Lane <tgl@sss.pgh.pa.us>
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