Re: Slow query after 9.3 to 9.6 migration

From: Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar>
To: Flávio Henrique <yoshimit(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow query after 9.3 to 9.6 migration
Date: 2016-12-28 02:52:30
Message-ID: 1769239108.5894902.1482893550894.JavaMail.root@fmed.uba.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

I dont know about the data distribution in kilo_victor, but maybe a partial index
ON kilo_victor (juliet_romeo) where not xray_seven
?

Gerardo

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Daniel Blanch Bataller 2016-12-28 13:11:33 Re: Slow query after 9.3 to 9.6 migration
Previous Message Valerii Valeev 2016-12-28 00:02:55 Re: why we do not create indexes on master