[postgis-users] RE: View or New Table?

[Steve Grey]

Hi,

Another method might be to create a new (spatial) index with a where clause
and feeding exactly that clause into your query - the planner should then
decide to use this partial index (check constraint exclusion settings also)
and you won't have to do anything to the data itself.  I've got this to work
with a spatial index and a where clause against a boolean field but haven't
tried it with a spatial where clause.

Steve



*>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
>> *
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080502/9057c02d/attachment.html>