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;
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.
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?