[postgis-users] View or New Table?
Colin Wetherbee
cww at denterprises.org
Fri May 2 08:27:25 PDT 2008
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
More information about the postgis-users
mailing list