I'm using an ancient version of postgresql (7.3 - don't ask) and I'm
experiencing a problem where many inserts into an empty table slow down
over time, even if I analyze in the middle of the insertions.
pg_stat_user_tables shows lots and lots of full scans, which explains the
slowdown, but leads me to wonder why the full scans. Each insert is its
own transaction. This table has at least one unique index on it, and a
couple other multi-column indexes, so I was wondering if maybe that was
what was causing the full scans. But a an explain shows that a select for
that unique column will use the index, so now I'm wondering if maybe:
- 7.3 isn't smart enough to use an index on an insert? Seems unlikely.
- I have to start a new session to use new planner stats, even though each
insert is in it's own transaction?
Something I haven't verified for myself yet but have been told by others
is that if I start these inserts quickly after installing a new database
cluster (which probably translates as: before running "vacuum analyze" for
the first time), then these slowdowns do not occur. I can't figure out why
that might be.
Any other thoughts?