ประสิทธิภาพของแบบสอบถาม PostGIS Spatial Relationships

Question

ฉันมีชุดข้อมูลขนาดใหญ่มากซึ่งมีจุดมากกว่า 700 ล้านจุดและชุดข้อมูลรูปหลายเหลี่ยมเป็นเขตกันชน

งานของฉันคือการแยกจุดทั้งหมดภายในเขตกันชนและสร้างตารางใหม่

ด้านล่างนี้คือรหัสของฉัน ฉันทดสอบด้วยชุดข้อมูลจุดเล็ก ๆ และใช้งานได้ดี

create table schema1.result as

select point.* from

schema1.site as point, schema2.buffer as poly

Where ST_Intersects(point.geo_loc,poly.wkb_geometry);

น่าเสียดายที่ข้อความค้นหาใช้เวลา 1 วันและไม่มีสัญญาณว่าจะเสร็จสิ้น

มีคำแนะนำในการเพิ่มประสิทธิภาพโค้ดของฉันเพื่อเร่งการสืบค้นหรือไม่?

อัปเดต: ผลลัพธ์ของ Explain

"Nested Loop (ต้นทุน = 0.41..17773703.88 แถว = 6789472 width = 208)"

"-> Seq Scan บนบัฟเฟอร์ poly (ราคา = 0.00..18.50 แถว = 850 width = 32)"

"-> การสแกนดัชนีโดยใช้ idx_site บนไซต์พอยต์ (ต้นทุน = 0.41..20902.23 แถว = 799 width = 208)"

"ดัชนี Cond: (geo_loc && poly.wkb_geometry)"

"ตัวกรอง: st_intersects (geo_loc, poly.wkb_geometry)"

"JIT:"

"ฟังก์ชั่น: 6"

"ตัวเลือก: อินไลน์จริง, การเพิ่มประสิทธิภาพจริง, นิพจน์จริง, เปลี่ยนรูปจริง"

Answer 1

ฉันคิดว่าเกิดอะไรขึ้นที่นี่เมื่อคุณเขียน:

from schema1.site as point, schema2.buffer as poly

PostgreSQL กำลังทำCROSS JOINระหว่างสองตาราง เมื่อหลายตารางมีการระบุไว้ในFROMpostgres ข้อใช้CROSS JOIN แหล่งที่ให้ผลในตารางที่มีจำนวนแถวเท่ากับผลิตภัณฑ์ Cartesian ของสองตารางแหล่งที่มา

เพื่อหลีกเลี่ยงที่คุณสามารถใช้WHERE EXISTSเป็น:

SELECT
    *
FROM schema1.site AS point_table
WHERE EXISTS(
    SELECT 1 
    FROM schema2.buffer AS poly_table
    where ST_Intersects(point_table.geom, poly_table.geom)
);

สิ่งนี้ช่วยให้ PostgreSQL สามารถคำนวณเฉพาะการรวมเชิงพื้นที่และไม่ต้องสร้างตารางชั่วคราวของการเข้าร่วมแบบเต็มซึ่งใช้เวลาตลอดไป

Answer 2

การใช้รูปหลายเหลี่ยมเป็นตารางการขับเคลื่อนอาจเร็วกว่าโดยใช้ดัชนีตารางจุดเพื่อกรองระเบียนจำนวนมากลง สิ่งนี้ช่วยให้ PostGIS สามารถเพิ่มประสิทธิภาพเพรดิเคตST_Intersectsเชิงพื้นที่โดยการเตรียมแต่ละรูปหลายเหลี่ยม

สามารถบังคับได้โดยใช้LATERAL:

SELECT pt.* FROM schema2.buffer AS poly
JOIN LATERAL (SELECT * FROM schema1.site) AS pt 
  ON ST_Intersects(poly.wkb_geometry, pt.geo_loc);

หากรูปหลายเหลี่ยมมีจุดยอดจำนวนมากสามารถใช้ST_Subdivideแยกส่วนได้เร็วกว่าก่อนที่จะทำการสืบค้นจุด:

WITH poly AS (
  SELECT ST_Subdivide(wkb_geometry) AS geom FROM schema2.buffer 
)
SELECT pt.* FROM poly
JOIN LATERAL (SELECT * FROM schema1.site) AS pt 
  ON ST_Intersects(poly.geom, pt.geo_loc);