From: Tom Lane <tgl@sss.pgh.pa.us>
To: David L <segedunum@actuaria.co.uk>
cc: pgsql-admin@postgresql.org
Subject: Re: Using Views as Tables
In-reply-to: 
 <CANcS_tdziVwWVaFhuiLcXcEKYbuBDu70w7jouzH6aBV2mA2=EQ@mail.gmail.com>
References: 
 <CANcS_tdziVwWVaFhuiLcXcEKYbuBDu70w7jouzH6aBV2mA2=EQ@mail.gmail.com>
Comments: In-reply-to David L <segedunum@actuaria.co.uk>
	message dated "Sun, 08 Jan 2017 16:30:48 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <31823.1483897226.1@sss.pgh.pa.us>
Date: Sun, 08 Jan 2017 12:40:26 -0500
Message-ID: <31824.1483897226@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

David L <segedunum@actuaria.co.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


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin