I had not seen this before, thanks!
I seems from the discussion on this that it does not work with spatial
data. I had the idea that it would be cool if each table kept its
current bbox for the table and that if you were doing a query that it
would only look at the tables with bboxes that overlap the query area of
interest. And if each of the tables were index then you should get
pretty good performance when using reasonably small areas of interest.
This is not really "constraint exclusion".
Any thoughts on doing this?
Would it be useful to support inherited tables with something like this?
or do you already get this if you just build a gist index and cluster on
it?
Can you cluster on a gist index?
With Tiger I think the real issue becomes managing all the data and all
the tables. With 3300+- counties and just and about 5-6 layers per
county plus state and national layers, you are looking an nearly 15-16K
files. Rolling county level data up to the state level improves this a
lot, but disassociates the data from its original file if that is
important to anyone. One use case for know this association is when
Census releases an updated county to fix some internal issues they
found. It would be nice to be able to easily drop that one file or
county and reload the new data. In most cases we are using Tiger as load
once, read a lot and not making edits to it.
-Steve
Paul Ramsey wrote:
Look at "constraint exclusion" as a strategy...
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
It actually *does* use inheritance, but it does fancy query re-writes too.
P.
On Fri, Apr 4, 2008 at 11:10 AM, Stephen Frost <[EMAIL PROTECTED]> wrote:
Stephen,
* Stephen Woodbridge ([EMAIL PROTECTED]) wrote:
> Have look into using inherited tables? This would allow you to have a
> nation view or a state view of the county data. View being used in the
> literal not postgres in this case.
Honestly, I havn't. I'm really not all that keen on inheiritance in PG
anyway. What I'm probably going to do instead though, so you know, is
combine all of the county-level data into state-level tables, and then
create a view across the state tables to generate a national table.
This partitioning should make things go pretty quickly, imv. I'll
certainly let folks know if I'm happy with it or not.
Thanks,
Stephen
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFH9m8DrzgMPqB3kigRAhj1AJ9upuYPkTNrzLQWv74QW1hewjtxJQCfXCZy
feifOYH9dPLDphnwVOd788M=
=izsS
-----END PGP SIGNATURE-----
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
