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

Search Discussions

  • Arnaud Lesauvage at Feb 17, 2006 at 9:45 am
    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
  • Tom Lane at Feb 17, 2006 at 2:58 pm

    Arnaud Lesauvage 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
  • Arnaud Lesauvage at Feb 17, 2006 at 3:06 pm

    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
  • Tom Lane at Feb 17, 2006 at 3:21 pm

    Arnaud Lesauvage 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
  • Arnaud Lesauvage at Feb 17, 2006 at 3:24 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 17, '06 at 9:38a
activeFeb 17, '06 at 3:24p
posts6
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase