<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.6000.16640" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=997081021-30042008><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=997081021-30042008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=997081021-30042008><FONT face=Arial color=#0000ff
size=2>However, if you are running a single disk, the separate
(non-fragmented) table _may_ be slightly faster.</FONT></SPAN></DIV>
<DIV><SPAN class=997081021-30042008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=997081021-30042008><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=997081021-30042008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=997081021-30042008><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<BLOCKQUOTE
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Dylan Lorimer<BR><B>Sent:</B> Wednesday, April 30, 2008 1:54
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> [postgis-users]
View or New Table?<BR><BR></FONT></DIV>Hi,<BR><BR>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.<BR><BR>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.<BR><BR>If I have requisite
indexes in place, will it still be faster to use the new table instead of the
view?<BR>Cheers,<BR>dylan<BR clear=all><BR>-- <BR>Dylan Lorimer | Strategic
Partner Management<BR>415.573.2909 (Grand Central) | 650.644.0182
(Fax)<BR><BR>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.
</BLOCKQUOTE></BODY></HTML>