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

Wed Mar 18 06:15:35 PDT 2009

I thinks you're right about the 'too many indices' , I'm worried about  
that too. I'll have to do some test's now to see what would work.  
Thanks!
..anyway, one thing I've learned is that there are no compound spatial/ 
non-spatial indices possibilities.

On Mar 18, 2009, at 1:43 PM, Kemal Bayram wrote:

> 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
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users