Re: Slow query with joins

От: Tom Lane
Тема: Re: Slow query with joins
Дата: ,
Msg-id: 13028.1136994347@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Slow query with joins  (Bendik Rognlien Johansen)
Ответы: Re: Slow query with joins  (Bendik Rognlien Johansen)
Список: pgsql-performance

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

Slow query with joins  (Bendik Rognlien Johansen, )
 Re: Slow query with joins  (Tom Lane, )
  Re: Slow query with joins  (Bendik Rognlien Johansen, )
   Re: Slow query with joins  ("Jim C. Nasby", )
    Re: Slow query with joins  (Bendik Rognlien Johansen, )
     Re: Slow query with joins  ("Jim C. Nasby", )

Bendik Rognlien Johansen <> writes:
> Has anyone got any tips for speeding up this query? It currently
> takes hours to start.

Are the rowcount estimates close to reality?  The plan doesn't look
unreasonable to me if they are.  It might help to increase work_mem
to ensure that the hash tables don't spill to disk.

Indexes:
     "people_original_is_null" btree (original) WHERE original IS NULL

This index seems poorly designed: the actual index entries are dead
weight since all of them are necessarily NULL.  You might as well make
the index carry something that you frequently test in conjunction with
"original IS NULL".  For instance, if this particular query is a common
case, you could replace this index with

CREATE INDEX people_deleted_original_is_null ON people(deleted)
  WHERE original IS NULL;

This index is still perfectly usable for queries that only say "original
IS NULL", but it can also filter out rows with the wrong value of
deleted.  Now, if there are hardly any rows with deleted = true, maybe
this won't help much for your problem.  But in any case you ought to
consider whether you can make the index entries do something useful.

            regards, tom lane


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

От: Bendik Rognlien Johansen
Дата:
Сообщение: Re: Slow query with joins
От: "Dave Dutcher"
Дата:
Сообщение: Showing Column Statistics Number