Alban Hertroys <alban@magproductions.nl> writes:
> It seems pretty obvious that the planner underestimates the cost of
> nestloops here, is there some way to tweak this?
The real problem is the factor-of-a-thousand underestimate of the size
of this join:
> -> 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=9width=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)
Have you got up-to-date ANALYZE stats for both of these tables?
Maybe increasing the statistics targets for them would help.
You may be kind of stuck because of the lack of cross-column statistics
--- I suppose these columns are probably rather highly correlated ---
but you should at least try pulling the levers you've got.
One thought is that country_id is probably entirely determined by
location_id, and possibly ancestor_type_id is determined by ancestor_id.
If so you should be leaving them out of the queries and indexes;
they're not doing anything for you except fooling the planner about the
net selectivity of the conditions.
regards, tom lane