Tuning - indexer mm.

PostgreSQL er en meget effektiv databaser, og performer virkelig godt. Det kræver dog (næsten) altid hjælp af en DBA.

Indekser er vejen til god performance. Kun i nødstilfælde skal man tage alternativer i brug (afledte tabeller, materialiserede views o.lign.)

2 slags indekser anvendes normalt i PostgreSQL:

• GIST (til geometri)
• BTREE (til alt andet)

Eksempel - GIST index på geometrikolonne (understøtter "find objekter indenfor en boundingbox"):

CREATE INDEX my_index ON fot.skove USING gist (geometri);
SELECT id, skovtype, navn FROM fot.skove WHERE geometri && ST_GeomFromText('POLYGON((390309 7538535,390306 7538533,390307 7538531,390304 7538530,390308 7538522,390322 7538529,390319 7538536,390310 7538532,390309 7538535))',25832)

Eksempel - BTREE indeks til understøttelse af atomare datatyper:

CREATE INDEX my_index ON fot.skove USING BTREE (skovtype);
SELECT id, skovtype, navn FROM fot.skove WHERE skovtype = 2;

Men hvad hvis man vil understøtte:

SELECT id,navn FROM fot.skove WHERE skovtype = 2 AND geometri && ST_GeomFromText('POLYGON((390309 7538535,390306 7538533,390307 7538531,390304 7538530,390308 7538522,390322 7538529,390319 7538536,390310 7538532,390309 7538535))',25832);

CREATE INDEX my_index ON fot.skove USING gist (geometri) WHERE skovtype=2;

BTREE indekser kan indeholde flere kolonner - men bemærk rækkefølgen er afgørende:

SELECT id,navn FROM fot.skove WHERE skovtype = 2 AND navn like 'Rude%'

Overvej forskellen mellem følgende to indekser:

CREATE INDEX my_index ON fot.skove USING BTREE (skovtype, navn);
CREATE INDEX my_index ON fot.skove USING BTREE (navn, skovtype);

"Korrekte" indekser er meget afhængig af data (fordeling og mængde)!

Tommelfingerregler:

• Fremmednøglekolonner skal indekseres
• Primary keys skal indekseres (gøres pr. default af PostgreSQL)
• Unique keys skal indekseres
• Alle geometrikolonner skal indekseres (GIST)
• Kend dine kunder - deres almindelige forespørgsler skal understøttes

Tips til optimering:

• Undersøg log-filer på PostgreSQL serveren for at kigge efter "dyre" forespørgsler. Husk opsæt log_statement = 'all' i PostgreSQL konfigurationen.
• Test dine forespørgsler (fra pgAdmin: Query->explain analyze - viser PostgreSQL's strategi)
• Pas på almindelige faldgruber:
  • SELECT * FROM mytable WHERE navn like '%skov%';
  • SELECT * FROM mytable WHERE upper(navn) = 'CHRISTIAN';
• Kend dine data.
• Vær ydmyg overfor tuning - det er ikke enkelt!

Fantastisk god ressource til forståelse af database-optimering: http://use-the-index-luke.com/