I agree that in theory this shouldn't happen as the query planner should be intelligent enough to take the base query out of the equation all together, but this happens on a server where there should be a plentiful supply of memory (32GB) and little enough activity that table data should remain cached.
On 8 January 2017 at 17:40, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
David L <segedunum@xxxxxxxxxxxxxx> writes:
> 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. Naturally, the fact that these are views they are
> querying is the first thing I'm questioning here and I've never seen query
> times of seconds using the table directly on the limited experiments I've
> done.
I think you're barking up the wrong tree. A view as simple as "select *
from something" should get flattened out of the plan entirely. You should
check that with EXPLAIN, but I'd expect that you get identical plans from
querying either the view or the underlying table.
What seems more plausible is that the inconsistent query times have to do
with locking, or with I/O stalls due to table data sometimes not being in
memory. Either of these might be rare enough that you simply didn't see
it in "limited experiments" with direct queries, but they'd apply to that
case just as well.
regards, tom lane
