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?
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 numbersdate_of_work_std_year
. The condition filters out 70% of all rows in that table, so the index would be helpful.explain (analyze, timing)
we could see where exactly the time is spent. With 9.1explain (analyze, buffers
)` might give some hints. It does sound as if the table is bloated though. Did you try avacuum full
? Do you have a lot of sessions that areidle in transaction
? That could be an indication that autovacuum can't do its job properly.