[postgis-users] View or New Table?

[Colin Wetherbee]

Kevin Neufeld wrote:
> Depending on the query, you may also get much worse results with a view 
> than using a separate table.

+1.  Views have their place, but in this case, it seems like inheritance 
would be a much better approach. (Or, see below.)

> 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.

If one cares only about a single continent at a time, a reasonable 
approach would be to put data related to each continent into its own 
table, bound by inheritance to a larger "parent" table.

> 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 ...

Indeed.

Another thing to consider...

If you only care about Africa, you could add an index on the main table 
that compares each point with the bounding polygon for Africa. 
Effectively, the table would then "know" which points are in Africa and 
which aren't, and a VIEW wouldn't have to make the comparison for each 
row.  This isn't necessarily as clean as inheritance, and it all depends 
on the data set and requirements, but I'm just throwing it out there as 
an idea.  This, for example, probably would not be a good idea if you 
care about *all* continents but only one at a time, since you'd have to 
add an index for each of the other continents, too.

Colin