Thread: Question about slow Select when using 'IN'.
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.12rows=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.75rows=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.
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
Hi, I would like to know if the keyword EXIST can be used with PostgreSQL ? I have search in the Reference Manuel et tried a query using EXIST in pgsql, but no result... Thanks a lot Rachel ************************************** Rachel.Vaudron@lazaret.unice.fr Laboratoire de prehistoire du Lazaret33 bis bd Franck Pilatte 06300 Nice tel:04-92-00-17-37/fax:04-92-00-17-39 ******** Windows a bug's life ********
At 09:01 AM 3/12/2002 +0100, Rachel.Vaudron wrote: >I would like to know if the keyword EXIST can be used with PostgreSQL ? EXISTS is supported (ie. trailing 'S'). ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
On Tue, 3 Dec 2002, Rachel.Vaudron wrote: > Hi, > > I would like to know if the keyword EXIST can be used with PostgreSQL ? > I have search in the Reference Manuel et tried a query using EXIST in > pgsql, but no result... it is EXISTS . > > Thanks a lot > > Rachel > > ************************************** > Rachel.Vaudron@lazaret.unice.fr > Laboratoire de prehistoire du Lazaret > 33 bis bd Franck Pilatte 06300 Nice > tel:04-92-00-17-37/fax:04-92-00-17-39 > ******** Windows a bug's life ******** > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
> it is EXISTS I'm a very little shamefull !!! But Thanks at all. Rachel
> EXISTS is supported (ie. trailing 'S'). I'm a little shamefull ;)!!!! Thanks a lot. Rachel