On Tue, Sep 8, 2009 at 8:12 AM, Eugene Morozov<eugene@xxxxxxxxxxxxxxxx> wrote: > Hello, > > I have a following query (autogenerated by Django) > > SELECT activity_activityevent.id, activity_activityevent.user_id, activity_activityevent.added_on > FROM activity_activityevent > WHERE activity_activityevent.user_id IN ( > SELECT U0.user_id > FROM profile U0 > INNER JOIN profile_friends U1 > ON U0.user_id = U1.to_profile_id > WHERE U1.from_profile_id = 5 > ) > ORDER BY activity_activityevent.added_on DESC LIMIT 10 > > > When I run EXPLAIN ANALYZE with my default settings (seq scan is on, > random_page_cost = 4) I get the following result: > > Limit (cost=4815.62..4815.65 rows=10 width=202) (actual time=332.938..332.977 rows=10 loops=1) > -> Sort (cost=4815.62..4816.35 rows=292 width=202) (actual time=332.931..332.945 rows=10 loops=1) > Sort Key: activity_activityevent.added_on > Sort Method: top-N heapsort Memory: 19kB > -> Hash IN Join (cost=2204.80..4809.31 rows=292 width=202) (actual time=12.856..283.916 rows=15702 loops=1) > Hash Cond: (activity_activityevent.user_id = u0.user_id) > -> Seq Scan on activity_activityevent (cost=0.00..2370.43 rows=61643 width=202) (actual time=0.020..126.129 rows=61643 loops=1) > -> Hash (cost=2200.05..2200.05 rows=380 width=8) (actual time=12.777..12.777 rows=424 loops=1) > -> Nested Loop (cost=11.20..2200.05 rows=380 width=8) (actual time=0.260..11.594 rows=424 loops=1) > -> Bitmap Heap Scan on profile_friends u1 (cost=11.20..62.95 rows=380 width=4) (actual time=0.228..1.202 rows=424 loops=1) > Recheck Cond: (from_profile_id = 5) > -> Bitmap Index Scan on profile_friends_from_profile_id_key (cost=0.00..11.10 rows=380 width=0) (actual time=0.208..0.208 rows=424 loops=1) > Index Cond: (from_profile_id = 5) > -> Index Scan using profile_pkey on profile u0 (cost=0.00..5.61 rows=1 width=4) (actual time=0.012..0.015 rows=1 loops=424) > Index Cond: (u0.user_id = u1.to_profile_id) > Total runtime: 333.190 ms > > But when I disable seq scan or set random_page_cost to 1.2 (higher > values doesn't change the plan), postgres starts using index and query > runs two times faster: > > Limit (cost=9528.36..9528.38 rows=10 width=202) (actual time=165.047..165.090 rows=10 loops=1) > -> Sort (cost=9528.36..9529.09 rows=292 width=202) (actual time=165.042..165.058 rows=10 loops=1) > Sort Key: activity_activityevent.added_on > Sort Method: top-N heapsort Memory: 19kB > -> Nested Loop (cost=2201.00..9522.05 rows=292 width=202) (actual time=13.074..126.209 rows=15702 loops=1) > -> HashAggregate (cost=2201.00..2204.80 rows=380 width=8) (actual time=12.996..14.131 rows=424 loops=1) > -> Nested Loop (cost=11.20..2200.05 rows=380 width=8) (actual time=0.263..11.665 rows=424 loops=1) > -> Bitmap Heap Scan on profile_friends u1 (cost=11.20..62.95 rows=380 width=4) (actual time=0.232..1.181 rows=424 loops=1) > Recheck Cond: (from_profile_id = 5) > -> Bitmap Index Scan on profile_friends_from_profile_id_key (cost=0.00..11.10 rows=380 width=0) (actual time=0.210..0.210 rows=424 loops=1) > Index Cond: (from_profile_id = 5) > -> Index Scan using profile_pkey on profile u0 (cost=0.00..5.61 rows=1 width=4) (actual time=0.013..0.016 rows=1 loops=424) > Index Cond: (u0.user_id = u1.to_profile_id) > -> Index Scan using activity_activityevent_user_id on activity_activityevent (cost=0.00..18.82 rows=35 width=202) (actual time=0.014..0.130 rows=37 loops=424) > Index Cond: (activity_activityevent.user_id = u0.user_id) > Total runtime: 165.323 ms > > > Can anyone enlighten me? Should I set random_page_cost to 1.2 > permanently (I feel this is not a really good idea in my case)? OK, you need to look a little deeper at what's happening here. The pgsql query planner looks at a lot of things to decide if to use seq scan or and index. If you look at your row estimates versus actual rows returned, you'll see they're off, sometimes by quite a bit. Particularly the ones near the top of your query plans. There are a few things you can do to help out here. Increase default stats target and re-analyse, increase effective_cache_size to reflect the actual size of data being cached by your OS / filesystem / pgsql, and then lowering random_page_cost. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
