ST_D ภายในช้าอย่างทวีคูณ ไม่พบสิ่งที่ฉันทำผิด

Aug 20 2020
  • เวอร์ชัน 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

คำถาม:

เป็นเรื่องปกติหรือไม่? ฉันจะลดเวลาดำเนินการได้อย่างไร

คำตอบ

3 robinloche Aug 21 2020 at 09:46

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;