Re: increasing query time after analyze

Pavel Stehule <pavel.stehule@xxxxxxxxx> · Wed, 12 Feb 2014 10:20:44 +0100

2014-02-12 9:58 GMT+01:00 Katharina Koobs <katharina.koobs@xxxxxxxxxxxxxxx>:

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