Re: increasing query time after analyze

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