我有table
CREATE TABLE BulkContainers
( objectId int PRIMARY KEY NOT NULL,
geometryType VARCHAR(10) NOT NULL,
geodata geometry NOT NULL,
timeFrom time,
timeTo time,
cityDistrict VARCHAR(25),
street VARCHAR(60),
trashTypeName VARCHAR(20),
payer VARCHAR(40),
day date);
表包含约60 k条记录,其中地理数据仅为点。
我在地理数据属性上创建了gist索引。
不幸的是,查询速度非常慢。我有一个测试查询集合,我使用ST_Intersects函数,我得到的吞吐量约为20 q/s。
查询示例:
SELECT * FROM BulkContainers WHERE ST_Intersects('POLYGON((14.435818094927747 50.117437918784645,14.435860110788347 50.100516737473654,14.436327451178308 50.105539453354766,14.43664241497306 50.100437942566366,14.437047791355718 50.11031766846078,14.437714063262893 50.105611788231904,14.439779334511888 50.11428054963237,14.438273887651917 50.119529142381225,14.43732995152818 50.11984052052608,14.435818094927747 50.117437918784645))', geodata) IS TRUE;
索引创建为:
CREATE INDEX iindex ON BulkContainers USING gist(geodata);
说明计划如下:
"QUERY PLAN"
"Seq Scan on public.bulkcontainers (cost=0.00..3823.65 rows=20 width=124) (actual time=27.961..27.961 rows=0 loops=1)"
" Output: objectid, geometrytype, geodata, timefrom, timeto, citydistrict, street, trashtypename, payer, day"
" Filter: (((bulkcontainers.geodata && '010300000001000000040000001F85EB51B8DE2C40022B8716D90E4940AC1C5A643BDF2C4077BE9F1A2F0D4940C74B378941E02C40CDCCCCCCCC0C49401F85EB51B8DE2C40022B8716D90E4940'::geometry) AND _st_intersects(bulkcontainers.geodata, '010300000001000000040000001F85EB51B8DE2C40022B8716D90E4940AC1C5A643BDF2C4077BE9F1A2F0D4940C74B378941E02C40CDCCCCCCCC0C49401F85EB51B8DE2C40022B8716D90E4940'::geometry)) IS TRUE)"
" Rows Removed by Filter: 67084"
" Buffers: shared hit=1308"
"Planning time: 2.168 ms"
"Execution time: 28.207 ms"
1条答案
按热度按时间k2arahey1#
IS TRUE会破坏索引的使用。只需编写裸st_intersects函数调用。它的意思是一样的,但写的方式让索引机器发痒。
我有时会得到这个失败使用索引,即使没有是真的,但这是不可复制的。我认为当我有一个bastardized的安装时,PostgreSQL被重新编译并安装在以前的安装之上,但postgis没有重新编译和重新安装。因此存在标头不匹配。