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?

  • @a_horse_with_no_name Thanks for the suggestion, I've updated with new EXPLAIN details. – Matthew Wilcoxson Mar 8 '17 at 15:07
  • 1
    What data type is date_of_work_std_year Is there an index on that column? If that column is an integer, then use a proper number literal in the query: WHERE date_of_work_std_year > 1800; single quotes are for character constants, not for numbers – a_horse_with_no_name Mar 8 '17 at 15:13
  • It is an integer but it doesn't change the overall wait time if I change the query to use an integer. And there's no index on that. The table contains about 30 different columns some are strings, some integers, while before it had no problem returning results from any of these, now it struggles on most (though not all). – Matthew Wilcoxson Mar 8 '17 at 15:27
  • 1
    Well the obvious way to improve the performance of that query is to create an index on date_of_work_std_year. The condition filters out 70% of all rows in that table, so the index would be helpful. – a_horse_with_no_name Mar 8 '17 at 16:17
  • 4
    A Seq Scan for just 100'000 rows in 30 seconds seems rather slow. With a more up-to-date version of Postgres (9.1 is no longer supported) and explain (analyze, timing) we could see where exactly the time is spent. With 9.1 explain (analyze, buffers)` might give some hints. It does sound as if the table is bloated though. Did you try a vacuum full? Do you have a lot of sessions that are idle in transaction? That could be an indication that autovacuum can't do its job properly. – a_horse_with_no_name Mar 8 '17 at 16:18
2
  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.

  • 2
    Thanks for the answer but I'd feel mean not to give credit to @a_horse_with_no_name . Having said that, your other two points are acknowledged! (I'll try to update to at least imdb.com/title/tt2724064 ) – Matthew Wilcoxson Mar 8 '17 at 18:34
  • 1
    On second thoughts I prefer questions to be marked as completed when it's possible. :) – Matthew Wilcoxson Mar 9 '17 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.