[postgis-users] Loading Tiger2007fe data - Thoughts on usinginherited tables

[Obe, Regina]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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 <sfrost at snowman.net>
wrote:
> Stephen,
>
>
>  * Stephen Woodbridge (woodbri at swoodbridge.com) 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
>  postgis-users at postgis.refractions.net
>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
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.