limit/offset und indexe

hallo,
ich habe eine Tabelle mit ca. 500.000 Einträgen, von denen mittels einer WHERE-Bedingung erst einmal 280.000 ausgeschlossen werden. Von den übrigen soll nach einem ORDER BY mittels OFFSET und LIMIT dann selektiert werden.

Als nächstes habe ich einen partiellen Index auf die zu sortierende Spalte gepackt (passend zu der WHERE-Bedingung) und die Abfragen funktionieren so lange gut, bis man mit OFFSET oder LIMIT mehr als etwa 26000 Einträge anfasst.

Bis dorthin wird brav der Index genutzt und vorwärts/rückwärts traversiert. Danach, naja … seht selbst.

gut

explain analyse
SELECT * 
FROM vt_product 
where product_type='rental' and product_activated = true 
order by product_begin desc
offset 25000
limit 25;

"Limit  (cost=51596.78..51648.38 rows=25 width=394) (actual time=59.996..59.996 rows=25 loops=1)"
"  ->  Index Scan Backward using test_index on vt_product  (cost=0.00..420181.47 rows=203589 width=394) (actual time=0.000..36.664 rows=25025 loops=1)"
"Total runtime: 59.996 ms"

schlecht

explain analyse
SELECT * 
FROM vt_product 
where product_type='rental' and product_activated = true 
order by product_begin desc
offset 28000
limit 25;

"Limit  (cost=55039.90..55039.96 rows=25 width=394) (actual time=986.599..986.599 rows=25 loops=1)"
"  ->  Sort  (cost=54969.90..55478.87 rows=203589 width=394) (actual time=956.601..966.600 rows=28025 loops=1)"
"        Sort Key: product_begin"
"        Sort Method:  top-N heapsort  Memory: 8250kB"
"        ->  Bitmap Heap Scan on vt_product  (cost=3273.53..38912.40 rows=203589 width=394) (actual time=73.329..429.976 rows=201273 loops=1)"
"              Recheck Cond: (product_activated AND ((product_type)::text = 'rental'::text))"
"              ->  Bitmap Index Scan on test_index  (cost=0.00..3222.64 rows=203589 width=0) (actual time=63.329..63.329 rows=201273 loops=1)"
"Total runtime: 989.932 ms"

Kann man das irgendwie umgehen und Postgresql dazu zwingen, den Index weiter zu benutzen?

Danke schonmal im Voraus

ciao
Stefan

Du kannst mal versuchsweise via ALTER TABLE table ALTER COLUMN column SET STATISTICS n mit n vielleicht 100 oder so PG drum bitten. Probier es aus, ob es hilft, wirst Du sehen.


Andreas

Sieht so aus, als ob Du das dann immer wieder so aufrufst, oder? Sinnvoller wäre es wohl, mit einem CURSOR zu arbeiten.

Andreas

Hallo,
ein Cursor funktioniert bei der einfachen Tabellenabfrage von oben wunderbar (er wandert brav den Index entlang). Sobald aber die nächste Tabelle dazu-gejoint wird, fängt er aber wieder an erst irgendwie die WHERE Bedingung zu erfüllen um dann hinterher aufwändig zu sortieren, anstatt dem Index zu folgen. Wenn ich die WHERE-Bedingungen rausnehme, klappt wieder alles wie gewollt.

Vielleicht bin auch gerade nur zu doof, die richtigen Indexe zu setzen.

Beispiel:

declare test_cursor cursor for

SELECT *
FROM vt_product as p
join vt_product_class pc
on p.product_class_id = pc.product_class_id
order by product_begin desc;

move absolute 10000 from test_cursor;
fetch forward 10 FROM test_cursor;
close test_cursor;

EXPLAIN ANALYSE:
Nested Loop  (cost=0.00..3835039.71 rows=536567 width=504) (actual time=0.000..4739.672 rows=536567 loops=1)
  ->  Index Scan Backward using index_product_begin_test on vt_product p  (cost=0.00..1276597.40 rows=536567 width=396) (actual time=0.000..813.279 rows=536567 loops=1)
  ->  Index Scan using index_product_class_id on vt_product_class pc  (cost=0.00..4.76 rows=1 width=108) (actual time=0.004..0.005 rows=1 loops=536567)
        Index Cond: (pc.product_class_id = p.product_class_id)
Total runtime: 4959.659 ms

Normal ausgeführt:
Abfrageergebnis mit 10 Zeilen wurde unterdrückt.
Abfrage war erfolgreich nach 126 ms. Keine Zeilen geliefert.

Das geht also erstmal ganz gut!

Auch das geht gut

declare test_cursor cursor for

SELECT *
FROM vt_product
where test=9
order by product_begin desc;

move absolute 10000 from test_cursor;
fetch forward 10 FROM test_cursor;
close test_cursor;

EXPLAIN ANALYSE:
Index Scan Backward using index_product_begin_test on vt_product  (cost=0.00..630161.94 rows=268874 width=396) (actual time=0.000..649.956 rows=268340 loops=1)
Total runtime: 776.613 ms

Normal ausgeführt:
Abfrageergebnis mit 10 Zeilen wurde unterdrückt.
Abfrage war erfolgreich nach 0 ms. Keine Zeilen geliefert.

Aber die Kombination von beidem geht halt garnicht

declare test_cursor cursor for

SELECT *
FROM vt_product p
join vt_product_class pc
on p.product_class_id = pc.product_class_id
where test=9
order by product_begin desc;

move absolute 10000 from test_cursor;
fetch forward 10 FROM test_cursor;
close test_cursor;

EXPLAIN ANALYSE:
Sort  (cost=158085.06..158757.24 rows=268874 width=500) (actual time=3093.121..3469.764 rows=268340 loops=1)
  Sort Key: vt_product.product_begin
  Sort Method:  external merge  Disk: 69072kB
  ->  Hash Join  (cost=26008.96..73640.73 rows=268874 width=500) (actual time=149.989..1549.889 rows=268340 loops=1)
        Hash Cond: (vt_product.product_class_id = vt_product_class.product_class_id)
        ->  Bitmap Heap Scan on vt_product  (cost=4365.20..45275.12 rows=268874 width=396) (actual time=109.992..499.967 rows=268340 loops=1)
              Recheck Cond: (test = 9)
              ->  Bitmap Index Scan on index_product_begin_test  (cost=0.00..4297.98 rows=268874 width=0) (actual time=93.327..93.327 rows=268340 loops=1)
        ->  Hash  (cost=21520.06..21520.06 rows=9896 width=108) (actual time=39.997..39.997 rows=9896 loops=1)
              ->  Index Scan using index_product_class_id on vt_product_class  (cost=0.00..21520.06 rows=9896 width=108) (actual time=0.000..23.332 rows=9896 loops=1)
Total runtime: 4216.377 ms


Normal auseführt:
Abfrageergebnis mit 10 Zeilen wurde unterdrückt.
Abfrage war erfolgreich nach 4413 ms. Keine Zeilen geliefert.

Das bringt mich langsam ein bisschen zur Verzweiflung.

PS: danke für eure Hilfe :slight_smile: