Postgres GIST vs Btree index

Question

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??

Please show explain analyze for both; paste the plans on explain.depesz.com and link to them here. — Craig Ringer
– Craig Ringer, Commented Oct 5, 2012 at 3:20

Craig Ringer · Accepted Answer · 2012-10-05 04:25:30Z

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.

But I am able to create multi-column index on (coords, user_id) using btree, so not sure what advantage does a slower GIST index give here?
@user310525 Just because you can create the index doesn't mean it's doing anything useful for you. Examine explain analyze output to see which indexes are used and how. B-tree indexes can only be used for =, <, <=, >, >=, <> and BETWEEN - so it's likely that it's not doing any good at all for the PostGIS part of your query and you'd be better off with a single column b-tree index on (user_id).
@user310525 ST_DWithin is documented to use any available geometry indexes, so it should benefit from the GiST index, but I doubt it can make any use of a b-tree index on the geometry type unless it can do an equality comparision.

Collectives™ on Stack Overflow

Postgres GIST vs Btree index

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related