ST_D ภายในช้าอย่างทวีคูณ ไม่พบสิ่งที่ฉันทำผิด
- เวอร์ชัน PostGIS: 3.1.2
- เวอร์ชัน PostgreSQL: 12.3.2
- เครื่องที่ฉันใช้งานมี: แรม 126G, 48 คอร์ CPU
ข้อมูล:
ฉันกำลังเริ่มต้นกับ PostGIS
เป้าหมายของฉันคือรับข้อมูลที่ตรงกันทั้งหมดระหว่างสองจุด
lv.geopoint และ sub.geopoint ทั้งคู่คือ GEOGRAPHY Points (SRID: 4326) และมีดัชนี GIST อยู่ด้วย
รายการย่อยของฉันส่งคืนประมาณ 3k บรรทัดตาราง 'valeurs_foncieres' ของฉันมี 14,000,000 บรรทัด
ฉันมีดัชนี BTREE บน valeurs_foncieres.id, caracteristiques_2018.id, caracteristiques_2018.num_acc, usagers_2018.id, usagers_2018.num_acc, Vehicleules_2018.id, Vehicleules_2018.num_acc
ปัญหา:
แบบสอบถามได้รับช้าอย่างทวีคูณเมื่อฉันเพิ่มระยะทางของ ST_DWithin
- ความแม่นยำ 100: 2 วินาที
- ความแม่นยำ 1,000: 10 วินาที
- แม่นยำ 10,000: 6 นาที
นี่คือคำถาม:
SELECT
DISTINCT(sub.num_acc),
sub.geopoint,
sub.id
FROM
(
SELECT
DISTINCT(u.num_acc) AS unumacc, c.*
FROM
usagers_2018 u
INNER JOIN vehicules_2018 v ON
u.num_acc = v.num_acc
INNER JOIN caracteristiques_2018 c ON
u.num_acc = c.num_acc
WHERE
u.grav = '2'
ORDER BY
c.id
) AS sub
INNER JOIN valeurs_foncieres vf ON
ST_DWithin(vf.geopoint,
sub.geog,
1000,
FALSE);
นี่คือคำอธิบาย:
HashAggregate (cost=265577998.10..265578004.81 rows=671 width=49)
Group Key: c.num_acc, c.geopoint, c.id
-> Nested Loop (cost=9948.38..264845621.97 rows=97650150 width=49)
-> Unique (cost=9947.84..10316.67 rows=6706 width=170)
-> Sort (cost=9947.84..9964.60 rows=6706 width=170)
Sort Key: c.id, u.num_acc, c.an, c.mois, c.jour, c.hrmn, c.lum, c.agg, c."int", c.atm, c.col, c.com, c.adr, c.gps, c.lat, c.long, c.dep, c.lat_gps, c.long_gps, c.geopoint, c.geog
-> Gather (cost=3200.48..9521.63 rows=6706 width=170)
Workers Planned: 1
-> Nested Loop (cost=2200.48..7851.03 rows=3945 width=170)
Join Filter: ((u.num_acc)::text = (v.num_acc)::text)
-> Parallel Hash Join (cost=2200.06..6686.70 rows=2075 width=170)
Hash Cond: ((c.num_acc)::text = (u.num_acc)::text)
-> Parallel Seq Scan on caracteristiques_2018 c (cost=0.00..2859.90 rows=33990 width=157)
-> Parallel Hash (cost=2174.12..2174.12 rows=2075 width=13)
-> Parallel Seq Scan on usagers_2018 u (cost=0.00..2174.12 rows=2075 width=13)
Filter: ((grav)::text = '2'::text)
-> Index Only Scan using vehicules_2018_num_acc_idx on vehicules_2018 v (cost=0.42..0.54 rows=2 width=13)
Index Cond: (num_acc = (c.num_acc)::text)
-> Index Scan using valeurs_foncieres_geopoint_idx on valeurs_foncieres vf (cost=0.54..39477.72 rows=1456 width=32)
Index Cond: (geopoint && _st_expand(c.geog, '1000'::double precision))
Filter: st_dwithin(geopoint, c.geog, '1000'::double precision, false)
JIT:
Functions: 30
Options: Inlining true, Optimization true, Expressions true, Deforming true
คำถาม:
เป็นเรื่องปกติหรือไม่? ฉันจะลดเวลาดำเนินการได้อย่างไร
คำตอบ
14,000 000 เส้นไม่เล็ก นอกจากนี้หาก geog ที่คุณมีกระจายอย่างสม่ำเสมอจำนวนจุดที่เกี่ยวข้องจะอยู่ที่ประมาณ x100 เมื่อคุณคูณรัศมี x10 (พื้นที่ของวงกลมขึ้นอยู่กับr²) ดังนั้นจึงเป็นเรื่องปกติที่การเพิ่มเวลาของคุณจะดูเหมือนกำลังสอง ที่นี่ดูเหมือนว่าจะมีมากกว่านั้น แต่ยิ่งคุณจัดการข้อมูลมากเท่าไหร่คุณก็จะยิ่งต้องการการดำเนินการมากขึ้นเท่านั้นเนื่องจากการตั้งค่าแคชและการเรียกใช้ดิสก์ทั้งหมด (ไม่จริงสำหรับข้อมูลขนาดเล็กหรือแคชขนาดใหญ่)
ที่นี่คำอธิบายดูเหมือนโอเคมันใช้ดัชนีดังนั้นจึงไม่ใช่ปัญหา คุณควรตรวจสอบให้แน่ใจว่าได้วิเคราะห์ตารางของคุณแล้ว แต่ก็ไม่ควรเปลี่ยนแปลงมากนัก
สิ่งสำคัญที่คุณสามารถทำได้หากคุณไม่ได้ปรับแต่ง postgresql ของคุณ โดยค่าเริ่มต้นพารามิเตอร์เป็นแบบอนุรักษ์นิยมหากคุณมีเซิร์ฟเวอร์ขนาดใหญ่คุณต้องแก้ไขพารามิเตอร์เพื่อให้ใช้งานได้อย่างถูกต้อง พารามิเตอร์เหล่านี้สามารถจัดการได้ในไฟล์นี้บน linux: /etc/postgresql/12/main/postgresql.conf จากนั้นคุณต้องรีสตาร์ท postgres (คุณสามารถค้นหาเอกสารบนอินเทอร์เน็ตได้อย่างง่ายดายหากคุณมีคำถามเกี่ยวกับเรื่องนั้น) โดยทั่วไปสิ่งที่ฉันแก้ไขมีดังต่อไปนี้ (ปรับให้เหมาะกับ 120Go และ 48 CPU ของ ram):
- shared_buffers = 30GB
- Effective_cache_size = 80GB
- work_mem = 256MB
- maintenance_work_mem = 5GB
- autovacuum_work_mem = 5GB
- Effective_io_concurrency = 200 (สำหรับ SSD หรือ 2 สำหรับดิสก์)
- max_worker_processes = 48
- max_parallel_workers = 48
- max_parallel_workers_per_gather = 12
- wal_buffers = 16MB
- min_wal_size = 1GB
- max_wal_size = 2GB
สิ่งเหล่านี้อาจไม่สมบูรณ์แบบและส่วนหนึ่งเป็นเพราะเอกสารที่ฉันพบและส่วนหนึ่งมาจากการลองและล้มเหลวตามคำขอครั้งใหญ่ แต่ถ้าคุณไม่ได้กำหนดค่า postgresql ของคุณเลย (คุณบอกว่าคุณเริ่ม) มันควรจะสร้างความแตกต่างอย่างมากในการทำงานสำหรับคำขอใหญ่ ๆ (ของคุณไม่ได้ใหญ่ขนาดนั้น แต่มันควรจะมีผลกระทบ) ข้อมูลเรขาคณิตมักมีขนาดใหญ่ดังนั้นจึงควรมีพื้นที่มากกว่าการใช้ postgresql ทั่วไป นอกจากนี้หากทำได้อย่าลืมใส่ข้อมูลลงใน SSD ก็อาจส่งผลกระทบอย่างมากเช่นกัน
แก้ไข
ฉันเพิ่งอ่านคำขอของคุณอีกครั้งและฉันไม่เข้าใจจริงๆว่าทำไมคุณถึงต้องการคะแนนทั้งหมดในระยะ X เมตรถ้าหลังจากที่คุณเก็บเพียงบรรทัดเดียวด้วยตัวเลข ไม่ว่าคุณจะไม่ได้ใส่ข้อความค้นหาทั้งหมดหรือคุณต้องการเพียงจุดเดียว ฉันจึงเขียนมันใหม่เผื่อว่าสิ่งที่คุณต้องการจริงๆคือเพื่อให้ได้จุดที่ใกล้ที่สุด ฉันใช้ MATERIALIZED CTE ซึ่งสร้างตารางชั่วคราวสำหรับแต่ละขั้นตอนบางครั้งก็สามารถปรับปรุงประสิทธิภาพได้จริงๆดังนั้นในกรณีที่คุณต้องการได้รับคะแนนทั้งหมดไม่ใช่แค่เพื่อนบ้านที่ใกล้เคียงที่สุดคุณสามารถลองเรียกใช้งานได้เช่นเดียวกับการลบ ORDER BY และ LIMIT ใน INNER JOIN LATERAL ในตอนท้าย และแน่นอนที่นี่ฉัน จำกัด การค้นหาด้วย ST_DWithin แต่ถ้าคุณต้องการเพื่อนบ้านที่ใกล้ที่สุดคุณสามารถลบสิ่งนี้ออกได้ WHERE:
WITH usg AS MATERIALIZED
(
SELECT
DISTINCT(u.num_acc) AS unumacc
, c.*
FROM
usagers_2018 u
WHERE
u.grav = '2'
INNER JOIN caracteristiques_2018 c ON
u.num_acc = c.num_acc
ORDER BY
c.id
), sub AS MATERIALIZED
(
SELECT
DISTINCT(usg.unumacc)
, usg.*
, v.*
FROM
usg
INNER JOIN vehicules_2018 v ON
usg.num_acc = v.num_acc
)
SELECT
sub.*
, vf.*
FROM sub
INNER JOIN LATERAL
(
SELECT
vf.*
FROM
valeurs_foncieres vf
WHERE
ST_DWithin(
vf.geopoint
,sub.geog
, 1000
,FALSE
)
ORDER BY vf.geopoint <-> sub.geog
LIMIT 1
)
ON TRUE;