pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing Views as Tables
6+ messages / 4 participants
[nested] [flat]
* Using Views as Tables
@ 2017-01-08 16:30 David L <segedunum@actuaria.co.uk>
2017-01-08 17:40 ` Re: Using Views as Tables Tom Lane <tgl@sss.pgh.pa.us>
2017-01-09 14:55 ` Re: Using Views as Tables Kevin Grittner <kgrittn@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: David L @ 2017-01-08 16:30 UTC (permalink / raw)
To: pgsql-admin
Hi All,
For some reason I'm having a hard time getting over to some developers that
assuming that you can use a view in Postgres as you would use a table is
probably a bad idea. This is in versions of Postgres before 9.3, 9.1 and
8.3, where materialized views came in, just to clarify.
As some background, there are a number of tables containing completely
static data that is updated every few months via a batch import into
different tables by date - table_201603 or table_201607. A view has then
been created called 'table' which clients then use which is just a 'SELECT
* FROM' of the table. When an updated batch of data is put into a new table
the view is then updated to point at the new table for consistency. This
means an in-place rename of the table does not need to take place that
might mean downtime. These tables generally have about 100 million rows in
them.This is geospatial data, so they're doing geospatial queries on a
view, whatever difference that might make.
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.
What are the pitfalls of using plain views like this and is there a refresh
interval to them of some kind which would account for the differing query
times? Is there a better way of doing this prior to 9.3? Naturally, a
materialized view would be better for this but an upgrade might not happen
in the near future.
Thanks in advance,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using Views as Tables
2017-01-08 16:30 Using Views as Tables David L <segedunum@actuaria.co.uk>
@ 2017-01-08 17:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-01-08 18:35 ` Re: Using Views as Tables David L <segedunum@actuaria.co.uk>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2017-01-08 17:40 UTC (permalink / raw)
To: David L <segedunum@actuaria.co.uk>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using Views as Tables
2017-01-08 16:30 Using Views as Tables David L <segedunum@actuaria.co.uk>
2017-01-08 17:40 ` Re: Using Views as Tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-01-08 18:35 ` David L <segedunum@actuaria.co.uk>
2017-01-09 08:22 ` Re: Using Views as Tables David L <segedunum@actuaria.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: David L @ 2017-01-08 18:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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@sss.pgh.pa.us> wrote:
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using Views as Tables
2017-01-08 16:30 Using Views as Tables David L <segedunum@actuaria.co.uk>
2017-01-08 17:40 ` Re: Using Views as Tables Tom Lane <tgl@sss.pgh.pa.us>
2017-01-08 18:35 ` Re: Using Views as Tables David L <segedunum@actuaria.co.uk>
@ 2017-01-09 08:22 ` David L <segedunum@actuaria.co.uk>
2017-01-09 08:28 ` Re: Using Views as Tables Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: David L @ 2017-01-09 08:22 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Yes I know. That's the point.
On 9 Jan 2017 1:46 a.m., "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
> On Sun, Jan 8, 2017 at 11:35 AM, David L <segedunum@actuaria.co.uk> wrote:
> > 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.
>
> I'm not sure you understand the underlying method for views in postgresql.
>
> Select * from view;
>
> is literally changed into
>
> select * from (yourviewdef here).
>
> I.e. the query planner does NOTHING different for a select from a view
> than if you just run the query that makes the up the view by hand.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using Views as Tables
2017-01-08 16:30 Using Views as Tables David L <segedunum@actuaria.co.uk>
2017-01-08 17:40 ` Re: Using Views as Tables Tom Lane <tgl@sss.pgh.pa.us>
2017-01-08 18:35 ` Re: Using Views as Tables David L <segedunum@actuaria.co.uk>
2017-01-09 08:22 ` Re: Using Views as Tables David L <segedunum@actuaria.co.uk>
@ 2017-01-09 08:28 ` Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: Simon Riggs @ 2017-01-09 08:28 UTC (permalink / raw)
To: David L <segedunum@actuaria.co.uk>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
> On 9 Jan 2017 1:46 a.m., "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>> I.e. the query planner does NOTHING different for a select from a view
>> than if you just run the query that makes the up the view by hand.
On 9 January 2017 at 08:22, David L <segedunum@actuaria.co.uk> wrote:
> Yes I know. That's the point.
At the moment, nobody can see a reason for the behaviour you are describing.
To take this further you'd need to show evidence or a repeatable test
that showed the effect you are describing is real, even if nobody here
can understand what would be causing it, yet.
Perhaps try executing your test case a million times and report on any
tests that are very substantially longer in the way you describe?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using Views as Tables
2017-01-08 16:30 Using Views as Tables David L <segedunum@actuaria.co.uk>
@ 2017-01-09 14:55 ` Kevin Grittner <kgrittn@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2017-01-09 14:55 UTC (permalink / raw)
To: David L <segedunum@actuaria.co.uk>; +Cc: pgsql-admin
On Sun, Jan 8, 2017 at 10:30 AM, David L <segedunum@actuaria.co.uk> wrote:
> [bulk load of data to new table every few months; latest referenced by view]
> 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.
I agree with others that the view has nothing to do with it.
Caching came to mind, but if you have sufficient RAM that the data
from the bulk load remains cached until use, my next two ideas are
statistics or hint bits. Both would be corrected by running VACUUM
FREEZE ANALYZE against the freshly loaded table before modifying
the view to reference it.
If that doesn't help, I suggest trying to create the issue on a
test machine, and seeing what influences the run time. A `vmstat
1` run covering fast and slow cases might help. If necessary,
capture fast and slow plans using EXPLAIN (ANALYZE, BUFFERS,
VERBOSE) both ways, and report as suggested here:
https://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-01-09 14:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-08 16:30 Using Views as Tables David L <segedunum@actuaria.co.uk>
2017-01-08 17:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-01-08 18:35 ` David L <segedunum@actuaria.co.uk>
2017-01-09 08:22 ` David L <segedunum@actuaria.co.uk>
2017-01-09 08:28 ` Simon Riggs <simon@2ndquadrant.com>
2017-01-09 14:55 ` Kevin Grittner <kgrittn@gmail.com>
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