Thread: Index usage btree+gist ?
Hi List ! I have a table with a lot of rows (~3.000.000 I believe), and two indexes. The first one is a BTree index on a column (lets call it btreecolumn) which contains only 8 different integer values (from 0 to 8). The second one is a Gist index on a geometry column (gistcolumn) in PostGIS format. I run a query on this table that looks like : SELECT gistcolumn FROM mytable WHERE btreecolumn=0 AND (SELECT AGeometry FROM anothertable) && gistcolumn; EXPLAIN on this query tells me : Index Scan using gistcolumn_gist on table (cost=13.52..188.20 rows=1 width=136)" Index Cond: ($0 && gistcolumn)" Filter: ((btreecolumn = 0) AND ($0 && gistcolumn))" InitPlan" -> Aggregate (cost=13.51..13.52 rows=1 width=32)" -> Seq Scan on anothertable (cost=0.00..13.50 rows=1 width=32)" Filter: ((somecolumn)::text = 'value'::text)" So if I understand this correctly, only the Gist index is used here ? I thought that first using the Btree index to filter some data, then the Gist index to refine the result would have been more efficient ? Am I correct, or am I misinterpreting the EXPLAIN result ? If not, what is wrong with my index or my query ? Thanks for your help ! Regards -- Arnaud
Arnaud Lesauvage <thewild@freesurf.fr> writes: > I have a table with a lot of rows (~3.000.000 I believe), and two > indexes. > The first one is a BTree index on a column (lets call it > btreecolumn) which contains only 8 different integer values (from > 0 to 8). An index as unselective as that is almost useless. It's not surprising that the planner doesn't think it's worth the trouble to use it. regards, tom lane
Tom Lane a écrit : > Arnaud Lesauvage <thewild@freesurf.fr> writes: >> I have a table with a lot of rows (~3.000.000 I believe), and two >> indexes. >> The first one is a BTree index on a column (lets call it >> btreecolumn) which contains only 8 different integer values (from >> 0 to 8). > > An index as unselective as that is almost useless. It's not surprising > that the planner doesn't think it's worth the trouble to use it. OK, thanks for pointing this out. I was advised the PostGis list to use a multicolumn index on both the integer column and the geometry column. Another suggestion was to cluster the table on the gist-geometry index. What do you think about that ? Regards -- Arnaud
Arnaud Lesauvage <thewild@freesurf.fr> writes: > I was advised the PostGis list to use a multicolumn index on both > the integer column and the geometry column. You could try that (put the geometry column first!). I'm not sure how effective additional columns in a gist index really are, but it's worth experimenting with. regards, tom lane
Tom Lane a écrit : > Arnaud Lesauvage <thewild@freesurf.fr> writes: >> I was advised the PostGis list to use a multicolumn index on both >> the integer column and the geometry column. > > You could try that (put the geometry column first!). I'm not sure how > effective additional columns in a gist index really are, but it's worth > experimenting with. OK, I'll try this first then ! Thanks Tom ! Regards -- Arnaud