[postgresql] index scan vs seq scan bij zelfde constructs

[Verwijderd]

Hoi,

Ik heb een query die een join doet op 2 tabellen.
Deze hebben de volgende vorm (vereenvoudigd)

code:

T1
id   status

T2
t1_id

Voor T1 heb ik (als test) 3 indexes: op id, op status en op (id,status)

Als ik nu de volgende query schrijf:

SQL:

SELECT *
FROM T1,T2
WHERE 
   id = t1_id

Wordt de index gebruikt.

Schrijf ik echter

SQL:

SELECT *
FROM T1,T2
WHERE 
   id = t1_id  AND
   status IN (2,3,4)

Dan wordt de index niet gebruikt. Explain geeft dan:

code:

-> Seq Scan on T1 ( cost = 179.89..593.75 rows=195 width = 84)
    Filter: ( (status=2) OR (status=3) OR (status=5)

Herschrijf ik mijn query nu naar het equivalente:

SQL:

SELECT *
FROM T1,T2
WHERE 
   id = t1_id  AND
  ((status = 2) OR (status =3) OR (status=4))

Dan wordt de index weer WEL gebruikt. Explain geeft dan:

code:

-> Index Scan using T1_status_index,T1_status_index,T1_status_index ON T1 (cost=0.00..22.40 rows=390 width=84)
    Index Cond: ((status = 2) OR (status =3) OR (status=4))

Zet ik er nog een conditie bij, bv:

SQL:

SELECT *
FROM T1,T2
WHERE 
   id = t1_id  AND
  ((status = 2) OR (status =3) OR (status=4)) AND
  id IN (
    SELECT id
    FROM T3
    WHERE bar = 'foo'
  )

Dan word de index weer NIET gebruikt.

Weet iemand waarom dit zo is? Als een index al niet meer gebruikt kan worden bij een paar condities in een where clause dan hebben die dingen bijna nergens zin. Ik heb zowat geen enkele query waarbij mijn where clause slechts 1 of 2 condities bevat.

Mijn DB is trouwens postgresql 7.4. (Deze constructie komt voor als onderdeel in een query die 700ms duurt. Aangezien iedereen altijd hier zegt dat elke query in 100ms moet kunnen, probeer ik deze sneller te maken.)

[ACM]

Niet elke query kan binnen 100ms Als die 700ms snel genoeg is, dan zou ik me er niet druk om maken.

Met zo weinig records (195) kan het best zijn dat een seq-scan gewoon het snelste is, omdat ie toch al alle db-pages op moet vragen. Dat ie bij de OR-versie wel en de IN-versie niet de index gebruikt lijkt op een bug. Draai je wel versie 7.4.7, er zijn wel wat bugs in de optimiser en planner gefixed vziw, 8.0.2 zou nog beter zijn natuurlijk (kwa performance iig).

Voer trouwens eens explain analyze uit, dan kan je zien of de ingeschatte waarden ook overeen komen met de werkelijke waarden. Als dat niet het geval is zal je het analyze commando op je tabellen los moeten laten (evt samen met vacuum).

Hoeveel tijdsverschil zit er eigenlijk tussen de OR en de IN-versie?

[Verwijderd]

Probleem zit in de OR. Als je je query herschrijft naar UNION zal je zien dat daarbij wel de index wordt gebruikt. Ten minste, dat gelde voor MSSQL Server 2000. Je zou eens kunnen kijken hoe snel onderstaande werkt en of deze de index gebruikt...

code:

SELECT 
    *
FROM 
    T1,T2
WHERE 
    id = t1_id
    AND
    status 2

UNION ALL

SELECT 
    *
FROM 
    T1,T2
WHERE 
    id = t1_id
    AND
    status 3

UNION ALL

SELECT 
    *
FROM 
    T1,T2
WHERE 
    id = t1_id
    AND
    status 4

[ACM]

Verwijderd schreef op woensdag 20 april 2005 @ 11:37:
Probleem zit in de OR. Als je je query herschrijft naar UNION zal je zien dat daarbij wel de index wordt gebruikt. Ten minste, dat gelde voor MSSQL Server 2000. Je zou eens kunnen kijken hoe snel onderstaande werkt en of deze de index gebruikt...

Je leest niet goed...

[Verwijderd]

Gaat dus om interpretatie van IN en OR, had te snel gelezen... Sorry, dan slaat het nergens op wat ik gezegd heb...

[jochemd]

Verwijderd schreef op woensdag 20 april 2005 @ 11:29:

SQL:

SELECT *
FROM T1,T2
WHERE 
   id = t1_id  AND
   status IN (2,3,4)

code:

-> Seq Scan on T1 ( cost = 179.89..593.75 rows=195 width = 84)
    Filter: ( (status=2) OR (status=3) OR (status=5)

SQL:

SELECT *
FROM T1,T2
WHERE 
   id = t1_id  AND
  ((status = 2) OR (status =3) OR (status=4))

code:

-> Index Scan using T1_status_index,T1_status_index,T1_status_index ON T1 (cost=0.00..22.40 rows=390 width=84)
    Index Cond: ((status = 2) OR (status =3) OR (status=4))

Weet iemand waarom dit zo is?

Omdat je queries niet equivalent zijn. Lees nou eens goed terug in de filter conditie vs. de index conditie van je EXPLAN welke queries je naar de database hebt gestuurd.

Als een index al niet meer gebruikt kan worden bij een paar condities in een where clause

Non sequitur. Zorg eerst eens gewoon dat je 2 queries de zelfde resultset hebben en geef de volledige EXPLAIN ANALYZE output.

Deze constructie komt voor als onderdeel in een query die 700ms duurt.

Dus je post een klein stukje van een query in de hoop dat een microoptimalisatie daar je query sneller maakt?


Je aanpak klopt niet en je queries kloppen niet. Leg het hele probleem voor in plaats van iets waarvan jij denkt dat het de oplossing zou kunnen zijn en geef voldoende (EXPLAIN ANALYZE) en correcte (zelfde resultset) informatie.

[Verwijderd]

ACM schreef op woensdag 20 april 2005 @ 11:37:
Niet elke query kan binnen 100ms Als die 700ms snel genoeg is, dan zou ik me er niet druk om maken.

Met zo weinig records (195) kan het best zijn dat een seq-scan gewoon het snelste is, omdat ie toch al alle db-pages op moet vragen.

Dat weet ik. Als het binnen 1 disk page valt wordt altijd seq. scan gebruikt.

Dat ie bij de OR-versie wel en de IN-versie niet de index gebruikt lijkt op een bug. Draai je wel versie 7.4.7, er zijn wel wat bugs in de optimiser en planner gefixed vziw, 8.0.2 zou nog beter zijn natuurlijk (kwa performance iig).

Ik draai versie 7.4.7 op Debian Linux.

Voer trouwens eens explain analyze uit, dan kan je zien of de ingeschatte waarden ook overeen komen met de werkelijke waarden. Als dat niet het geval is zal je het analyze commando op je tabellen los moeten laten (evt samen met vacuum).

Vacuum full en vacuum analyze heb ik beiden gedaan.

Hoeveel tijdsverschil zit er eigenlijk tussen de OR en de IN-versie?[/quote]

Die zijn exact hetzelfde. 3x runnen geeft voor de IN versie:
560, 620, 598

En voor de OR versie:
593, 606, 620

Het vervelende is, is dat de planner ook niet consequent is. Mijn DB veranderd op dit moment niet. Ik runde de query nogmaals met explain, en nu gebruikt ie bij 2x OR -wel- de index, en bij 3x niet.

dus AND (status=2 OR status=3) nu WEL index en bij
AND (status=2 OR status=3 OR status=4) GEEN index.

[Verwijderd]

jochemd schreef op woensdag 20 april 2005 @ 12:51:
Omdat je queries niet equivalent zijn. Lees nou eens goed terug in de filter conditie vs. de index conditie van je EXPLAN welke queries je naar de database hebt gestuurd.

Dat snap ik niet helemaal.

Ik had:

code:

status IN (2,3,4)

Dat heb ik alleen maar veranderd in:

code:

(status=2 OR status=3 OR status=4)

Waarom is dat niet equivalent?

Non sequitur. Zorg eerst eens gewoon dat je 2 queries de zelfde resultset hebben en geef de volledige EXPLAIN ANALYZE output.

De resultset was hetzelfde. Eerste ding wat ik controleerde natuurlijk

Dus je post een klein stukje van een query in de hoop dat een microoptimalisatie daar je query sneller maakt?

Dat wel waar ja. Ik wilde niet teveel detail hier posten maar de kwestie terug brengen tot de essentie. Na een analyze zag ik dat er veel seq. scans in mijn query plan zaten, en geen enkele index scan. Toen probeerde ik dus per geval na te gaan waarom de index niet gebruikt werd.

[Verwijderd]

Ok, ik heb het stukje uit mijn oorspronkelijke query even eruit gehaald en apart gezet.

Dit is dus de aparte, standalone query:

code:

SELECT *
FROM T1,T2
WHERE 
   id = t1_id  AND
  ((status = 2) OR (status =3) OR (status=4))

Het gedrag veranderd echter niet of dit een stuk is van de grotere query of apart staat. Op een of andere reden wordt de index nu niet meer gebruikt bij 3 OR condities, zowel in de aparte als de grotere query. Bij 2 OR condities ( als ik bv OR (status=4) weghaal), gebruikt ie wel de index.

Ik paste nu rechtstreeks de output van Explain, waarbij ik alleen even de lange tabel namen heb afgekort naar t1 en t2.

Met 2 or condities:

Merge Join (cost=31.44..207.00 rows=347 width=171) (actual time=2.695..20.845 rows=391 loops=1);
Merge Cond: ("outer".t1_id = "inner".id);
-> Index Scan using t2_t1_id_key on t2 (cost=0.00..169.40 rows=3139 width=43) (actual time=0.049..9.359 rows=3016 loops=1);
-> Sort (cost=31.44..32.31 rows=346 width=128) (actual time=2.599..2.851 rows=391 loops=1);
Sort Key: t1.id;
-> Index Scan using t1_id_index, t1_id_index on t1 (cost=0.00..16.85 rows=346 width=128) (actual time=0.044..1.391 rows=391 loops=1);
Index Cond: ((status = 2) OR (status = 3));
Total runtime: 21.517 ms;

Met 3 or condities:

Merge Join (cost=45.91..223.39 rows=475 width=171) (actual time=4.432..24.900 rows=606 loops=1);
Merge Cond: ("outer".t1_id = "inner".id);
-> Index Scan using t2_t1_id_key on t2 (cost=0.00..169.40 rows=3139 width=43) (actual time=0.055..9.397 rows=3016 loops=1);
-> Sort (cost=45.91..47.09 rows=474 width=128) (actual time=4.315..4.730 rows=607 loops=1);
Sort Key: t1.id;
-> Seq Scan on t1 (cost=0.00..24.84 rows=474 width=128) (actual time=0.217..2.348 rows=607 loops=1);
Filter: ((status = 2) OR (status = 3) OR (status = 4));
Total runtime: 25.732 ms;

In dit geval (in tegenstelling tot het eerste probleem) zijn de queries niet equivalent, maar het feit blijft dat de index eerst wel gebruikt werd bij 3 OR condities.

[jochemd]

Verwijderd schreef op woensdag 20 april 2005 @ 14:31:

code:

status IN (2,3,4)

Dat heb ik alleen maar veranderd in:

code:

(status=2 OR status=3 OR status=4)

Dat is niet wat je gedaan hebt. Ik zeg toch heel duidelijk "de filter conditie vs. de index conditie". Als je die uit je originele post haalt staat er:

code:

    Filter: ( (status=2) OR (status=3) OR (status=5)

    Index Cond: ((status = 2) OR (status =3) OR (status=4))

status = 4 is niet het zelfde als status = 5. Jouw hele redenatie is gebaseerd op drijfzand want de EXPLAIN output komt van queries die niet noodzakelijkerwijs de zelfde resultset opleveren.

Dat wel waar ja. Ik wilde niet teveel detail hier posten maar de kwestie terug brengen tot de essentie.

Terugbrengen tot hetgeen jij denkt dat de essentie is. Alleen wie zegt dat hetgeeen jij denkt dat de essentie is echt de essentie is? Ik neem aan dat inmiddels wel duidelijk is dat er het een en ander mis is met je argumentatie.

[jochemd]

Verwijderd schreef op woensdag 20 april 2005 @ 14:56:
Ok, ik heb het stukje uit mijn oorspronkelijke query even eruit gehaald en apart gezet.

Het stukje dat je nu laat zien duurt 20 ms dus in het optimale geval daalt de duur van je query van 700 ms naar 680 ms. Je hebt dus inderdaad het probleem tot de verkeerde essentie gereduceerd.

Kom nou eens met de EXPLAIN ANALYZE output van je volledige query.

[ Voor 17% gewijzigd door jochemd op 20-04-2005 15:16 ]

[Verwijderd]

jochemd schreef op woensdag 20 april 2005 @ 15:12:
[...]

Dat is niet wat je gedaan hebt. Ik zeg toch heel duidelijk "de filter conditie vs. de index conditie". Als je die uit je originele post haalt staat er:

code:

    Filter: ( (status=2) OR (status=3) OR (status=5)

    Index Cond: ((status = 2) OR (status =3) OR (status=4))

status = 4 is niet het zelfde als status = 5. Jouw hele redenatie is gebaseerd op drijfzand want de EXPLAIN output komt van queries die niet noodzakelijkerwijs de zelfde resultset opleveren.

Status 5 is een typo, sorry. Ik typte de output van analyze met de hand over (waarbij ik ook nog de echte tabel namen en index namen met de hand naar T1, T2 had vervangen).

Ik heb er wel heel goed opgelet dat ik IN door de exact matchende serie ORs had vervangen in de query zelf. Daarnaast nog de resultset vergeleken en die waren ook identiek.

Terugbrengen tot hetgeen jij denkt dat de essentie is. Alleen wie zegt dat hetgeeen jij denkt dat de essentie is echt de essentie is?

Niet de essentie van de HELE query, maar de essentie van het sub-probleem. Waarom de hele query langzaam is, is mischien meer mijn eigen probleem en niet echt interesant voor de gebruikers hier. Ik kan de hele query van 200 regels wel posten, inclusief de data-types van de tabellen, inclusief het aantal regels, inclusief de distributie van bepaalde waarden, maar dan wordt het zo'n groot ding dat niemand anders die dit leest erwat aan heeft.

-tijdens- het optimaliseren van deze query kwam ik dit sub-probleem tegen: waarom wordt die index wel gebruikt bij de ORs en niet bij de IN. Dat vroeg ik me gewoon af, en leek me interesant als topic. Dat het op de gehele query niet veel gaat schelen, en dat de problemen elders zitten is een heel ander verhaal.

Bijvoorbeeld, een andere bottleneck die ik tegenkwam was een coalesce in de sub query die boven ook al gaf:

SELECT *
FROM T1,T2
WHERE
id = t1_id AND
((status = 2) OR (status =3) OR (status=4)) AND
id IN (
SELECT id
FROM T3
WHERE COALESCE (bar, 'foo') = 'foo'
)

De executie tijd van de query was eerst +-1500ms. Door de COALESCE weg te halen en meteen bar = 'foo' te schrijven, ging de tijd terug naar de huidige +-700ms!

Ik zou heel graag toch de hele query geven, maar het is voor mijn werk en dus kan ik deze niet zomaar posten.

[ Voor 45% gewijzigd door Verwijderd op 20-04-2005 16:50 ]

[jochemd]

Verwijderd schreef op woensdag 20 april 2005 @ 16:38:

Status 5 is een typo, sorry. Ik typte de output van analyze met de hand over (waarbij ik ook nog de echte tabel namen en index namen met de hand naar T1, T2 had vervangen).

Ik heb er wel heel goed opgelet dat ik IN door de exact matchende serie ORs had vervangen in de query zelf. Daarnaast nog de resultset vergeleken en die waren ook identiek.

Ik ben niet overtuigd. Maar als je zeker weet dat ze gelijk zijn dan zit de sleutel tot de oplossing in het feit dat de schatting van het aantal rijen dat terugkomt een factor 2 verschilt.

]Niet de essentie van de HELE query, maar de essentie van het sub-probleem. Waarom de hele query langzaam is, is mischien meer mijn eigen probleem en niet echt interesant voor de gebruikers hier. Ik kan de hele query van 200 regels wel posten, inclusief de data-types van de tabellen, inclusief het aantal regels, inclusief de distributie van bepaalde waarden, maar dan wordt het zo'n groot ding dat niemand anders die dit leest erwat aan heeft.

Try me

-tijdens- het optimaliseren van deze query kwam ik dit sub-probleem tegen: waarom wordt die index wel gebruikt bij de ORs en niet bij de IN. Dat vroeg ik me gewoon af, en leek me interesant als topic.

Mij niet. Vergelijk de totale kosten maar eens middels een EXPLAIN ANALYZE op het moment dat je indexscans uit hebt staan vs. op het moment dat je seqscans uit hebt staan. Dat is om en nabij lood om oud ijzer.

[Verwijderd]

jochemd schreef op woensdag 20 april 2005 @ 18:35:
Try me

Ik zou alle data graag posten, maar denk dat het bedrijf waar ik werk daar dan niet gelukkig mee is

Mij niet. Vergelijk de totale kosten maar eens middels een EXPLAIN ANALYZE op het moment dat je indexscans uit hebt staan vs. op het moment dat je seqscans uit hebt staan. Dat is om en nabij lood om oud ijzer.

M.a.w. indexen zijn in de meeste gevallen totaal onnodig?

[jochemd]

Verwijderd schreef op donderdag 21 april 2005 @ 10:56:

M.a.w. indexen zijn in de meeste gevallen totaal onnodig?

Nee, in het geval van het voorbeeld dat je gaf. Gooi de volgende code maar eens door je database en maak een tabelletje van de resultaten:

code:

set enable seqscan off;
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2);
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2) OR (status =3);
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2) OR (status =3) OR (status=4);
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2) OR (status =3) OR (status=4) OR (status=5);
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2) OR (status =3) OR (status=4) OR (status=5) OR (status=6);
set enable seqscan on;
set enable indexscan off;
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2);
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2) OR (status =3);
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2) OR (status =3) OR (status=4);
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2) OR (status =3) OR (status=4) OR (status=5);
EXPLAIN ANALYZE SELECT * FROM T1 WHERE (status = 2) OR (status =3) OR (status=4) OR (status=5) OR (status=6);

Als het goed is zie je nu het patroon dat een index scan een cost heeft die proportioneel is met het aantal records dat je terugkrijgt terwijl een seqscan een cost heeft die ongeveer constant is. Jouw voorbeeld zit precies op het breekpunt waardoor het voor dat voorbeeld niet uitmaakt. Als je 1 of 2 statussen in je WHERE hebt zal het een indexscan worden, als je er 4 of meer hebt een seqscan en bij 3 is het een beetje random. (Jouw voorbeeld is niet één op één vergelijkbaar met dit voorbeeld omdat jij 2 tabellen gebruikt en dit voorbeeld noodzakelijkerwijs maar 1 tabel.)