From: Jeffrey Tenny <jeffrey.tenny@comcast.net>
To: pgsql-performance@postgresql.org
Subject: Which is better, correlated subqueries or joins?
Date: Thu, 19 May 2005 17:50:14 -0400
Message-ID: <428D0A16.2010709@comcast.net> (raw)
Looking for some general advice on correlated subqueries vs. joins.
Which of these plans is likely to perform better. One table is a master
record table for entities and their IDs (nv_products), the other
represents a transitive closure of parent/child relationships (for a
tree) of ID's in the master record table (and so is larger)
(ssv_product_children).
The query is, in english: for direct children of an ID, return the ones
for which isrel is true.
I have only a tiny demo table set for which there is only one record
matched by the queries below, it's hard to guess at how deep or branchy
a production table might be, so I'm trying to develop a general query
strategy and learn a thing or two about pgsql.
Here's the join:
# explain select child_pid from ssv_product_children, nv_products where
nv_products.id = ssv_product_children.child_pid and
ssv_product_children.pid = 1 and nv_products.isrel = 't';
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=1.22..2.47 rows=2 width=8)
Hash Cond: ("outer".child_pid = "inner".id)
-> Seq Scan on ssv_product_children (cost=0.00..1.18 rows=9 width=4)
Filter: (pid = 1)
-> Hash (cost=1.21..1.21 rows=4 width=4)
-> Seq Scan on nv_products (cost=0.00..1.21 rows=4 width=4)
Filter: (isrel = true)
(7 rows)
Here's the correlated subquery:
# explain select child_pid from ssv_product_children where pid = 1 and
child_pid = (select nv_products.id from nv_products where nv_products.id
= child_pid and isrel = 't');
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on ssv_product_children (cost=0.00..18.78 rows=1 width=4)
Filter: ((pid = 1) AND (child_pid = (subplan)))
SubPlan
-> Seq Scan on nv_products (cost=0.00..1.26 rows=1 width=4)
Filter: ((id = $0) AND (isrel = true))
(5 rows)
Thanks for any advice.
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-performance@postgresql.org
Cc: jeffrey.tenny@comcast.net
Subject: Re: Which is better, correlated subqueries or joins?
In-Reply-To: <428D0A16.2010709@comcast.net>
* 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