Is it possible to mix btree and gist in a Postgres index?

Question

I have a table built like this:

create table pois (
       id varchar(32) primary key,
       location geography(Point,4326),
       category varchar(32),
       entity_class varchar(1),
       hide boolean
);

on which most queries look like this:

SELECT * from pois
WHERE ( (
    ST_contains(st_buffer(ST_SetSRID(ST_LineFromEncodedPolyline('ifp_Ik_vpAfj~FrehHhtxAhdaDpxbCnoa@~g|Ay_['), 4326)::geography, 5000)::geometry, location::geometry)
    AND ST_Distance(location, ST_LineFromEncodedPolyline('ifp_Ik_vpAfj~FrehHhtxAhdaDpxbCnoa@~g|Ay_[')) < 5000
    AND hide = false
    AND entity_class in ('A', 'B')
) );

currently I have two indexes. one on location "pois_location_idx" gist (location) and one on hide and entity_class: "pois_select_idx" btree (hide, entity_class)

Performance is acceptable but I am wondering if there is a better indexing strategy, and specifically if it is possible and makes sense to have mixed btree + gist indexes.

Answer 1

You can use the operator classes from the btree_gist extension to create a multi-column GiST index:

CREATE EXTENSION btree_gist;

CREATE INDEX ON pois USING gist (location, category);

In your special case I would doubt the usefulness of that, because hide is a boolean and a GiST index cannot support an IN clause.

Perhaps it would be better to create a partial index:

CREATE INDEX ON pois USING gist (location) WHERE NOT hide AND entity_class in ('A', 'B');

Such an index can only be used for queries whose WHERE clause matches that of the index, so it is less universally useful.