PostGIS-i jõudluse show: geomeetria vs geograafia

Coordis kasutame oma peamise andmesalvestusena PostgreSQL-i ja kasutame ära suurepärast PostGIS-i laiendit, et salvestada georuumilisi andmeid, näiteks äärekivi geomeetriaid. PostGIS võimaldab teil valida kahe erineva viisi georuumiliste andmete salvestamiseks:

  • Geomeetria, kus ta eeldab, et kõik teie andmed elavad Descartes'i lennukil (nagu kaardi projektsioon);
  • Geograafia, kus eeldatakse, et teie andmed koosnevad maapinna punktidest, mida täpsustavad laius- ja pikkuskraadid.

Geograafiatüübid annavad täpsemaid tulemusi, eriti pikkade vahemaade korral, kuid geomeetriatüüpidel on parem jõudlus. Kuid kui suured erinevused on päriselus? Ja kumba peaksite kasutama? Viisin hiljuti Coordis läbi mõned katsed, et sellele küsimusele meie jaoks vastata. Lisateabe saamiseks lugege edasi!

Raadiuse päringute tegemine

Isegi enam kui täpsus, lihtsus on põhjus, miks ma kõige enam tahan kasutada meie andmebaasis geograafiatüüpe. Vaadakem näiteks otsitud raadiuses toimingut - toimingut, mida Coord kasutab meie mobiilsuse API-liidestes mitu korda.

Kuna see raadius on täpsustatud kilomeetrites ja mitte suvalistes projektsioonikaugustes, peame selle päringu geomeetriatüübi teostamiseks kõigepealt teadma, kuidas teisendada vahemaa meetrites projektsioonikauguseks. Päring on midagi sellist:

WITH (
  c AS (
    SELECT ST_Transform (ST_Point (lat, lng) :: geograafia AS-i kese
  ),
  d AS (
    VALI ST_Distants (
      ST_Transform (kese :: geomeetria, minu_projektsioon),
      ST_Transform (ST_Translate (keskel, 1) :: geomeetria, minu_projektsioon)
    ) AS-i arvesti vahemaa
    C
  )
VALI ID FROM my_table, c, d
KUS ST_DWithin (
  my_table.geometry,
  Keskus,
  meeter_distants * raadius_in_meetrid);

See päring ei muuda mitte ainult nii täpseid suuremaid raadius_in_meetrite sirvimisi, vaid on ka lihtne viga saada ja seda on raske lugeda.

Teisest küljest, kui minu tabel kasutab geograafiat, on päring sama lihtne:

VALI ID FROM my_table WHERE ST_DWithin (
  Minu_tabel.geograafia,
  ST_Point (lat, lng) :: geograafia,
  raadius_in_meetrid);

Kui võimalik, eelistaksin tõesti kasutada teist päringut. Kuid kui palju etendust me loovutaksime?

Jooksukontrolli näitajad

Et otsustada, millised on kompromissid geomeetria ja geograafia vahel, soovisin käitada võrdlusalust ja välja mõelda, kui palju maksaks iga variant ja andmebaasi kasutamine reaalainete koormuste korral. Lihtsaim viis selleks on lihtsalt kasutada psql \ timing'iga. Kuid see annab teile mitmel põhjusel ebausaldusväärseid tulemusi. Peamine muretsemine on külmkäivitus. Vahemälu salvestamise tõttu nii DB kui ka OS-i tasemel saate tabeli esmakordsel pärimisel väga erinevaid tulemusi kui järgnevatel kordadel. Uue ühenduse kasutamisel näeme ka mõningaid külmkäivituse probleeme. See tähendab, et kehtivate tulemuste saamiseks on oluline käitada võrdlusuuringuid mitu korda. Me kasutame aja kokkuvõtte jälgimiseks skripti plpgsql ja kirjutame tulemused CSV-vormingus välja.

Tegeliku koormuse toimivuse võrdlemiseks võtsin meie San Francisco äärekivi geomeetriate kaardi ja koostasin kaks tabelit, mis olid ühe erandiga identsed: üks neist kasutas geomeetriat (kasutades EPSG 3857 Web Mercatori projektsiooni) ja üks neist kasutatud geograafia. Seejärel korraldasime kõigil nendel tabelitel 5000 päringut, nagu ülaltoodud, kasutades erinevaid raadiusi (käitasime ka kolmandat päringute komplekti, mis kasutasid geograafiat, kuid kus use_spheroid oli seatud valele, mis muudab kauguse arvutamise kiiremaks. Lõin mõlemale tabelile georuumilise indeksi. , kuna soovisin toimivuse hindamise käigus testida mõlemat tüüpi indekseerimise käitumist.

Tulemused

Leidsin järgmist:

Pole üllatav, et geomeetria (sinised punktid) on kiirem kui geograafia (punased ja oranžid punktid). Samuti saime teada, et kui teil on piisavalt ridu, skaleeritakse nii geomeetria kui ka geograafiliste päringute toimivus lineaarselt tagastatud ridade arvuga. See viitab sellele, et meie päringu indeksi otsimise samm on mõlemat tüüpi efektiivne: kui peaksime tegema tabeli skannimise või kui indeks ei töötaks hästi, näeksime suurt fikseeritud ajakaristust, sõltumata read, mille lõpuks naasime. Aeg, mis lõpuks aega võtab, näib selle asemel olevat kauguse arvutus ridadel, kuhu me lõpuks tagasi jõuame.

Lisateavet saame, kui vaadata päringu aja suhet geograafia ja geomeetria vahel.

Jõudluse karistusaste on keskmine aeg geograafilise päringu kohta jagatud keskmise ajaga geomeetrilise päringu kohta.

See näitab, et väga väheste ridade tagastatavate päringute korral võtab geograafia vähem kui 2x geomeetria aega, kuid kuna tagastatud ridade arv suureneb üle 750, tasandab jõudluse karistus 4x. See viitab sellele, et nagu arvata võis, on fikseeritud otsingukulud väiksemate päringute puhul olulisemad kui suurte.

Mida see teie jaoks tähendab

Mida peaksite sellest tegema? Üks oluline punkt on see, et mida vähem päringu kohta ridu skannite, seda väiksemat trahvi te geograafia eest maksate. Kui teie päringukoormus domineerib indeksotsingutes, võib geograafiline karistus olla väga lähedal nullile. Kuid kui teie päringud hõlmavad arvutusi keerukate geograafiliste piirkondade või suure hulga geograafiliste objektide kohta, on teie karistus suurem.

Lisaks pole tõenäoliselt kogu teie töökoormus georuumiline! Neljakordne trahv võib kõlada palju, kuid kui georuumiline arvutus moodustab ainult 10% teie tehtud tööst iga rea ​​alguses, on trahv, mida maksate murdosaga kogu arvutusajast, palju väiksem.

Niisiis, kui ma alustaksin uut projekti nullist, kasutaksin ma geograafiat sõltumata jõudluskaristusest. Miks? Pidage meeles, et te ei tohiks kunagi enneaegselt optimeerida: kui teie päringud pole juba aeglased või kui teie andmebaasi laadimine muutub problemaatiliseks, pole tõenäoliselt mõistlik kiirendada. Ja kui jõudlus pole juba probleem, on geograafiat palju lihtsam hallata ja palju keerulisem.

Kui olete oma andmed geograafiaga töötanud, on teil vähemalt olemas lähteseis: kui otsustate oma andmed geomeetriaks teisendada, saate tulemusi vähemalt võrrelda, et tagada teie tehtud ebatäpsuste vastuvõetavus. See võimaldab teil ka jõudlust võrrelda, et mõista, kui palju aega tegelikult säästate.

Teisest küljest peame kinni geomeetriast. Curbs API kõned võivad hõlpsalt tagastada tuhandeid äärekivid ja nende päringute georuumilised kulud on tõeliselt märkimisväärsed. Oleme juba rakendanud muid jõudluse optimeerimisi, et vältida paljude mittegeograafiliste äärekivide andmete andmebaasi löömist ja kiirema geomeetria andmetüübi kasutamisest saadavad eelised on märkimisväärsed.

Kas kasutate PostGIS-i? Kas teil on olnud sarnaseid esinemisküsimusi? Mul oleks hea meel teid kuulda e-posti aadressil jacob@coord.co. Head kaardistamist!