Thanks for your answers. I tried with > set session work_mem='250MB'; > set session geqo_threshold = 20; > set session join_collapse_limit = 20;
It seems to have no real impact : https://explain.depesz.com/s/CBVd Indeed an index cannot really be used for sorting here, based on the complexity of the returned fields. Wich strikes me is that if I try to simplify it a lot, removing all data but the main table (occtax.observation) primary key cd_nom and aggregate, the query plan should be able tu use the cd_nom index for sorting and provide better query plan (hash aggregate), but it does not seems so : * SQL ; http://paste.debian.net/hidden/c3ee7889/ * EXPLAIN : https://explain.depesz.com/s/FR3h -> a group aggregate is used, which : GroupAggregate 1 10,639.313 ms 72.6 % It is better, but I think 10s for such a query seems bad perf for me. Regards Michaël Le ven. 22 févr. 2019 à 19:06, Tom Lane <t...@sss.pgh.pa.us> a écrit : > Michael Lewis <mle...@entrata.com> writes: > > Does the plan change significantly with this- > > set session work_mem='250MB'; > > set session geqo_threshold = 20; > > set session join_collapse_limit = 20; > > Yeah ... by my count there are 16 tables in this query, so raising > join_collapse_limit to 15 is not enough to ensure that the planner > considers all join orders. Whether use of GEQO is a big problem > is harder to say, but it might be. > > regards, tom lane >
