Search code examples
postgresqlpostgis

create 2 indexes on same column

I have a table with geometry column.

I have 2 indexes on this column:

create index idg1 on tbl using gist(geom)

create index idg2 on tbl using gist(st_geomfromewkb((geom)::bytea))

I have a lot of queries using the geom (geometry) field.

  1. Which index is used ? (when and why)
  2. If there are two indexes on same column (as I show here), can the select queries run slower than define just one index on column ?
Solution

  • The use of an index depends on how the index was defined, and how the query is invoked. If you SELECT <cols> FROM tbl WHERE geom = <some_value>, then you will use the idg1 index. If you SELECT <cols> FROM tabl WHERE st_geomfromewkb(geom) = <some_value>, then you will use the idg2 index.

    A good way to know which index will be used for a particular query is to call the query with EXPLAIN (i.e., EXPLAIN SELECT <cols> FROM tbl WHERE geom = <some_value>) -- this will print out the query plan, which access methods, which indexes, which joins, etc. will be used.

    For your question regarding performance, the SELECT queries could run slower because there are more indexes to consider in the query planning phase. In terms of executing a given query plan, a SELECT query will not run slower because by then the query plan has been established and the decision of which index to use has been made.

    You will certainly experience performance impact upon INSERT/UPDATE/DELETE of the table, as all indexes will need to be updated with respect to the changes in the table. As such, there will be extra I/O activity on disk to propagate the changes, slowing down the database, especially at scale.