Andreas Wernitznig <andreas@insilico.com> writes:
> I took option 1 and managed to create a profile of a slow and a fast run:
It's difficult to compare these profiles, because they seem to be taken
over very different numbers of queries --- did you let the "fast" run
process more queries than the "slow" one?
However, I think what is happening is that some queries are being done
as indexscans in the fast case and seqscans in the slow case. The
ratio of ExecIndexScan calls to ExecSeqScan calls is vastly different
in the two profiles.
It looks like you are doing vacuums fairly frequently, so I speculate
that the statistics gathered by vacuum are changing just enough to alter
the planner's choice between indexscan and seqscan for some
often-executed query type. Evidently the planner is guessing the costs
to be nearly the same, so a small change in stats might tip the choice
--- but in reality the costs are quite a bit different, thus you observe
fast and slow behavior.
The next step should be to get EXPLAIN results for the queries used
by your application in both fast and slow states. This will help us
narrow down where the planner's misprediction is occurring.
regards, tom lane