PG 9..5 index performance Q's

Поиск
Список
Период
Сортировка
Искать

PG 9..5 index performance Q's

От:
ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
Дата:
Hi List,

  running PGSQL 9.5.1 ( EL7) ,
we are seeing a issues with one of our SQL statement getting the wrong 
plan once in a while ( large temp tables being generate, exec time   
takes  > 3 minutes compared with fastes plan < 1 min), we want to 
undestand why a new/ expensive plan is been used instead of efficient 
one, there are not data changes on the tables, any tips where to look?
Second , I would like to know if when re-creating one index ( non pk ) 
on  a large table with already existing indexes, are  All tables 
statistics  being re-generated? ( there is a way to control persistency 
of old stats?)

Isabella


Re: PG 9..5 index performance Q's

От:
Igor Neyman <ineyman@perceptron.com>
Дата:

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of ghiureai
Sent: Tuesday, November 08, 2016 1:31 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] PG 9..5 index performance Q's

Hi List,

  running PGSQL 9.5.1 ( EL7) ,
we are seeing a issues with one of our SQL statement getting the wrong 
plan once in a while ( large temp tables being generate, exec time   
takes  > 3 minutes compared with fastes plan < 1 min), we want to undestand why a new/ expensive plan is been used instead of efficient one, there are not data changes on the tables, any tips where to look?
Second , I would like to know if when re-creating one index ( non pk ) on  a large table with already existing indexes, are  All tables statistics  being re-generated? ( there is a way to control persistency of old stats?)

Isabella

_____________________________________________________________________________________________________________________

Without seeing execution plans (both: fast and slow) it's hard to tell.
Auto_explain extension can help you catching these plans in PG log.
Your temp tables - are they being analyzed?

Regards,
Igor Neyman 

FAQ