On Tue, Dec 27, 2016 at 5: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. > > Any tip/help will be much appreciated (even from the query side). > > Thank you! > > The query plan: https://explain.depesz.com/s/5KMn > > Note: I tried to add index on kilo_victor table already, but Postgresql > still thinks that is better to do a seq scan. Hard to provide more without the query or the 'old' plan. Here are some things you can try: *) Set effective_io_concurrency high. You have some heap scanning going on and this can sometimes help (but it should be marginal). *) See if you can get any juice out of parallel query *) try playing with enable_nestloop and enable_seqscan. these are hail mary passes but worth a shot. Run the query back to back with same arguments in the same database session. Does performance improve? Big gains (if any) are likely due to indexing strategy. I do see some suspicious casting, for example: Join Filter: ((four_charlie.delta_tango)::integer = (six_quebec.golf_bravo)::integer) Are you casting in the query or joining through dissimilar data types? I suspect your database team might be incorrect. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
