Re: query question

Поиск
Список
Период
Сортировка
Искать

Re: query question

От:
Josh Berkus <josh@agliodbs.com>
Дата:

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

query question

От:
Laurette Cisneros <laurette@nextbus.com>
Дата:

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.
FAQ