Re: Yet another slow nested loop

Alexander Staubo <alex@bengler.no> 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