Re: Subqueries and the optimizer - Mailing list pgsql-general
From | Dmitri Bichko |
---|---|
Subject | Re: Subqueries and the optimizer |
Date | |
Msg-id | 7A4ADADFC8AFF0478D47F63BEDD57CE30CDF80@gpmail.gphq.genpathpharma.com Whole thread Raw |
In response to | Subqueries and the optimizer ("Dmitri Bichko" <dbichko@genpathpharma.com>) |
Responses |
Re: Subqueries and the optimizer
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Re: Subqueries and the optimizer (Mike Mascari <mascarm@mascari.com>) Re: Subqueries and the optimizer (Alvaro Herrera <alvherre@dcc.uchile.cl>) Re: Subqueries and the optimizer (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-general |
I wish it were as easy as a join - the query is much simplified for the purpose of the example, in reality the subselect is more complicated and includes a GROUP BY (which, at least as far as I know, makes subqueries the only way of doing this). Thanks anway, guess I'll wait for 7.4 with this (and just split them up into two queries for the time being), Dmitri -----Original Message----- From: Dean Gibson (DB Administrator) [mailto:dba-sql@ultimeth.net] Sent: Tuesday, May 20, 2003 2:28 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Subqueries and the optimizer Try: SELECT blast_id FROM genes, ll_out_mm WHERE blast_batch_id = 2 AND genes.locus_id = ll_out_mm.locus_id; Using more recent versions of PostgreSQL, you can also write: SELECT blast_id FROM genes JOIN ll_out_mm USING ( locus_id) WHERE blast_batch_id = 2; -- Dean Dmitri Bichko wrote on 2003-05-20 10:50: >So, I have a table with an index: > >dev=# explain SELECT blast_id FROM genes WHERE blast_batch_id = 2 AND >locus_id IN (1,2); > QUERY PLAN >----------------------------------------------------------------------- - >------------------------------ > Index Scan using idx_genes_locus_id, idx_genes_locus_id on genes >(cost=0.00..88.21 rows=14 width=4) > Index Cond: ((locus_id = 1) OR (locus_id = 2)) > Filter: (blast_batch_id = 2) > >So far so good, but when I try it with a subquery: > >dev=# explain SELECT blast_id FROM genes WHERE blast_batch_id = 2 AND >locus_id IN (SELECT locus_id FROM ll_out_mm); >QUERY PLAN >----------------------------------------------------------------------- - > Seq Scan on genes (cost=0.00..21414353.48 rows=11003 width=4) > Filter: ((blast_batch_id = 2) AND (subplan)) > SubPlan > -> Seq Scan on ll_out_mm (cost=0.00..1267.64 rows=59264 width=4) > > >How can I nudge the optimizer in the direction of using the index in the >second case as well? Or is it supposed to be doing this in this case. > >Thanks, >Dmitri > >---------------------------(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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
pgsql-general by date: