Re: Yet another slow nested loop

От: Tom Lane
Тема: Re: Yet another slow nested loop
Дата: ,
Msg-id: 14270.1245163017@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Yet another slow nested loop  (Alexander Staubo)
Ответы: Re: Yet another slow nested loop  (Alexander Staubo)
Список: pgsql-performance

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

Yet another slow nested loop  (Alexander Staubo, )
 Re: Yet another slow nested loop  ("Dave Dutcher", )
  Re: Yet another slow nested loop  (Alexander Staubo, )
 Re: Yet another slow nested loop  (Tom Lane, )
  Re: Yet another slow nested loop  (Alexander Staubo, )
   Re: Yet another slow nested loop  (Robert Haas, )

Alexander Staubo <> writes:
> Here's the query:
> select photos.*
> from photos
> inner join event_participations on
>   event_participations.user_id = photos.creator_id and
>   event_participations.attend = true
> inner join event_instances on
>   event_instances.id = event_participations.event_instance_id
> where (
>   (event_instances.venue_id = 1290) and
>   (photos.taken_at > (event_instances.time + interval '-3600 seconds')) and
>   (photos.taken_at < (event_instances.time + interval '25200 seconds'))
> )
> order by taken_at desc
> limit 20

> It occasionally takes four minutes to run:

Actually the easiest way to fix that is to get rid of the LIMIT.
(Maybe use a cursor instead, and fetch only twenty rows.)  LIMIT
magnifies the risks from any estimation error, and you've got a lot
of that here ...

            regards, tom lane


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

От: Anthony Presley
Дата:
Сообщение: Re: Speeding up a query.
От: "Mark Steben"
Дата:
Сообщение: Performance discrepancy