postgresql ST_Intersects太慢

5lhxktic  于 2023-04-29  发布在  PostgreSQL
关注(0)|答案(1)|浏览(225)

我有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"
k2arahey

k2arahey1#

IS TRUE会破坏索引的使用。只需编写裸st_intersects函数调用。它的意思是一样的,但写的方式让索引机器发痒。
我有时会得到这个失败使用索引,即使没有是真的,但这是不可复制的。我认为当我有一个bastardized的安装时,PostgreSQL被重新编译并安装在以前的安装之上,但postgis没有重新编译和重新安装。因此存在标头不匹配。

相关问题