Re: Primary key index suddenly became very slow

From: Gustav Karlsson
Subject: Re: Primary key index suddenly became very slow
Date: ,
Msg-id: EC62E940-B42B-4AA3-BC39-8A09D2ECBE9C@bekk.no
(view: Whole thread, Raw)
In response to: Primary key index suddenly became very slow  (Gustav Karlsson)
Responses: Re: Primary key index suddenly became very slow  (jaime soler)
Re: Primary key index suddenly became very slow  (Venkata Balaji N)
List: pgsql-performance

Tree view

Primary key index suddenly became very slow  (Gustav Karlsson, )
 Re: Primary key index suddenly became very slow  (Gustav Karlsson, )
  Re: Primary key index suddenly became very slow  (jaime soler, )
  Re: Primary key index suddenly became very slow  (Venkata Balaji N, )

Additional information:

The problematic row has likely received many hot updates (100k+). Could this be a likely explanation for the high execution time?


Regards,
Gustav



On Feb 8, 2016, at 10:45 AM, Gustav Karlsson <> wrote:

Hi,

Question:

What may cause a primary key index to suddenly become very slow? Index scan for single row taking 2-3 seconds. A manual vacuum resolved the problem.


Background:

We have a simple table ‘KONTO’ with about 600k rows. 


            Column            |            Type             |   Modifiers
------------------------------+-----------------------------+---------------
 id                           | bigint                      | not null
...

Indexes:
    "konto_pk" PRIMARY KEY, btree (id)
...


Over the weekend we experienced that lookups using the primary key index (‘konto_pk’) became very slow, in the region 2-3s for fetching a single record:

QUERY PLAN
Index Scan using konto_pk on konto  (cost=0.42..6.44 rows=1 width=164) (actual time=0.052..2094.549 rows=1 loops=1)
  Index Cond: (id = 2121172829)
Planning time: 0.376 ms
Execution time: 2094.585 ms


After a manual Vacuum the execution time is OK:

QUERY PLAN
Index Scan using konto_pk on konto  (cost=0.42..6.44 rows=1 width=164) (actual time=0.037..2.876 rows=1 loops=1)
  Index Cond: (id = 2121172829)
Planning time: 0.793 ms
Execution time: 2.971 ms


So things are working OK again, but we would like to know what may cause such a degradation of the index scan, to avoid this happening again? (We are using Postgresql version 9.4.4)



Regards,
Gustav


pgsql-performance by date:

From: Merlin Moncure
Date:
Subject: Re: bad COPY performance with NOTIFY in a trigger
From: Jeff Janes
Date:
Subject: Re: Bitmap and-ing between btree and gin?