Re: Using Views as Tables

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David L <segedunum(at)actuaria(dot)co(dot)uk> 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
>