Cédric Villemain<cedric.villemain.debian@xxxxxxxxx> wrote: > 2011/5/26 panam <panam@xxxxxxx>: >> "max_connections";"100" >> "work_mem";"1GB" Each connection can allocate work_mem, potentially several times. On a machines without hundreds of GB of RAM, that pair of settings could cause severe swapping. >> "Patholgical" query: >> >> select >> b.id, >> (SELECT >> m1.id >> FROM >> message m1 >> LEFT JOIN >> message m2 >> ON ( >> m1.box_id = m2.box_id >> AND m1.id < m2.id >> ) >> WHERE >> m2.id IS NULL >> AND m1.box_id = b.id) >> from >> box b > without explaining further why the antijoin has bad performance > without cluster, I wonder why you don't use this query : > > SELECT b.id, > max(m.id) > FROM box b, message m > WHERE m.box_id = b.id > GROUP BY b.id; > > looks similar and fastest. I think you would need a left join to actually get identical results: SELECT b.id, max(m.id) FROM box b LEFT JOIN message m ON m.box_id = b.id GROUP BY b.id; But yeah, I would expect this approach to be much faster. Rather easier to understand and harder to get wrong, too. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
