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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] On
Behalf Of
M.A. (Thijs) van den Berg
Sent: 18 March 2009 11:57
To: [email protected]
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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
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
_______________________________________________
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
_______________________________________________
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
