Обсуждение: [ADMIN] Using Views as Tables
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,
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
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
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.
> 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
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