From: scott.marlowe <scott.marlowe@ihs.com>
To: Cedar Cox <cedarc@visionforisrael.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: subselects - which is faster?
Date: Fri, 13 Jun 2003 15:00:34 -0600 (MDT)
Message-ID: <Pine.LNX.4.33.0306131458510.21088-100000@css120.ihs.com> (raw)
In-Reply-To: <Pine.LNX.4.21.0306121259480.3203-100000@nanu.visionforisrael.com>
On Thu, 12 Jun 2003, Cedar Cox wrote:
> It's been a while since I've done much SQL..
>
> . I'm wondering which of these two queries is faster (both get the
> same result)?
>
> . Which one is more correct? Does it even matter or are they the
> same? The first one reads easier to me.
>
> . What's the difference between "InitPlan" and "SubPlan"?
>
> explain SELECT eqid,
> (select name from tbleqattrtypes where id=
> (select eqattrtypeid from tbleqattrs
> where id=main.eqattrid))
> as attrtype, eqattrid from tbleqattrmap as main;
>
> NOTICE: QUERY PLAN:
> Seq Scan on tbleqattrmap main (cost=0.00..1.15 rows=15 width=8)
> SubPlan
> -> Seq Scan on tbleqattrtypes (cost=0.00..1.04 rows=1 width=12)
> InitPlan
> -> Seq Scan on tbleqattrs (cost=0.00..1.09 rows=1 width=4)
>
>
> explain SELECT eqid,
> (select
> (select name from tbleqattrtypes where id=sec.eqattrtypeid)
> from tbleqattrs as sec where id=main.eqattrid)
> as attrtype, eqattrid from tbleqattrmap as main;
>
> NOTICE: QUERY PLAN:
> Seq Scan on tbleqattrmap main (cost=0.00..1.15 rows=15 width=8)
> SubPlan
> -> Seq Scan on tbleqattrs sec (cost=0.00..1.09 rows=1 width=4)
> SubPlan
> -> Seq Scan on tbleqattrtypes (cost=0.00..1.04 rows=1 width=12)
>
> One additional detail: right now the tables are all very small, and
> tbleqattrtypes will not grow much, but tbleqattrs will eventually be
> very large.
A couple of quick points.
1: Postgresql uses a cost based planner, not a rule based planner. This
means you need to run analyze every so often to let the database know how
many rows of what kind of data are in each table.
This also means that if you are going to have 100,000 rows when you go
live, then you need to create 100,000 representative rows now in order to
figure out which is faster.
2: User 'explain analyze select ...' to make the database actually run
the query and time it for you. Then you'll know which is faster.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: scott.marlowe@ihs.com, cedarc@visionforisrael.com
Subject: Re: subselects - which is faster?
In-Reply-To: <Pine.LNX.4.33.0306131458510.21088-100000@css120.ihs.com>
References: <Pine.LNX.4.21.0306121259480.3203-100000@nanu.visionforisrael.com> <Pine.LNX.4.33.0306131458510.21088-100000@css120.ihs.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