Thread: Planner tuning

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)

Alban Hertroys wrote:
> 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?

Ok, I think I've figured this one out. A few order by's after the groups
in my subqueries helped a _lot_. I'm now back under 1s again.

> 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).
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


--
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 //

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

Tom Lane wrote:
> 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:

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=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.

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@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 //

QUERYPLAN
         

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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_monthproperty_availability_month  (cost=0.00..3893.52 rows=34992 width=12) (actual
time=0.023..147.269rows=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=3522loops=1) 
                                 ->  Bitmap Heap Scan on fewo_period_type_property period_type_property
(cost=23.03..253.01rows=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.03rows=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=4881width=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.19rows=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.33rows=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)

I don't know exactly is it your case, but sometimes

SET enable_sort = off;

speeds up some queries by the factor of hundred. But in some cases this command slows down operations, so I TEMPORARILY switch enable_sort on and off for some queries. It affects the query plan greatly.

Dmitry Koterov wrote:
> I don't know exactly is it your case, but sometimes
>
> SET enable_sort = off;
>
> speeds up some queries by the factor of hundred. But in some cases this
> command slows down operations, so I TEMPORARILY switch enable_sort on and
> off for some queries. It affects the query plan greatly.

As you may have noticed, there's no sort in my query plan ;)

Seriously, I do not intend to use options like these in production
queries. They're not meant to be used like that.

It usually means you're trying to do something more complicated than
necessary - I achieved improved results by cleaning up my design,
removing hacks that appeared useful while we were still using PG 7.4.

--
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 //