pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.




view thread (2+ messages)

Message-ID: <Pine.LNX.4.33.0306131458510.21088-100000@css120.ihs.com>
Permalink:  ../Pine.LNX.4.33.0306131458510.21088-100000@css120.ihs.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.33.0306131458510.21088-100000@css120.ihs.com

 · 

reply

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