2014-02-12 9:58 GMT+01:00 Katharina Koobs <katharina.ko...@uni-konstanz.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
