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
