ST_DDalam waktu yang sangat lambat. Tidak dapat menemukan kesalahan yang saya lakukan

Aug 20 2020
  • Versi PostGIS: 3.1.2
  • Versi PostgreSQL: 12.3
  • Mesin yang saya gunakan memiliki: RAM 126G, 48 inti CPU

Info:

Saya memulai dengan PostGIS.

Tujuan saya adalah mendapatkan semua data yang cocok antara dua titik.

lv.geopoint dan sub.geopoint keduanya adalah GEOGRAPHY Points (SRID: 4326) dan memiliki indeks GIST di atasnya.

Sub SELECT saya mengembalikan sekitar 3k baris, namun tabel 'valeurs_foncieres' saya memiliki 14.000.000 baris.

Saya memiliki indeks BTREE di valeurs_foncieres.id, caracteristiques_2018.id, caracteristiques_2018.num_acc, usagers_2018.id, usagers_2018.num_acc, vehicleicules_2018.id, veicules_2018.num_acc.

Masalah:

Kueri menjadi sangat lambat saat saya meningkatkan jarak ST_DWithin.

  • Presisi 100: 2sec
  • Presisi 1000: 10sec
  • Presisi 10.000: 6 menit

Berikut pertanyaannya:

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);

Berikut PENJELASAN:

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

Pertanyaan:

Apakah ini normal? Bagaimana cara mengurangi waktu eksekusi?

Jawaban

3 robinloche Aug 21 2020 at 09:46

14.000.000 baris tidaklah kecil. Selain itu, Jika geog yang Anda miliki terdistribusi seragam, jumlah titik yang bersangkutan adalah sekitar x100 saat Anda mengalikan jari-jari x10 (luas lingkaran bergantung pada r²), jadi wajar jika pembesaran waktu Anda tampak kuadrat. Di sini tampaknya lebih dari itu, tetapi semakin banyak data yang Anda manipulasi, semakin banyak operasi yang berpotensi Anda perlukan karena semua gerakan cache dan panggilan disk (tidak berlaku untuk data kecil atau cache besar).

Disini penjelasannya nampaknya ok, menggunakan index jadi tidak masalah. Anda hanya harus memastikan untuk MENGANALISIS VAKUM tabel Anda tetapi tidak akan banyak berubah.

Hal utama yang dapat Anda lakukan jika tidak melakukannya adalah mengubah postgresql Anda. Secara default, parameternya sangat konservatif, jika Anda memiliki server yang besar, Anda perlu mengubah parameter untuk menggunakannya dengan benar. Parameter ini dapat ditangani dalam file ini di linux: /etc/postgresql/12/main/postgresql.conf maka Anda perlu memulai ulang postgres (Anda dapat dengan mudah menemukan dokumen di internet jika Anda memiliki pertanyaan tentang itu). Biasanya, yang saya modifikasi adalah sebagai berikut (diadaptasi untuk sekitar 120Go dan 48 CPU ram):

  • shared_buffers = 30 GB
  • effective_cache_size = 80 GB
  • work_mem = 256MB
  • maintenance_work_mem = 5 GB
  • autovacuum_work_mem = 5GB
  • effective_io_concurrency = 200 (untuk SSD, atau 2 untuk disk)
  • max_worker_processes = 48
  • max_parallel_workers = 48
  • max_parallel_workers_per_gather = 12
  • wal_buffers = 16MB
  • ukuran_wal_minimal = 1 GB
  • max_wal_size = 2 GB

Itu mungkin tidak sempurna, dan didefinisikan sebagian karena dokumentasi yang saya temukan dan sebagian dari percobaan dan kegagalan atas permintaan besar. Tetapi jika Anda tidak mengkonfigurasi postgresql Anda sama sekali (Anda mengatakan bahwa Anda memulai) itu akan membuat perbedaan besar dalam kinerja untuk permintaan besar (milik Anda tidak terlalu besar, tetapi harus berdampak). Data geometri biasanya besar, sehingga membutuhkan lebih banyak ruang daripada penggunaan postgresql biasa. Selain itu, jika Anda bisa, pastikan untuk meletakkan data Anda di SSD, itu bisa berdampak besar juga.

EDIT

Saya baru saja membaca ulang permintaan Anda, dan saya tidak begitu mengerti mengapa Anda membutuhkan semua poin dalam X meter jika setelah Anda hanya menyimpan satu baris demi nomor. Entah Anda tidak memasukkan seluruh kueri, atau Anda benar-benar hanya membutuhkan satu poin. Jadi saya hanya menulis ulang seandainya yang Anda inginkan adalah mendapatkan poin terdekat. Saya menggunakan MATERIALIZED CTE, yang membuat tabel sementara untuk setiap langkah, terkadang itu benar-benar dapat meningkatkan kinerja, jadi jika Anda ingin mendapatkan semua poin dan bukan hanya tetangga terdekat, Anda dapat mencoba menjalankannya sebagaimana adanya dengan menghapus ORDER OLEH dan BATAS di INNER JOIN LATERAL di akhir. Dan tentu saja di sini saya membatasi pencarian dengan ST_DWithin tetapi jika Anda ingin tetangga terdekat sejati Anda dapat menghapus ini DI MANA:

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;