Re: Aggregate and many LEFT JOIN

Thanks for your answers.

I have tried via

--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.

Obviously I have already tried to VACUUM ANALYSE

My current PostgreSQL settings
max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 10485kB
maintenance_work_mem = 512MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100