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

[Stephen Frost]

* Obe, Regina (robe.dnd at cityofboston.gov) wrote:
> 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.

Certainly that's a problem I've also noticed.  One good thing is that
GIST should be able to very quickly pick up on if the bounding box being
requested covers any part of the indexed data or not.  So while you have
to touch 50 indexes, the ones where the bounding box doesn't overlap the
index at all should be very quickly skipped over.

Not as fast as constraint exclusion, but not horrible either.

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

Indeed.  If we can figure out a way to do it at planner time and not
have to touch the indexes at all, that'd be great.

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

I'd be interested in real, repeatable, performance metrics which show
this.  Certainly I'm not aware of PG doing any kind of multi-threaded
reads and views will benefit from the shared memory cache just the same
as inheritance.

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

I'm not sure if helper functions would work or not...  I'd have to play
with it and see what's going on.  Of course, if anyone's done this or
has ideas about it, we're all ears...

	Thanks!

		Stephen

> -----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.
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080404/9ce389b7/attachment.pgp>