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:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..10997.65 rows=20 width=116) (actual
time=262614.474..262614.474 rows=0 loops=1)
-> Nested Loop (cost=0.00..5729774.95 rows=10420 width=116)
(actual time=262614.470..262614.470 rows=0 loops=1)
Join Filter: ((photos.taken_at > (event_instances."time" +
'-01:00:00'::interval)) AND (photos.taken_at < (event_instances."time"
+ '07:00:00'::interval)))
-> Nested Loop (cost=0.00..2055574.35 rows=11869630
width=120) (actual time=21.750..121838.012 rows=14013998 loops=1)
-> Index Scan Backward using photos_taken_at on photos
(cost=0.00..40924.34 rows=544171 width=116) (actual
time=14.997..1357.724 rows=544171 loops=1)
-> Index Scan using event_participations_user_id_index
on event_participations (cost=0.00..2.95 rows=60 width=8) (actual
time=0.007..0.159 rows=26 loops=544171)
Index Cond: (event_participations.user_id =
photos.creator_id)
Filter: event_participations.attend
-> Index Scan using event_instances_pkey on event_instances
(cost=0.00..0.29 rows=1 width=12) (actual time=0.008..0.008 rows=0
loops=14013998)
Index Cond: (event_instances.id =
event_participations.event_instance_id)
Filter: (event_instances.venue_id = 1290)
Total runtime: 262614.585 ms
With enable_nestloop to false, it takes about 1 second to run.
Database is freshly analyzed and vacuumed. Default statistics target
is 100. I have tried increasing the stats on
event_participations.user_id, event_participations.event_instance_id
and photos.taken_at to 1000, but no improvement.
This is PostgreSQL 8.3.3.
A.
