From: | David L <segedunum(at)actuaria(dot)co(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Using Views as Tables |
Date: | 2017-01-08 18:35:12 |
Message-ID: | CANcS_tcaSNYMxxvS5_kwjmp8TaDbkaeT8tpLCQCkKExmSxpjDg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | David L | 2017-01-09 08:22:42 | Re: Using Views as Tables |
Previous Message | Tom Lane | 2017-01-08 17:40:26 | Re: Using Views as Tables |