From: Joshua D. Drake <jd@commandprompt.com>
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Which is better, correlated subqueries or joins?
Date: Thu, 19 May 2005 14:53:52 -0700
Message-ID: <428D0AF0.4020001@commandprompt.com> (raw)
In-Reply-To: <428D0A16.2010709@comcast.net>
References: <428D0A16.2010709@comcast.net>
Hello,
It always depends on the dataset but you should try an explain analyze
on each query. It will tell you which one is more efficient for your
particular data.
Sincerely,
Joshua D. Drake
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
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: jd@commandprompt.com, jeffrey.tenny@comcast.net
Subject: Re: Which is better, correlated subqueries or joins?
In-Reply-To: <428D0AF0.4020001@commandprompt.com>
* 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