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
[EMAIL PROTECTED]
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.02 rows=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.19
rows=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.34 rows=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=3624
width=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.02 rows=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.19
rows=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=3522 loops=1)
-> Bitmap Heap Scan on
fewo_period_type_property period_type_property (cost=24.68..256.98 rows=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.68 rows=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=5177 width=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.22 rows=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.34 rows=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)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
