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.karls...@bekk.no<mailto:gustav.karls...@bekk.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
