Re: Yet another slow nested loop

Alexander Staubo <alex(at)bengler(dot)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