Tom Lane wrote:
> Alban Hertroys <alban@xxxxxxxxxxxxxxxxx> 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:
Good observation, I missed that one. Thanks.
>> -> 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)
>
> Have you got up-to-date ANALYZE stats for both of these tables?
> Maybe increasing the statistics targets for them would help.
Yes. This is as of this moment a mostly static development database that
has been vacuumed and analyzed quite recently.
> 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.
Actually property.location_id refers to cities, which is the deepest
level in the represented data. Country_id is the top level.
Ancestry id, type and child id, type are indeed closely related. I
changed their representation based on your suggestions.
> 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.
I tried a few things, but it seems I am quite successful at fooling the
planner...
I changed the indices on our ancestry table to not combine id and type
on the same half of the join; which is something we're in fact never
interested in anyway. This seems to have helped some indeed.
I tried removing country_id from the equation, but I haven't had the
patience to wait for the explain analyzes to complete that way - they
take long.
I implemented it this way as an optimization; I decided to join
property_location with both period_type_property and
property_availability_month using (country_id, property_id) as FK.
That quickly narrows down the number of matching records in those
tables, which an index on property_id only somehow didn't accomplish.
The good news is that I get results under 1s without having to
explicitly sort my subquery results.
The bad news is that the estimated row counts are still quite a bit off.
I analyzed the DB just before generating the attached result.
--
Alban Hertroys
alban@xxxxxxxxxxxxxxxxx
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=6780.04..6780.42 rows=1 width=182) (actual time=629.652..629.653 rows=1 loops=1)
-> Nested Loop (cost=1053.78..6779.62 rows=1 width=182) (actual time=160.662..595.059 rows=969 loops=1)
-> Hash Join (cost=1053.78..6688.46 rows=21 width=186) (actual time=160.493..545.222 rows=3522 loops=1)
Hash Cond: ("outer".property_id = "inner".property_id)
-> GroupAggregate (cost=0.00..5581.97 rows=3500 width=12) (actual time=0.159..363.108 rows=3522 loops=1)
-> Index Scan using fewo_property_availability_month_country_property_idx on fewo_property_availability_month property_availability_month (cost=0.00..3893.52 rows=34992 width=12) (actual time=0.023..147.269 rows=37316 loops=1)
Index Cond: (300 = country_id)
-> Hash (cost=1053.16..1053.16 rows=250 width=86) (actual time=160.277..160.277 rows=3522 loops=1)
-> Hash Join (cost=825.03..1053.16 rows=250 width=86) (actual time=115.767..147.429 rows=3522 loops=1)
Hash Cond: ("outer".property_id = "inner".property_id)
-> HashAggregate (cost=338.96..488.83 rows=2141 width=12) (actual time=64.207..77.280 rows=3522 loops=1)
-> Bitmap Heap Scan on fewo_period_type_property period_type_property (cost=23.03..253.01 rows=3438 width=12) (actual time=0.625..27.199 rows=3522 loops=1)
Recheck Cond: (300 = country_id)
-> Bitmap Index Scan on fewo_period_type_property_country_property_idx (cost=0.00..23.03 rows=3438 width=0) (actual time=0.605..0.605 rows=3522 loops=1)
Index Cond: (300 = country_id)
-> Hash (cost=473.87..473.87 rows=4881 width=18) (actual time=51.496..51.496 rows=4873 loops=1)
-> Bitmap Heap Scan on fewo_property_location property_location (cost=50.19..473.87 rows=4881 width=18) (actual time=0.974..24.530 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..50.19 rows=4912 width=0) (actual time=0.939..0.939 rows=4873 loops=1)
Index Cond: (country_id = 300)
-> Index Scan using fewo_location_ancestry_ancestor_child_idx on fewo_location_ancestry ancestor (cost=0.00..4.33 rows=1 width=4) (actual time=0.007..0.008 rows=0 loops=3522)
Index Cond: ((ancestor.ancestor_id = 309) AND (ancestor.child_id = "outer".location_id))
Total runtime: 631.858 ms
(24 rows)
