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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster