[postgis-users] View or New Table?

[Kevin Neufeld]

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:* postgis-users-bounces at postgis.refractions.net
>     [mailto:postgis-users-bounces at postgis.refractions.net] *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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>