Re: subselects - which is faster?

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.