On Sun, Jan 8, 2017 at 10:30 AM, David L <segedunum@actuaria.co.uk> wrote:
> [bulk load of data to new table every few months; latest referenced by view]
> This is apparently leading to some confusing results when people are
> querying these views with inconsistent query times. Sometimes queries are
> taking two or three seconds, other times 20 or 30 milliseconds, the latter
> being what we'd expect.
I agree with others that the view has nothing to do with it.
Caching came to mind, but if you have sufficient RAM that the data
from the bulk load remains cached until use, my next two ideas are
statistics or hint bits. Both would be corrected by running VACUUM
FREEZE ANALYZE against the freshly loaded table before modifying
the view to reference it.
If that doesn't help, I suggest trying to create the issue on a
test machine, and seeing what influences the run time. A `vmstat
1` run covering fast and slow cases might help. If necessary,
capture fast and slow plans using EXPLAIN (ANALYZE, BUFFERS,
VERBOSE) both ways, and report as suggested here:
https://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company