On Tue, Dec 27, 2016 at 3:50 PM, Flávio Henrique <yoshimit@xxxxxxxxx> wrote: > Hi there, fellow experts! > > I need an advice with query that became slower after 9.3 to 9.6 migration. > > First of all, I'm from the dev team. > > Before migration, we (programmers) made some modifications on query bring > it's average time from 8s to 2-3s. > > As this query is the most executed on our system (it builds the user panel > to work), every bit that we can squeeze from it will be nice. > > Now, after server migration to 9.6 we're experiencing bad times with this > query again. > > Unfortunately, I don't have the old query plain (9.3 version) to show you, > but in the actual version (9.6) I can see some buffers written that tells me > that something is wrong. > > Our server has 250GB of memory available, but the database team says that > they can't do nothing to make this query better. I'm not sure, as some > buffers are written on disk. The second sorts etc start spilling to disk your performance is gonna tank. Try increasing work_mem to something moderate like 256M to 1G. Note that work_mem is per sort / action, so if you got 100 users running queries with 2 or 3 sorts at a time you can exhaust memory real fast. OTOH, a db with proper pooling on connections etc (aka 10 to 20 live connections at a time) cna easily handle 1G work_mem if it's got 256G RAM -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
