--show work_mem; "10485kB" -> initial work_mem for my first post -- set session work_mem='100000kB'; -- set session geqo_threshold = 12; -- set session join_collapse_limit = 15;
I have a small machine, with SSD disk and 8GB RAM. I cannot really increase work_mem up to 2GB (or more). There are only 300 000 data in occtax.observation, which will increase (and possibly go up to 3 millions...)
I am running PostgreSQL 9.6. I should probably test it against PostgreSQL 11 as many improvements has been made.
I even tried to remove all non aggregated columns and keep only o.cle_obs (the primary key) to have a
GROUP BY o.cle_obs
AND the query plan does not show a HASH AGGREGATE, but only a GROUP AGGREGATE.