Re: Index usage with sub select or inner joins

От: Joshua Tolley
Тема: Re: Index usage with sub select or inner joins
Дата: ,
Msg-id: 20081112135405.GD22032@polonium.part.net
(см: обсуждение, исходный текст)
Ответ на: Index usage with sub select or inner joins  ("Julien Theulier")
Ответы: Re: Index usage with sub select or outer joins  ("Julien Theulier")
Список: pgsql-performance

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

Using index for IS NULL query  ("Andrus", )
 Re: Using index for IS NULL query  (Tom Lane, )
  Re: Using index for IS NULL query  (Andreas Kretschmer, )
  Re: Using index for IS NULL query  (Matthew Wakeling, )
 Re: Using index for IS NULL query  (Andreas Kretschmer, )
 Re: Using index for IS NULL query  (Tomas Vondra, )
  Re: Using index for IS NULL query  ("Vladimir Sitnikov", )
   Re: Using index for IS NULL query  ("Scott Marlowe", )
   Re: Using index for IS NULL query  (Tomas Vondra, )
  Index usage with sub select or inner joins  ("Julien Theulier", )
   Re: Index usage with sub select or inner joins  (Joshua Tolley, )
    Re: Index usage with sub select or outer joins  ("Julien Theulier", )

On Wed, Nov 12, 2008 at 02:22:47PM +0100, Julien Theulier wrote:
> QUESTION: Why the planner choose seq scan in the first case & indexes scan
> in the second case? In a more general way, I observed that the planner has
> difficulties to select index scans & does in almost all the cases seq scan,
> when doing join queries. After investigations, it looks like when you join
> table a with table b on a column x and y and you have an index on column x
> only, the planner is not able to choose the index scan. You have to build
> the index corresponding exactly to the join statement btw the 2 tables

Short, general answer: index scans aren't always faster than sequential
scans, and the planner is smart enough to know that. Googling "Why isn't
postgresql using my index" provides more detailed results, but in short,
if it scans an index, it has to read pages from the index, and for all
the tuples it finds in the index, it has to read once again from the
heap, whereas a sequential scan requires reading once from the heap. If
your query will visit most of the rows of the table, pgsql will choose a
sequential scan over an index scan.

- Josh / eggyknap

Вложения

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

От: "Andrus"
Дата:
Сообщение: Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
От: "Andrus"
Дата:
Сообщение: Re: Increasing select max(datecol) from bilkaib wheredatecol<=date'2008-11-01' and (cr='00' or db='00') speed