Обсуждение: query question

Silly question (and just because I would like to know exactly why):

This query:
select distinct x, y
  from table1 t
  join table2 t2
 using (col1)
order by x;

is *slower* than this query:

select disting x, y
  from table1
 where col1 = (select col1 from table2)
ORDER BY x;

Is this because in the latter case the select col1 is cached?

Ooo, I would love to have a web page full of these tidbits (along with how
to get around the max and min aggregates and why as an example..., etc.)!

Thanks,

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
There is more to life than just SQL.

Laurette,

> This query:
> select distinct x, y
>   from table1 t
>   join table2 t2
>  using (col1)
> order by x;
>
> is *slower* than this query:
>
> select disting x, y
>   from table1
>  where col1 = (select col1 from table2)
> ORDER BY x;
>
> Is this because in the latter case the select col1 is cached?

Yes.   For all of the following structures:

where x = (select col from table)
where x IN (select col from table)
where x NOT IN (select col from table)
where x != ANY(select col from table)
etc.,

... Postgres must process the full subquery, return the results, and compare
all of the results as individual values against the reference column.

However, if you re-wrote the query as:

 select distint x, y
   from table1
  where EXISTS (select col1 from table2
    where table2.col1 = table1.col1)
 ORDER BY x;

... then Postgres would be able to use JOIN optimizations to evaluate the
subquery and pull a subset of relevant records or even use an index, making
the query *much* faster.

> Ooo, I would love to have a web page full of these tidbits (along with how
> to get around the max and min aggregates and why as an example..., etc.)!

Um:

http://techdocs.postgresql.org/guides/

Add your own Wiki page!


--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology     josh@agliodbs.com
    and data management solutions     (415) 565-7293
   for law firms, small businesses      fax 621-2533
    and non-profit organizations.     San Francisco