От: Kevin Grittner
Тема: Re: Some question
Дата: ,
Msg-id: 4BC17DD902000025000306D4@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Some question  (Ľubomír Varga)
Список: pgsql-performance

Скрыть дерево обсуждения

Some question  (Ľubomír Varga, )
 Re: Some question  (Scott Marlowe, )
  Re: Some question  (Yeb Havinga, )
 Re: Some question  ("Kevin Grittner", )
  Re: Some question  (Ľubomír Varga, )
 Re: Some question  ("Kevin Grittner", )

Ľubomír Varga wrote:

> SELECT * FROM t_route
>   WHERE t_route.route_type_fk = 1
>   limit 4;

This one scanned the t_route table until it found four rows that
matched.  It apparently didn't need to look at very many rows to find
the four matches, so it was fast.

> SELECT * FROM t_route
>   WHERE t_route.route_type_fk =
>     (SELECT id FROM t_route_type WHERE type = 2)
>   limit 4;

This one came up with an id for a route type that didn't have any
matches in the t_route table, so it had to scan the entire t_route
table.  (Based on your next query, the subquery probably returned
NULL, so there might be room for some optimization here.)  If you had
chosen a route type with at least four matches near the  start of the
route table, this query would have completed quickly.

> SELECT * FROM t_route, t_route_type
>   WHERE t_route.route_type_fk = t_route_type.id
>     AND type = 2
>   limit 4;

Since it didn't find any t_route_type row which matched, it knew
there couldn't be any output from the JOIN, so it skipped the scan of
the t_route table entirely.

-Kevin




В списке pgsql-performance по дате сообщения:

От: Scott Marlowe
Дата:
Сообщение: Re: [PERFORM] About “context-switching issue on Xeon” test case ?
От: Corin
Дата:
Сообщение: planer chooses very bad plan