Yet another slow nested loop

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux