9 Harjoitus 8: Indeksit - tavalliset ja spatiaaliset
Harjoituksen sisältö Kyselyjen suorittamisen tutkinta, Query Planner, sekä indeksien luonti
Harjoituksen tavoite - Opiskelija tuntee indeksien luomisen ja käyttötavat
9.0.1 Valmistautuminen
Avaa pgAdmin selaimeen ja kirjaudu sisään. Avaa Query Tool (Valitse trainingdatabase -> Ylhäältä Tools -> Query Tool).
9.1 Harjoitus 8.1: PostgreSQL:n ei-spatiaaliset indeksit
Käydään läpi ensin hieman taustaa yleisesti kyselyjen kestoon ja optimoimiseen liittyen.
9.1.1 PostgreSQL:n Query Planner sekä EXPLAIN ANALYZE -komento
Kun SQL-kysely ajetaan, kysely menee ensimmäiseksi nk. Plannerille, suunnittelijalle, joka määrittää mikä on optimaalisin tapa toteuttaa annetun SQL-syntaksin mukainen kysely tietokannassa (esimerkiksi milloin tulisi käyttää määriteltyjä indeksejä ja milloin hakea vain taulusta rivi kerrallaan), ja muokkaa kyselyä sen mukaan.
Olet ehkä huomannutkin että kun ajat PgAdminissa kyselyn ruudussa käväisee vihreä palkki, joka kertoo kuinka monta tietuetta kysely palautti mutta myös sen, kuinka kauan kyselyyn kului aikaa millisekunteina. Vaikka tästä arvosta saakin osviittaa kyselyn kestosta, se sisältää myös esimerkiksi verkkoliikenteeseen kuluvan ajan ja ei siten ole kovin sopiva itse kyselyjen optimointia tarkasteltaessa.
Tähän tarkoitukseen on olemassa komennot EXPLAIN sekä EXPLAIN ANALYZE. Näitä käytetään yksinkertaisesti kirjoittamalla kyseinen avainsana ennen varsinaista kyselyä. Nämä kaksi eroavat toisistaan siinä, että EXPLAIN-komennolla saa näkyviin Plannerin muodostaman suunnitelman kyselyn toteutuksesta, kun taas EXPLAIN ANALYZE suorittaa kyselyn ja palauttaa lisäksi tietoa itse suorituksen kestosta. Kestosta annetaan sekä suunnitelman tuottamiseen kulunut aika että (käytettäessä EXPLAIN ANALYZEä) sen suorittamiseen kuluva aika millisekunteina, sekä kyselyn eri osien suhteellisia kustannuksia (Cost).
On myös huomattava, että kumpikaan ei kuitenkaan palauta itse kyselyn tuottamia rivejä vaan ainoastaan tiedon siitä, millä tavoin kyselyä tultaisiin ajamaan.
Tarkastellaan tätä muutamien yksinkertaisten liikennevaylat-tauluun suoritettujen esimerkkikyselyiden avulla. Varmistetaan ensin mitä indeksejä kyseisessä taulussa on valmiina:
Kuva: liikennevaylat-taulun indeksit.
Nähdään, että taulussa on on indeksi sidx_liikennevaylat_geom. Kun tarkastellaan sitä lähemmin (properties), huomataan että se on GiST-tyyppinen indeksi geom-kentälle. Tämä on siis se spatiaalinen indeksi, joka luotiin tuotaessa data tietokantaan QGIS-työkalun avulla. Ensimmäiset esimerkkikyselyt eivät kuitenkaan kohdistu geometria-kenttään, joten tällä ei ole nyt vaikutusta ja se voidaan jättää paikoilleen.
Kuva: Esimerkki EXPLAIN ANALYZEN antamasta tulostuksesta.
- Aja siis seuraavia kyselyjä ja kiinnitä huomiota/tallenna tulokset esimerkiksi notepadiin myöhempää vertailua ja tutkimista varten (voit koittaa ajaa samaa kyselyn muutaman kerran saadaksesi jonkinlaisen keskiarvon).
EXPLAIN ANALYZE SELECT * FROM liikennevaylat;
EXPLAIN ANALYZE SELECT * FROM liikennevaylat WHERE pituus BETWEEN 50 AND 55;
EXPLAIN ANALYZE SELECT * FROM liikennevaylat WHERE pituus < 13;Luodaan sitten BTREE-indeksi kentälle “pituus”. Lyhimmillään tämä käy komennolla
CREATE INDEX IF NOT EXISTS ON liikennevaylat (pituus);Jos halutaan varmistua indeksin nimestä ja tyypistä, kirjoitetaan eksplisiittisesti
CREATE INDEX liikennevaylat_pituus_idx ON liikennevaylat USING BTREE (pituus);Samaten indeksin poistaminen on helppoa (älä kuitenkaan poista vielä tässä):
DROP INDEX liikennevaylat_pituus_idx;- Aja edellä annettuja kyselyjä nyt indeksin määrittelyn jälkeen ja vertaa saatuja tuloksia aiempaan. Miten query plan on muuttunut? Onko suunnittelu- ja ajoaika muuttunut jollain tavalla?
Huom! Muista että kysely suoritetaan ANALYZE-avainsanaa käytettäessä, ja vaikka tuloksena ei palautetakaan rivejä, voi muita kuin SELECT-kyselyjä suoritettaessa aiheutua epätoivottuja sivuvaikutuksia kyselyn ajamisesta. Jos pitää analysoida esimerkiksi INSERT-/UPDATE-/DELETE-/MERGE/CREATE TABLE AS -kyselyjä, on turvallisempaa tehdä nämä transaktiossa seuraavalla tavalla:
BEGIN;
EXPLAIN ANALYZE …;
ROLLBACK;
- Katso tallennetun indeksin koko, esimerkiksi
SELECT pg_size_pretty(pg_relation_size(‘sidx_liikennevaylat_geom’));tai vaihtamalla argumentiksi jonkin muun luodun indeksin nimen. Voit lisäksi verrata kokoluokkaa koko taulun kokoon kyselemällä kokoa relaatiolle ‘liikennevaylat’. Kannattaako mahdollisimman monen kentän indeksointi?
9.2 Harjoitus 8.2: PostgreSQL:n spatiaaliset indeksit
Testataan nyt indeksien vaikutusta kyselyihin, joissa on mukana geometrioita ja spatiaalisia liitoksia.
EXPLAIN ANALYZE
SELECT *
FROM liikennevaylat a
JOIN kaupunginosajako b
ON ST_Crosses(a.geom, b.geom);Miltä kyselyn osat näyttävät nyt EXPLAIN ANALYZEN mukaan? Ota jälleen tulostus talteen vertailun vuoksi. Poista nyt jo luodut spatiaaliset indeksit, ensin ‘sidx_kaupunginosajako_geom’. Tämän voi tehdä joko pgAdminin kautta
Kuva: indeksin poistaminen taululta
tai suoraan Query Toolissa SQL:ää käyttäen
DROP INDEX <indeksin nimi>;Aja EXPLAIN ANALYZE -kysely uudelleen. Tuleeko muutoksia? Poista vielä spatiaalinen indeksi liikennevaylat-taululta, ja vertaa tuloksia kyselyssä.
Luodaan lopuksi vielä spatiaaliset indeksit uudelleen kyseisille tauluille.
CREATE INDEX IF NOT EXISTS
sidx_kaupunginosajako_geom
ON kaupunginosajako
USING GIST (geom);ja
CREATE INDEX IF NOT EXISTS
sidx_liikennevaylat_geom
ON liikennevaylat
USING GIST (geom);9.3 Harjoitus 8.3: Taulutilat
Tarkista koulutuspalvelimen oletustaulutilojen sijainti. Käyttäjien tiedot sijaitsevat pg_default-taulutilassa, joka on data_directoryn base-hakemistossa. Järjestelmän yleiset tiedot sijaitsevat pg_global-taulutilassa, joka on data_directoryn global-hakemistossa. Datahakemiston sijainnin voit tarkistaa komennolla:
SHOW data_directory;9.4 Harjoitus 8.4: Uuden taulutilan luominen
Uutta taulutilaa varten pitää luoda ensin palvelimelle kansio. Kansion tulee olla postgres-käyttäjän omistuksessa ja käyttöoikeudet vain postgres-käyttäjälle. Hakemisto on luotu valmiiksi palvelimelle komennoilla:
sudo mkdir /usr/local/tmp_tbls
sudo chown -R postgres:postgres /usr/local/tmp_tbls/
sudo chmod 700 /usr/local/tmp_tbls/Luo uusi taulutila SQL-komennolla psql:n tai pgAdminin avulla:
CREATE TABLESPACE tmp_tablespace
LOCATION '/usr/local/tmp_tbls';9.5 Harjoitus 8.5: Tietokannan ja taulun taulutilan muuttaminen
Koko tietokannan taulutilan voi muuttaa yhdellä komennolla.
HUOM! Tätä ei kuitenkaan voi tehdä jos tietokantaan on auki aktiivisia yhteyksiä!
- Sulje pgAdminin tietokantayhteys harjoitustietokantaasi klikkaamalla tietokantaa sivupaneelissa hiiren oikealla painikkeella ja valitsemalla Disconnect Database.
- Avaa tämän jälkeen sivupaneelista postgres-tietokanta ja avaa Query tool (voit tehdä tämän myös psql:n avulla). Anna komento:
ALTER DATABASE trainingdatabase
SET TABLESPACE tmp_tablespace;Mikäli tämä ei onnistu, ja pgAdmin4 kertoo, että tietokantaan on aktiivisia yhteyksiä, yritä kirjautua ulos ja takaisin sisään pgAdminiin.
Voit tarkistaa tietokannan taulutilan sijainnin SQL-kyselyllä:
SELECT
spcname, pg_tablespace_location(oid)
FROM
pg_tablespace;Luo testausta varten tilapäinen taulu:
DROP TABLE IF EXISTS tmp_table;
CREATE TABLE tmp_table AS
SELECT x
FROM
generate_series(2,5000,2) AS x;Taulun käyttämän taulutilan voit tarkistaa seuraavalla komennolla:
SELECT
tablename, tablespace
FROM
pg_tables
WHERE
tablename = 'tmp_table';Muuta taulun taulutilaa seuraavalla komennolla:
ALTER TABLE
tmp_table
SET TABLESPACE
tmp_tablespace;Tarkista, että taulun taulutila on nyt muuttunut. Voit myös käyttää pgAdminin käyttöliittymää taulutilojen tarkastamiseen.
9.6 Harjoitus 8.6: Indeksin taulutilan muuttaminen
Voit luoda indeksin tauluun komennolla:
CREATE INDEX
idx_tmp_x
ON tmp_table(x);Indeksit luodaan oletusarvoisesti pg_default-taulutilaan. Usein käytetyt indeksit voi olla tarpeen tallentaa sellaiseen taulutilaan, joka käyttää palvelimessa olevaa nopeinta levyä (esimerkiksi SSD-levyt). Indeksien taulutilan muuttaminen onnistuu seuraavasti:
ALTER INDEX
idx_tmp_x
SET TABLESPACE
tmp_tablespace;