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
Date: Sun, 08 Jan 2017 12:40:26 -0500
Message-ID: <31824.1483897226@sss.pgh.pa.us> (raw)
In-Reply-To: <CANcS_tdziVwWVaFhuiLcXcEKYbuBDu70w7jouzH6aBV2mA2=EQ@mail.gmail.com>
References: <CANcS_tdziVwWVaFhuiLcXcEKYbuBDu70w7jouzH6aBV2mA2=EQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, segedunum@actuaria.co.uk
Subject: Re: Using Views as Tables
In-Reply-To: <31824.1483897226@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox