Postgres GIST与Btree指数

我在Postgres 9.2上有下表(带有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);并且我在表上运行以下查询:select * from updates where ST_DWithin(coords, ST_MakePoint(-126.4, 45.32)::geography, 30000) and user_id='3212312' order by created_at desclimit 60因此，鉴于此，我应该对(coords + user_id)，GIST或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);我读到BTree的性能优于GIST，但是由于我使用的是Postgis地理字段，我是否被迫使用GIST?解决方案如果要使用除常规b树索引(或哈希索引)以外的任何索引方法，则必须使用GiST ). PostGIS索引需要GiST. B树索引只能用于涉及相等或排序的基本操作，例如=，<，<=，>，>=，<>，BETWEEN和.尽管您可以在地理对象(点，区域等)上创建b树索引，但实际上只能将其用于相等性，因为像>这样的排序比较对于此类对象通常是没有意义的.必须使用GiST索引才能支持更复杂和通用的比较，例如包含"，相交"等.您可以使用 btree_gist扩展名为以下内容启用b树索引要旨.它比常规的b树索引慢得多，但是允许您创建一个多列索引，该索引既包含仅GiST的类型，又包含常规类型，如text，integer等.在这种情况下，您确实需要使用explain analyze( explain.depesz.com 对此很有用)来检查Pg如何使用各种索引以及您创建的索引组合.尝试在多列索引中使用不同的列顺序，并查看两个或更多个单独的索引是否更有效.我强烈怀疑在这种情况下，使用多列GiST索引会获得最佳结果，但是我会尝试几种不同的索引和索引列顺序组合来查看.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 desclimit 60So 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);ORCREATE 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?? 解决方案 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. 这篇关于Postgres GIST与Btree指数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！