ST_DDalam waktu yang sangat lambat. Tidak dapat menemukan kesalahan yang saya lakukan
- 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
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;