Re: increasing query time after analyze

2014-02-12 9:58 GMT+01:00 Katharina Koobs <katharina(dot)koobs(at)uni-konstanz(dot)de>:

> explain.depesz.com/s/HuZ

fast query is fast due intesive use a hashjoins

but you can see

Hash Left Join (cost=9343.05..41162.99 rows=6889 width=1350) (actual
time=211.767..23519.296 rows=639137 loops=1)

a estimation is out. Is strange so after ANALYSE a estimation is worse

Nested Loop Left Join (cost=1122.98..28246.98 rows=1 width=1335) (actual
time=33.222..14631581.741 rows=639137 loops=1)

So it looks some in data is strange - probably dependency between columns:
sos_stg_aggr.stichtag = sos_stichtag.tid, sos_stg_aggr.stuart = cifx.apnr

Hash Join (cost=1121.04..24144.02 rows=57 width=339) (actual
time=2.407..11157.151 rows=639221 loops=1)

- Hash Cond: (sos_stg_aggr.stuart = cifx.apnr)

Nested loop based plan is extremely sensitive to this wrong estimation.

You can try:

* penalize nested loop - set enable_nested_loop to off; -- for this query
* divide this query to more queries - store result temporary table and
analyze (fix wrong estimation)
* maybe you can increase a work_mem

Regards

Pavel Stehule