[postgis-users] View or New Table?

[Sufficool, Stanley]

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. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080430/75d31626/attachment.html>