Re: [PERFORM] Slow query after 9.3 to 9.6 migration

От: Gerardo Herzig
Тема: Re: [PERFORM] Slow query after 9.3 to 9.6 migration
Дата: ,
Msg-id: 1769239108.5894902.1482893550894.JavaMail.root@fmed.uba.ar
(см: обсуждение, исходный текст)
Ответ на: [PERFORM] Slow query after 9.3 to 9.6 migration  (Flávio Henrique)
Список: pgsql-performance

Скрыть дерево обсуждения

[PERFORM] Slow query after 9.3 to 9.6 migration  (Flávio Henrique, )
 Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Gerardo Herzig, )
 Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Daniel Blanch Bataller, )
 Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Kevin Grittner, )
 Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Merlin Moncure, )
  Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Flávio Henrique, )
   Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Kevin Grittner, )
   Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Daniel Blanch Bataller, )
    Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Peter Geoghegan, )
   Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Merlin Moncure, )
   Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Filipe Oliveira, )
    Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Michael Paquier, )
     Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Filipe Oliveira, )
 Re: [PERFORM] Slow query after 9.3 to 9.6 migration  (Scott Marlowe, )

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



В списке pgsql-performance по дате сообщения:

От: Gerardo Herzig
Дата:
Сообщение: Re: [PERFORM] Slow query after 9.3 to 9.6 migration
От: Daniel Blanch Bataller
Дата:
Сообщение: Re: [PERFORM] Slow query after 9.3 to 9.6 migration