Planner tuning - Mailing list pgsql-general

I have a query here for which postgres chooses a nested loop, for which
it takes ~19s to complete in the worst-case (measured). However, if I
disable nestloops it completes in ~400ms...

It seems pretty obvious that the planner underestimates the cost of
nestloops here, is there some way to tweak this?



This is using PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC
cc (GCC) 3.3.5 (Debian 1:3.3.5-13)

The server is on some kind of xen domain. I have absolutely no idea what
impact that has for various planner parameters (except that it'll
probably not perform as well as an unxenned server), it may be relevant.

The plans with and w/o nestloops respectively are attached (to prevent
wrapping).

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //
                                                                                                       QUERY PLAN
                                                                                                

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3946.48..3946.56 rows=1 width=62) (actual time=19940.602..19940.604 rows=1 loops=1)
   ->  Nested Loop  (cost=3641.37..3946.40 rows=1 width=62) (actual time=559.955..19917.625 rows=969 loops=1)
         Join Filter: ("outer".property_id = "inner".property_id)
         ->  Hash Join  (cost=3348.15..3549.62 rows=1 width=50) (actual time=525.618..552.537 rows=969 loops=1)
               Hash Cond: ("outer".property_id = "inner".property_id)
               ->  HashAggregate  (cost=3066.41..3212.93 rows=3663 width=12) (actual time=506.511..519.870 rows=3522
loops=1)
                     ->  Bitmap Heap Scan on fewo_property_availability_month property_availability_month
(cost=232.19..2517.02rows=36626 width=12) (actual time=6.238..234.237 rows=37316 loops=1) 
                           Recheck Cond: (300 = country_id)
                           ->  Bitmap Index Scan on fewo_property_availability_month_country_property_idx
(cost=0.00..232.19rows=36626 width=0) (actual time=6.181..6.181 rows=37316 loops=1) 
                                 Index Cond: (300 = country_id)
               ->  Hash  (cost=281.74..281.74 rows=2 width=14) (actual time=19.052..19.052 rows=1683 loops=1)
                     ->  Nested Loop  (cost=0.00..281.74 rows=2 width=14) (actual time=0.068..14.000 rows=1683 loops=1)
                           ->  Index Scan using fewo_location_ancestry_full_idx on fewo_location_ancestry ancestor
(cost=0.00..49.34rows=9 width=4) (actual time=0.024..0.172 rows=41 loops=1) 
                                 Index Cond: ((ancestor_id = 309) AND (ancestor_type_id = 12) AND (child_type_id = 10))
                           ->  Index Scan using fewo_property_location_country_location_idx on fewo_property_location
property_location (cost=0.00..25.80 rows=2 width=18) (actual time=0.009..0.169 rows=41 loops=41) 
                                 Index Cond: ((property_location.country_id = 300) AND ("outer".child_id =
property_location.location_id))
                                 Filter: (property_state_id = 3)
         ->  HashAggregate  (cost=293.22..347.72 rows=2180 width=12) (actual time=0.038..11.221 rows=3522 loops=969)
               ->  Bitmap Heap Scan on fewo_period_type_property period_type_property  (cost=24.68..256.98 rows=3624
width=12)(actual time=0.674..11.258 rows=3522 loops=1) 
                     Recheck Cond: (300 = country_id)
                     ->  Bitmap Index Scan on fewo_period_type_property_country_property_idx  (cost=0.00..24.68
rows=3624width=0) (actual time=0.659..0.659 rows=3522 loops=1) 
                           Index Cond: (300 = country_id)
 Total runtime: 19941.453 ms
(23 rows)

                                                                                              QUERY PLAN
                                                                              

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4278.42..4278.50 rows=1 width=62) (actual time=412.843..412.845 rows=1 loops=1)
   ->  Hash Join  (cost=4076.02..4278.33 rows=1 width=62) (actual time=362.413..403.271 rows=969 loops=1)
         Hash Cond: ("outer".location_id = "inner".child_id)
         ->  Hash Join  (cost=4026.66..4228.36 rows=24 width=66) (actual time=362.074..392.155 rows=3522 loops=1)
               Hash Cond: ("outer".property_id = "inner".property_id)
               ->  HashAggregate  (cost=3066.41..3212.93 rows=3663 width=12) (actual time=264.426..276.010 rows=3522
loops=1)
                     ->  Bitmap Heap Scan on fewo_property_availability_month property_availability_month
(cost=232.19..2517.02rows=36626 width=12) (actual time=6.417..113.221 rows=37316 loops=1) 
                           Recheck Cond: (300 = country_id)
                           ->  Bitmap Index Scan on fewo_property_availability_month_country_property_idx
(cost=0.00..232.19rows=36626 width=0) (actual time=6.350..6.350 rows=37316 loops=1) 
                                 Index Cond: (300 = country_id)
               ->  Hash  (cost=959.58..959.58 rows=270 width=38) (actual time=97.607..97.607 rows=3522 loops=1)
                     ->  Hash Join  (cost=787.44..959.58 rows=270 width=38) (actual time=57.491..86.516 rows=3522
loops=1)
                           Hash Cond: ("outer".property_id = "inner".property_id)
                           ->  HashAggregate  (cost=293.22..347.72 rows=2180 width=12) (actual time=24.881..35.667
rows=3522loops=1) 
                                 ->  Bitmap Heap Scan on fewo_period_type_property period_type_property
(cost=24.68..256.98rows=3624 width=12) (actual time=0.605..10.283 rows=3522 loops=1) 
                                       Recheck Cond: (300 = country_id)
                                       ->  Bitmap Index Scan on fewo_period_type_property_country_property_idx
(cost=0.00..24.68rows=3624 width=0) (actual time=0.592..0.592 rows=3522 loops=1) 
                                             Index Cond: (300 = country_id)
                           ->  Hash  (cost=481.28..481.28 rows=5177 width=18) (actual time=32.546..32.546 rows=4873
loops=1)
                                 ->  Bitmap Heap Scan on fewo_property_location property_location  (cost=53.22..481.28
rows=5177width=18) (actual time=0.989..17.495 rows=4873 loops=1) 
                                       Recheck Cond: (country_id = 300)
                                       Filter: (property_state_id = 3)
                                       ->  Bitmap Index Scan on fewo_property_location_country_property_idx
(cost=0.00..53.22rows=5204 width=0) (actual time=0.951..0.951 rows=4873 loops=1) 
                                             Index Cond: (country_id = 300)
         ->  Hash  (cost=49.34..49.34 rows=9 width=4) (actual time=0.287..0.287 rows=41 loops=1)
               ->  Index Scan using fewo_location_ancestry_full_idx on fewo_location_ancestry ancestor
(cost=0.00..49.34rows=9 width=4) (actual time=0.026..0.177 rows=41 loops=1) 
                     Index Cond: ((ancestor_id = 309) AND (ancestor_type_id = 12) AND (child_type_id = 10))
 Total runtime: 413.932 ms
(28 rows)