2

I need to use btree_gist in order to index three fields: a postgis geometry, a date and bigint.

I have postgresql-contrib installed, and I'm able to create the extension without any problem using: CREATE EXTENSION btree_gist; the extension is visible in the extensions list in pgAdmin, but it is not installed according to the command \dx in psql.

Obviously trying to create an index with btree_gist results in an error stating that btree_gist does not exist.

I'm running pqsl 9.5.4. Is there something that I'm missing?

luckyshonway
  • 101
  • 1
  • 7

1 Answers1

2

According to manuels https://www.postgresql.org/docs/9.5/static/btree-gist.html

btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, inet, and cidr.

PostGIS geometries not included here and also Postgis geometries has own index type GIST

And there is a similar question here Postgres GIST vs Btree index

Barış Serkan AKIN
  • 551
  • 5
  • 14
  • Hi, thanks for your time, btree_gist supports postGIS geometries (even though that case is not in the text you copied from the manual). In the link you provided they address exactly the case of using btree_gist, as a solution. But the problem i'm trying to solve is not to use btree_gist or not, the problem i'm having is that the btree_gist extension is not installing correctly. – luckyshonway Jun 12 '17 at 03:34
  • Did you have any luck with this? I too am experiencing this issue. – Stan Bridge May 11 '20 at 21:42
  • 1
    Looking again at the docs (https://www.postgresql.org/docs/11/btree-gist.html), I believe the usage is just "USING GIST". Also from this doc "_btree_gist **provides GiST index operator classes** that implement B-tree equivalent behavior for the data types_" ie: GIST indexing supports a number of "operator classes". Some you get "out of the box" as per https://www.postgresql.org/docs/11/gist-builtin-opclasses.html But the btree_gist extension gives you _additional_ "operator classes" for GIST. Make sense? – Stan Bridge May 12 '20 at 01:39