Postgresql – Postgres GIST vs Btree index

postgispostgresql

Following on from my previous question on this topic, Postgres combining multiple Indexes:

I have the following table on Postgres 9.2 (with postgis):

CREATE TABLE updates (
    update_id character varying(50) NOT NULL,
    coords geography(Point,4326) NOT NULL,
    user_id character varying(50) NOT NULL,
    created_at timestamp without time zone NOT NULL
);

And I am running following query on the table:

select * 
from updates 
where ST_DWithin(coords, ST_MakePoint(-126.4, 45.32)::geography, 30000) 
and user_id='3212312' 
order by created_at desc
limit 60

So given that, what Index should I use for (coords + user_id), GIST or BTree?

CREATE INDEX ix_coords_user_id ON updates USING GIST (coords, user_id);

OR

CREATE INDEX ix_coords_user_id ON updates (coords, user_id);

I was reading that BTree performs better than GIST, but am I forced to use GIST since I am using postgis geography field??

Best Answer

You must use GiST if you want to use any index method other than the regular b-tree indexes (or hash indexes, but they shouldn't really be used). PostGIS indexes require GiST.

B-tree indexes can only be used for basic operations involving equality or ordering, like =, <, <=, >, >=, <>, BETWEEN and IN. While you can create a b-tree index on a geomtery object (point, region, etc) it can only actually be used for equality as ordering comparisons like > are generally meaningless for such objects. A GiST index is required to support more complex and general comparisons like "contains", "intersects", etc.

You can use the btree_gist extension to enable b-tree indexing for GiST. It's considerably slower than regular b-tree indexes, but allows you to create a multi-column index that contains both GiST-only types and regular types like text, integer, etc.

In these situations you really need to use explain analyze (explain.depesz.com is useful for this) to examine how Pg uses various indexes and combinations of indexes that you create. Try different column orderings in multi-column indexes, and see whether two or more separate indexes are more effective.

I strongly suspect that you'll get the best results with the multicolumn GiST index in this case, but I'd try several different combinations of indexes and index column orderings to see.

Related Topic