[postgis-users] Can I build an index on combinednon-spatial&spatial columns?

[Kemal Bayram]

For what you want to do I don't think partial indexs are the the solution as
you would have too many indicies.  You don't want the query taking more time
going through the metadata then the actual query.

Postgres will use a separate index you have on building type, infact if you
look at EXPLAIN the best case costs are lower with an index on building_type
than on a partial index.  And like I said if performance of this query is
critical you can cluster on the building_type.

You really need to play around with your queries to see what is best.

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of M.A. (Thijs) van den Berg
> Sent: 18 March 2009 14:08
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Can I build an index on 
> combinednon-spatial&spatial columns?
> 
> Thanks, that's true. I think the performance benefit with be 
> good because I have approx 10.000 building_type's (although, 
> that's a lot of indices to choose between). A typical 
> distance query for a specific building_type will result in 
> 10-100 matches. Not using partial indexing will thus result 
> in appox 500.000  rows for each query (all building types), 
> which I would then have to filter on building_type, reducing 
> the resultset with a factor 10.000
> 
> Giving it some more thought, I think splitting the building 
> table into individual building_type tables (and attaching 
> spatial indices to
> those) might be the best solution.
> 
>  From a technical point, I think the postgresql CREATE INDEX 
> should support multiple USING to allow for mixed patial / 
> non-spatial indices, something like this:
> 
> CREATE INDEX blabla ON buildings (USING HASH(building_type), USING
> GIST(the_geom))
> 
> internally that could be rewritten as
> CREATE INDEX blabla_hash_value1 ON buildings USING 
> GIST(the_geom) WHERE hash(building_type)=value1 CREATE INDEX 
> blabla_hash_value2 ON buildings USING GIST(the_geom) WHERE 
> hash(building_type)=value2 ..
> 
> 
> On Mar 18, 2009, at 12:43 PM, Kemal Bayram wrote:
> 
> > Well according to EXPLAIN a partial index is taken into 
> account, how 
> > much of a performance benefit you gain ultimately depends 
> on how many 
> > records lie within your average distance.
> >
> > If your data set is relatively static you may also want to consider 
> > clustering your table too.
> >
> >> -----Original Message-----
> >> From: postgis-users-bounces at postgis.refractions.net
> >> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of 
> >> M.A. (Thijs) van den Berg
> >> Sent: 18 March 2009 13:00
> >> To: PostGIS Users Discussion
> >> Subject: Re: [postgis-users] Can I build an index on combined 
> >> non-spatial&spatial columns?
> >>
> >> Thanks :)
> >>
> >> Does PostGIS support partial indices?
> >> http://www.postgresql.org/docs/8.3/interactive/indexes-partial.html
> >>
> >> If so.. I could do something like this:
> >>
> >> CREATE INDEX building_type_spindx_school ON buildings USING
> >> GIST(the_geom) WHERE building_type='School';
> >>
> >> CREATE INDEX building_type_spindx_bar ON buildings USING 
> >> GIST(the_geom) WHERE building_type='Bar';
> >>
> >> ...
> >> Yet another option would be to partition the buildings table into 
> >> child tables per building_type (ie have child table containing all 
> >> the schools, another child table having all the bars), and have 
> >> individua spatial indices on the child tables. Is that an option?
> >>
> >>
> >>
> >>
> >> On Mar 18, 2009, at 11:36 AM, Kemal Bayram wrote:
> >>
> >>> I don't know about combining indexes but you want to
> >> include a spatial
> >>> operator such as && in addition to Distance so that you can take 
> >>> advantage of an index on "the_geom".
> >>>
> >>>> -----Original Message-----
> >>>> From: postgis-users-bounces at postgis.refractions.net
> >>>> [mailto:postgis-users-bounces at postgis.refractions.net] On
> >> Behalf Of
> >>>> M.A. (Thijs) van den Berg
> >>>> Sent: 18 March 2009 11:57
> >>>> To: postgis-users at postgis.refractions.net
> >>>> Subject: [postgis-users] Can I build an index on combined
> >> non-spatial
> >>>> &spatial columns?
> >>>>
> >>>> Suppose I want to speedup the following  type of queries with an
> >>>> index:
> >>>>
> >>>> SELECT the_geom, building_name
> >>>> FROM buildings
> >>>> WHERE Distance(the_geom, 'POINT(100312 102312)') < 5000  AND 
> >>>> building_type = 'School'
> >>>>
> >>>> Is that possible, and if not, why not? My current option
> >> is to write
> >>>> my own index storage in C++, using a hashmap to filter
> >> building_type,
> >>>> and build separate spatial indices for each building type.
> >> Can I do
> >>>> something similar in postgresql?
> >>>> Would my only option be to split the building_types into 
> separate 
> >>>> tables?
> >>>> _______________________________________________
> >>>> 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
> >>
> >> _______________________________________________
> >> 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
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>