Index usage btree+gist ?

Поиск
Список
Период
Сортировка
От Arnaud Lesauvage
Тема Index usage btree+gist ?
Дата
Msg-id 43F59B21.4060201@freesurf.fr
обсуждение исходный текст
Ответы Re: Index usage btree+gist ?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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


В списке pgsql-novice по дате отправления:

Предыдущее
От: "Sean Davis"
Дата:
Сообщение: Re: Queries to Export
Следующее
От: John DeSoi
Дата:
Сообщение: Re: Version 8.1