From: | Gustav Karlsson <gustav(dot)karlsson(at)bekk(dot)no> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Cc: | Frode Nerbråten <frode(dot)nerbraten(at)bekk(dot)no> |
Subject: | Re: Primary key index suddenly became very slow |
Date: | 2016-02-08 10:04:58 |
Message-ID: | EC62E940-B42B-4AA3-BC39-8A09D2ECBE9C@bekk.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 <gustav(dot)karlsson(at)bekk(dot)no<mailto:gustav(dot)karlsson(at)bekk(dot)no>> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Mamin | 2016-02-08 10:21:53 | Re: gin performance issue. |
Previous Message | Gustav Karlsson | 2016-02-08 09:45:24 | Primary key index suddenly became very slow |