[SQL] Postgres expression index werkt alleen met = operator.

[flowerp]

Ik heb een tabel met daarin onder andere 3 integer columns. Nu wil ik snel weten voor welke rijen deze 3 bijelkaar opgeteld groter is dan een bepaalde waarde. De tabel heeft enkele miljoenen rijen. Een straightforward query doet er op een X2 5000+ met 4GB memory en snelle disk zo'n 20 seconden over.

Ik dacht dat het beter zou kunnen en maakte er een expression index voor aan. Voor de netheid maakte ik eerst een procedure aan voor het optellen:

code:

CREATE or REPLACE FUNCTION sum3(integer, integer, integer) RETURNS integer AS $$
DECLARE

BEGIN   
 RETURN $1 + $2 + $3;
END;

$$ LANGUAGE plpgsql IMMUTABLE;

Daarna de index als volgt:

SQL:

CREATE INDEX table1_sum3test_idx ON table1 (sum3(nr1, nr2, nr3));

Vervolgens doe ik een query:

SQL:

select * from table1 where sum3(nr1,nr2,nr3) = 2000;

Deze werkt perfect. Executie duurt enkele milliseconden. Via explain analyze zie ik dat PG gewoon de aangemaakte index gebruikt.

Doe ik echter de volgende query:

SQL:

select * from table1 where sum3(nr1,nr2,nr3) > 2000;

Dan pakt PG ijskoud 60 seconden, en is niet te bewegen om ook maar even te kijken in de index. Nu gebruikt de index een gewone btree en de normale int4_ops. Deze zou gewoon de > operator kunnen gebruiken.

Natuurlijk kan PG soms besluiten om de index niet toe te passen als een seqscan sneller is, maar dat is hiet dus overduidelijk niet het geval. Vacuum, analyze, full, etc, helpt ook niet.

Iemand enig idee?

[jochemd]

Laat maar eens een explain analyze zien, schakel dan seqscans uit in de planner en laat nog een explain analyze zien.

[flowerp]

Sequence scans uitschakelen zal ik later even posten, dit zijn de resultaten van explain op de huidige situatie.

code:

"Limit  (cost=0.00..3.93 rows=10 width=128) (actual time=0.070..0.152 rows=10 loops=1)"
"  ->  Index Scan using table1_sum3test_idx on table1  (cost=0.00..3988.77 rows=10163 width=128) (actual time=0.068..0.143 rows=10 loops=1)"
"        Index Cond: (sum3("nr1", "nr2", "nr3") = 1750)"
"Total runtime: 0.191 ms"

code:

"Limit  (cost=0.00..2.12 rows=10 width=128) (actual time=63314.521..72752.249 rows=10 loops=1)"
"  ->  Seq Scan on table1 (cost=0.00..558665.79 rows=2638929 width=128) (actual time=63314.519..72752.237 rows=10 loops=1)"
"        Filter: (sum3("nr1", "nr2", "nr3") > 1750)"
"Total runtime: 72752.300 ms"

Dit is voor de bovengenoemde query met een limit 10; erachter. Ik heb ook nog geprobeerd om gewoon een extra kolom aan te maken en daar de sum in te zetten en een index eroverheen, maar dan pakt ie ook de index niet bij gebruik van de > operator. Misschien dat bij een bepaalde hoeveelheid waardes, of een bepaalde distributie ervan, de PG planner in de war raakt? Natuurlijk heb ik vacuum etc paar keer gedraaid.

[jochemd]

flowerp schreef op maandag 16 april 2007 @ 08:35:

Dit is voor de bovengenoemde query met een limit 10

Niet doen. LIMIT beinvloedt de planner en maakt een zogeheten fast-start plan waarschijnlijker. Daarnaast is het vervolgens onmogelijk om te bepalen of de schattingen van de selectiviteit correct zijn. Gewoon de exacte queries die je zelf ook gebruikt nemen.

[ACM]

Vacuum draaien is niet zo belangrijk, analyze is belangrijker voor goede beslissingen. Maar vziw maakt Postgres geen statistieken van de indexwaarden zelf.

Overigens zorgt je limit 10 er nu voor dat er niet na te gaan is of er een grote afwijking in aantallen tussen de schatting of de werkelijkheid zit, als je het aantal resultaten wilt beperken kan je beter een count(*) in de selectlist zetten.

Seq scans uitzetten kan je trouwens gewoon op de sql-commandline of met je client meegeven, 'set enable_seqscan to off' als query opgeven en je ze staan (min of meer) uit.

[flowerp]

Bedankt voor de antwoorden tot zover.

Ik ben na wat meer proberen er achter dat het niet gebruiken van de index aan de grote van het (tussenliggende) resultaat te wijten is. Bij een kleiner aantal betrokken rijen wordt de index wel degelijk gebruikt voor operator >. Als ik bv > 4000 kies, dan is het uiteindelijke resultaat maar enkele 10.000'en rijen en duurt de executie slechts een paar ms.

Blijft natuurlijk wel dat het een niet al te mooie situatie is. 10 willekeurige rijen zouden met een index toch zo opgehaald moeten zijn via een btree. Bij >= en een groot aantal rijen in het resultaat wordt de index overigens ook niet gebruikt.

Als ik zelf programmatisch een btree zou benaderen, dan is het niets meer dan in 1 stap naar de juiste node te gaan (2000 in mijn voorbeeld), en vanaf daar de tree slechts 10 nodes te volgen. Het is zo niet echt duidelijk waarom de PG planner daar nu 60 seconden lang de index voor wil nalopen.

Feitelijk is m'n praktische probleem dus opgelost, omdat ik veel vaker die grotere getallen (waaraan dus minder rijen voldoen) nodig heb, en de case met de kleine getallen en de limit slechts als test bedoeld was. Toch lijkt het me interessant om te weten waar de planner nu precies de mist in gaat bij gebruik van de limit.

[jochemd]

Als je antwoorden wil zal je toch echt de gevraagde informatie moeten posten.