Re: multi-layered view join performance oddities

Hi.

Your suggestion with disableing the nested loop really worked well:

rkr=# set enable_nestloop=false;
SET
rkr=# explain analyze select * from ord_result_pct_pretty ;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=230.06..337.49 rows=1 width=174) (actual
time=21.893..42.356 rows=2250 loops=1)
Hash Cond: (("outer".dataset_id = "inner".dataset_id) AND
("outer".nb_property_type_id = "inner".nb_property_type_id))
-> Hash Join (cost=56.94..164.10 rows=26 width=93) (actual
time=5.073..17.906 rows=2532 loops=1)
Hash Cond: ("outer".dataset_id = "inner".id)
-> Hash Join (cost=55.54..161.63 rows=161 width=57) (actual
time=4.996..14.775 rows=2532 loops=1)
Hash Cond: ("outer".institut = "inner".id)
-> Append (cost=54.38..121.72 rows=2476 width=44)
(actual time=4.964..11.827 rows=2532 loops=1)
-> HashAggregate (cost=54.38..57.20 rows=226
width=16) (actual time=4.964..5.174 rows=282 loops=1)
-> Seq Scan on ord_entrydata_current
(cost=0.00..37.50 rows=2250 width=16) (actual time=0.002..1.305
rows=2250 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..60.00
rows=2250 width=20) (actual time=0.009..4.948 rows=2250 loops=1)
-> Seq Scan on ord_entrydata_current
(cost=0.00..37.50 rows=2250 width=20) (actual time=0.003..2.098
rows=2250 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=17) (actual
time=0.022..0.022 rows=13 loops=1)
-> Seq Scan on groups g (cost=0.00..1.13 rows=13
width=17) (actual time=0.003..0.013 rows=13 loops=1)
-> Hash (cost=1.32..1.32 rows=32 width=36) (actual
time=0.070..0.070 rows=32 loops=1)
-> Seq Scan on ord_dataset od (cost=0.00..1.32 rows=32
width=36) (actual time=0.009..0.043 rows=32 loops=1)
Filter: is_visible
-> Hash (cost=173.07..173.07 rows=10 width=97) (actual
time=15.472..15.472 rows=256 loops=1)
-> Hash Join (cost=166.15..173.07 rows=10 width=97) (actual
time=14.666..15.203 rows=256 loops=1)
Hash Cond: ("outer".nb_property_type_id = "inner".id)
-> HashAggregate (cost=165.05..168.15 rows=248
width=40) (actual time=14.619..14.849 rows=288 loops=1)
-> Append (cost=54.38..121.72 rows=2476 width=44)
(actual time=5.012..11.130 rows=2532 loops=1)
-> HashAggregate (cost=54.38..57.20
rows=226 width=16) (actual time=5.011..5.222 rows=282 loops=1)
-> Seq Scan on ord_entrydata_current
(cost=0.00..37.50 rows=2250 width=16) (actual time=0.001..1.261
rows=2250 loops=1)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..60.00 rows=2250 width=20) (actual time=0.010..4.308
rows=2250 loops=1)
-> Seq Scan on ord_entrydata_current
(cost=0.00..37.50 rows=2250 width=20) (actual time=0.002..1.694
rows=2250 loops=1)
-> Hash (cost=1.08..1.08 rows=8 width=57) (actual
time=0.026..0.026 rows=8 loops=1)
-> Seq Scan on nb_property_type npt
(cost=0.00..1.08 rows=8 width=57) (actual time=0.004..0.019 rows=8 loops=1)
Total runtime: 43.297 ms
(28 rows)

Now, the whole question becomes, how do I get the planner to make a
better estimation of the returned rows.

I am not sure, I can follow your moving-the-union-all-further-out
advice, as I see no different place for the unioning of the two datasets.

Maybe one of the core devs know, where to fiddle :)

Svenne

Steinar H. Gunderson wrote:

>On Sun, Oct 30, 2005 at 06:16:04PM +0100, Svenne Krap wrote:
>
>
>> Nested Loop (cost=223.09..338.61 rows=1 width=174) (actual time=20.213..721.361 rows=2250 loops=1)
>> Join Filter: (("outer".dataset_id = "inner".dataset_id) AND ("outer".nb_property_type_id = "inner".nb_property_type_id))
>> -> Hash Join (cost=58.04..164.26 rows=1 width=150) (actual time=5.510..22.088 rows=2250 loops=1)
>>
>>
>
>There's horrible misestimation here. It expects one row and thus starts a
>nested loop, but gets 2250. No wonder it's slow :-)
>
>The misestimation can be traced all the way down here:
>
>
>
>> Hash Cond: ("outer".institut = "inner".id)
>> -> Hash Join (cost=56.88..163.00 rows=16 width=137) (actual time=5.473..19.165 rows=2250 loops=1)
>> Hash Cond: ("outer".dataset_id = "inner".id)
>> -> Hash Join (cost=55.48..160.95 rows=99 width=101) (actual time=5.412..16.264 rows=2250 loops=1)
>>
>>
>
>where the planner misestimates the selectivity of your join (it estimates 99
>rows, and there are 2250).
>
>I've had problems joining with Append nodes in the past, and solved the
>problem by moving the UNION ALL a bit out, but I'm not sure if it's a very
>good general solution, or a solution to your problems here.
>
>If all else fails, you could "set enable_nestloop=false", but that is not a
>good idea in the long run, I'd guess -- it's much better to make sure the
>planner has good estimates and let it do the correct decisions from there.
>
>/* Steinar */
>
>