PostgreSQL Performance – Slow Select Query After Table Alter and Update

postgresqlpostgresql-9.1

I have postgresql 9.1.14 (upgraded a few weeks ago from 9.1.3, but no problems up till now).

I've recently altered several tables by adding a new UUID column and have been updating these UUIDs to match another unrelated system. This was a large (around 100'000) update for the system, which only usually has small changes.

One of the tables I updated had a trigger on it to update another table (which is used for querying) this table now runs SELECT queries upwards of 10+ times slower than it did before the update. The original tables I changed do not show any slowdown for select statements. The queries are not complicated, for instance:

SELECT iwork_id,
FROM cofk_union_queryable_work
WHERE date_of_work_std_year > 1800;

I've tried reindex, vacuum and analyse this table but no change. The table has only ever had a couple of indexes related to IDs, not to the columns which are queried on.

Running EXPLAIN (ANALYSE, BUFFERS, VERBOSE) on this database:

 Seq Scan on public.cofk_union_queryable_work  (cost=0.00..357332.10 rows=7198 width=4) (actual time=9451.804..32923.487 rows=7434 loops=1)
   Output: iwork_id
   Filter: (cofk_union_queryable_work.date_of_work_std_year > 1800)
   Buffers: shared hit=857 read=354888
 Total runtime: 32924.968 ms

This table contains about 100'000 entries.

Any thoughts on other lines of investigation?

Best Answer

  1. There is no reasonable way to determine why a seqscan is slower shy of bloat and dead rows. Removing the dead rows is simple. Try VACUUM FULL cofk_union_queryable_work.
  2. Consider indexing the columns to avoid the seq scan entirely. When you say something like this "this was a large (around 100'000) update for the system, which only usually has small changes" That's a clue there should be indexes. The only reason not to index, is to save space and write/speed. If you're not writing, index away! Feel free to \d table and update your question if you need help.
  3. Your version of PostgreSQL is olldddd. 9.1 was released on 2011-09-11. That's Shark Night 3D old

Most of this stuff was said by @a_horse_with_no_name, like usual, and in the comments. Also, like usual.

Related Question