From: | Arnaud Lesauvage <thewild(at)free(dot)fr> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Index usage btree+gist ? |
Date: | 2006-02-17 09:38:39 |
Message-ID: | 43F5999F.50208@free.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Arnaud Lesauvage | 2006-02-17 09:45:05 | Index usage btree+gist ? |
Previous Message | Christopher Browne | 2006-02-17 05:56:54 | Re: Version 8.1 |