Unfortunately, Stanley, I'm going to have to politely disagree. If a
view is set up to be a filter on a table (even with proper indexes in
place), it will always be slower than looking at the same data in a
materialized view. At the very least, you'll have the overhead of the
view's filter applied to every query written.
Depending on the query, you may also get much worse results with a view
than using a separate table.
Consider a query that is interested in the entire contents of the view's
resultset. (IE. SELECT count(*) FROM my_view). In this case, if the
view represents only a small portion of the data from much larger table,
the query planner will most likely opt to perform an index scan,
retrieving the rows of interest. On the other hand, if the view were
materialized into a table, the query planner would choose a sequential
scan which is significantly faster than performing an index scan over
the same dataset.
This is one of the ideas behind table inheritance - break a large table
into smaller, quicker sub-tables by separating most often / least often
used data.
Dylan, I would advise that you perform some timings on your most often
used queries, comparing a view and an equivalent materialized view.
Perhaps the incurred performance penalties is not worth your bother to
create and maintain duplicate datasets. But then again ...
Cheers,
Kevin
Sufficool, Stanley wrote:
I would think this depends on your back-end storage. If you have
substantial backend storage, then the seek time on a view using a
constraint on an indexed column should be negligible.
However, if you are running a single disk, the separate
(non-fragmented) table _may_ be slightly faster.
This is based on no knowledge of how PostgreSQL breaks up it's table
spaces into file system functions. Most ORDBMS's have files for index
and files for data (or in MS, one huge file) which causes non-cached
index data to cause moderate disk thrashing on initial scans. However
once the index is cached, you are only looking at seek times between
rows/sectors in the data.
If anyone wishes to tell me that I'm talking out my ----, please do. I
would like to know what PG optimizes in cases like these.
-----Original Message-----
*From:* [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] *On Behalf
Of *Dylan Lorimer
*Sent:* Wednesday, April 30, 2008 1:54 PM
*To:* PostGIS Users Discussion
*Subject:* [postgis-users] View or New Table?
Hi,
Perhaps an obvious question, but I was wondering if there is a
huge speed hit when running queries against a table vs running the
same queries against a view of a larger table constrained to the
data that is pertinent.
Concrete example: millions of points all over the world, but I
only care about those over Africa. I could either create a new
table storing only those points over Africa, or I could create a
view on the original table that is constrained by a join on a
table containing the borders of the African continent.
If I have requisite indexes in place, will it still be faster to
use the new table instead of the view?
Cheers,
dylan
--
Dylan Lorimer | Strategic Partner Management
415.573.2909 (Grand Central) | 650.644.0182 (Fax)
If you received this communication by mistake, please don't
forward it to anyone else (it may contain confidential or
privileged information), please erase all copies of it, including
all attachments, and please let the sender know it went to the
wrong person. Thanks.
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
