Multicolumn index on 3 fields with heterogenous data types

by

Single-column index

Postgres can combine multiple indexes very efficiently in a single query with bitmap index scans. Most of the time, the most selective index is picked (or two, combined with bitmap index scans) and the rest is filtered. Once the result set is narrow enough, it’s not efficient to scan more indexes.

Multicolumn index

It is still faster to have a perfectly matching multicolumn index, but not by orders of magnitude.
Since you want to include an array type I suggest to use a GIN index. AFAIK, operator classes are missing for general-purpose GiST indexes on array type. (The exception being intarray for integer arrays.)

To include the integer column, first install the additional module btree_gin, which provides the necessary GIN operator classes. Run once per database:

CREATE EXTENSION btree_gin;

Then you should be able to create your multicolumn index:

CREATE INDEX tbl_abc_gin_idx ON tbl USING GIN(a, b, c);

The order of index columns is irrelevant for GIN indexes. The manual:

A multicolumn GIN index can be used with query conditions that involve
any subset of the index’s columns. Unlike B-tree or GiST, index search
effectiveness is the same regardless of which index column(s) the
query conditions use.

Nearest neighbour search

Since you are including a PostGis geometry type, chances are you want to do a nearest neighbour search, for which you need a GiST index. In this case I suggest two indexes:

CREATE INDEX tbl_ac_gist_idx ON tbl USING GiST(a, c);  -- geometry type
CREATE INDEX tbl_bc_gin_idx  ON tbl USING GIN(b, c);

You could add the integer column c to either or both. It depends.
For that, you need either btree_gin or btree_gist or both, respectively.

More Related Contents:

  1. Postgres query optimization (forcing an index scan)
  2. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  3. Optimize Postgres query on timestamp range
  4. Optimize GROUP BY query to retrieve latest row per user
  5. PostgreSQL LIKE query performance variations
  6. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  7. Why does PostgreSQL perform sequential scan on indexed column?
  8. Do nullable columns occupy additional space in PostgreSQL?
  9. Running PostgreSQL in memory only
  10. Buffers (Circle) in PostGIS
  11. Does a Postgres UNIQUE constraint imply an index?
  12. Difference between GiST and GIN index
  13. Postgis installation: type “geometry” does not exist
  14. Configuration parameter work_mem in PostgreSQL on Linux
  15. How to add a conditional unique index on PostgreSQL
  16. PostgreSQL composite primary key
  17. How to import CSV file data into a PostgreSQL table
  18. Any downsides of using data type “text” for storing strings?
  19. The forgotten assignment operator “=” and the commonplace “:=”
  20. Equivalent to unpivot() in PostgreSQL
  21. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  22. I forgot the password I entered during postgres installation
  23. Way to try multiple SELECTs till a result is available?
  24. List columns with indexes in PostgreSQL
  25. How to configure PostgreSQL to accept all incoming connections
  26. Disable PostgreSQL foreign key checks for migrations
  27. Using row_to_json() with nested joins
  28. Use variable set by psql meta-command inside of DO block
  29. pgAdmin III Why query results are shortened?
  30. SELECT DISTINCT is slower than expected on my table in PostgreSQL

Leave a Comment