pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReplacing a simple nested query?
3+ messages / 2 participants
[nested] [flat]
* Replacing a simple nested query?
@ 2003-07-13 21:50 Steve Wampler <swampler@noao.edu>
0 siblings, 2 replies; 3+ messages in thread
From: Steve Wampler @ 2003-07-13 21:50 UTC (permalink / raw)
To: pgsql-sql
I've got a simple nested query:
select * from attributes where id in (select id from
attributes where (name='obsid') and (value='oid00066'));
that performs abysmally. I've heard this described as the
'classic WHERE IN' problem.
Is there a better way to obtain the same results? The inner
select identifies a set of ids (2049 of them, to be exact)
that are then used to locate records that have the same id
(about 30-40K of those, including the aforementioned 2049).
Thanks!
-Steve
--
Steve Wampler -- swampler@noao.edu
Quantum materiae materietur marmota monax si marmota
monax materiam possit materiari?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Replacing a simple nested query?
@ 2003-07-13 22:01 Joe Conway <mail@joeconway.com>
parent: Steve Wampler <swampler@noao.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Joe Conway @ 2003-07-13 22:01 UTC (permalink / raw)
To: Steve Wampler <swampler@noao.edu>; +Cc: pgsql-sql
Steve Wampler wrote:
> I've got a simple nested query:
>
> select * from attributes where id in (select id from
> attributes where (name='obsid') and (value='oid00066'));
>
> that performs abysmally. I've heard this described as the
> 'classic WHERE IN' problem.
I may be missing something, but why can't you just do:
select * from attributes where name='obsid' and value='oid00066';
?
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Replacing a simple nested query?
@ 2003-07-14 13:38 Steve Wampler <swampler@noao.edu>
parent: Steve Wampler <swampler@noao.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Wampler @ 2003-07-14 13:38 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Postgres-performance <pgsql-performance@postgresql.org>
On Sun, 2003-07-13 at 14:50, Steve Wampler wrote:
> I've got a simple nested query:
>
> select * from attributes where id in (select id from
> attributes where (name='obsid') and (value='oid00066'));
>
> that performs abysmally. I've heard this described as the
> 'classic WHERE IN' problem.
>
> Is there a better way to obtain the same results? The inner
> select identifies a set of ids (2049 of them, to be exact)
> that are then used to locate records that have the same id
> (about 30-40K of those, including the aforementioned 2049).
For the record, Joe Conway and Hannu Krosing both provided
the same solution:
select at.* from attributes_table at, attributes a
where at.id = a.id and a.name='obsid' and a.value='oid00066';
which is several orders of infinity faster than than my naive
approach above:
-------------------------------------------------------------
lab.devel.configdb=# explain analyze select * from
attributes_table where id in (select id from attributes
where (name='obsid') and (value='oid00066')) order by id;
NOTICE: QUERY PLAN:
Index Scan using id_index on attributes_table (cost=0.00..8773703316.10
rows=241201 width=59) (actual time=136297.91..3418016.04 rows=32799
loops=1)
SubPlan
-> Materialize (cost=18187.48..18187.48 rows=15 width=25) (actual
time=0.01..1.68 rows=1979 loops=482402)
-> Index Scan using name_index on attributes_table
(cost=0.00..18187.48 rows=15 width=25) (actual time=0.27..251.95
rows=2049 loops=1)
Total runtime: 3418035.38 msec
--------------------------------------------------------------
lab.devel.configdb=# explain analyze select at.* from
attributes_table at, attributes a
where at.id = a.id and a.name='obsid' and a.value='oid00066';
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..18739.44 rows=217 width=84) (actual
time=0.76..1220.65 rows=32799 loops=1)
-> Index Scan using name_index on attributes_table
(cost=0.00..18187.48 rows=15 width=25) (actual time=0.47..507.31
rows=2049 loops=1)
-> Index Scan using id_index on attributes_table at
(cost=0.00..35.80 rows=12 width=59) (actual time=0.11..0.31 rows=16
loops=2049)
Total runtime: 1235.42 msec
-------------------------------------------------------------------
My thanks to both Joe and Hannu!
Steve
--
Steve Wampler -- swampler@noao.edu
Quantum materiae materietur marmota monax si marmota
monax materiam possit materiari?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-07-14 13:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-13 21:50 Replacing a simple nested query? Steve Wampler <swampler@noao.edu>
2003-07-13 22:01 ` Joe Conway <mail@joeconway.com>
2003-07-14 13:38 ` Steve Wampler <swampler@noao.edu>
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