Thread: subselects - which is faster?

subselects - which is faster?

From
Cedar Cox
Date:
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))asattrtype, 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.04rows=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
secwhere 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.09rows=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.

Thanks, and sorry for bothering you all..
- Cedar



Re: subselects - which is faster?

From
"scott.marlowe"
Date:
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.