Re: subselects - which is faster? - Mailing list pgsql-sql

From scott.marlowe
Subject Re: subselects - which is faster?
Date
Msg-id Pine.LNX.4.33.0306131458510.21088-100000@css120.ihs.com
Whole thread Raw
In response to subselects - which is faster?  (Cedar Cox <cedarc@visionforisrael.com>)
List pgsql-sql
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.



pgsql-sql by date:

Previous
From: Craig Jensen
Date:
Subject: rpm scripts
Next
From: "scott.marlowe"
Date:
Subject: Re: How to make a IN without a table... ?