The only caveat there is that constraint exclusion only works with range queries and range constraints or at least I haven't had success getting it work with spatial indexes.
So if you put a constraint on state code or something of that sort, it will work great. But for super performance a bounding box constraint exclusion would be even better. Interestingly enough there does appear to be some speed benefit (at least I've noticed) with using inheritance even without constraint exclusion. My guess is something along the line of parallel reads. It would be really neat if spatial indexes could work with constraint exclusion. Has anyone found a way around this? I have a couple of thoughts on the matter which unfortunately involve introducing helper functions that will flip a bounding box constraint into a range constraint. Haven't fleshed it out completely though. Thanks, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Ramsey Sent: Friday, April 04, 2008 2:18 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Loading Tiger2007fe data - Thoughts on usinginherited tables 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 ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
