[postgresql] stronicowanie / paginacja

[Goliatus]

Problem mam konkretny: jest sobie tabela z rekordami, z których każdy ma jakieś id.
Załóżmy, że chce mieć paginacje np. 20 rekordów na strone i teraz mając jakąś wartość id=X jak wyznaczyć numer strony, na której znalazłby się dany rekord nie używając zliczania i offset/limit większego niż 200.

Problem polega na tym, że postgre w zależności od ilości przydzielonej na niego pamięci, nie używa indeksów jeśli limit lub offset jest zbyt duże, a zliczanie to zliczanie. Przy dużej ilości rekordów zapytania wykonuja sie przez wiele sekund.

to co już wiem to dwie opcje:

• [codei=sql]SELECT id FROM tabela WHERE id < wartosc /* dodatkowe warunki */ ORDER BY id ASC OFFSET 20 LIMIT 1[/codei]i powtarzanie tego w pętli, zliczając przebegi aż dojde do X. To jest szybsze niż użycie count(*).
• osobna tabela, w której będę zapisywal informacje o stronach (numer_strony, pierwszy_rekord, ostatni_rekord), ale ze względu na dodatkowe warunki to jest kiepskie rozwiązanie

[bies]

Jak często usuwasz dane z tej tabeli?

[Goliatus]

Bardzo rzadko, praktycznie tylko dodaje.

[bies]

No to dodaj tam klucz zaciągający dane z sekwencji i normalnie wykonuj select * from tbl where idx >= 20*n and idx < 20*n + 20. A numer strony to  oczywiście idx / 20.

[Goliatus]

no to ok dla zapytania bez dodatkowych warunków

[bies]

No fakt, nie zauważyłem dodatkowych warunków. No to cóż, my preciousss PL/pqSQL. Otwórz kursor dla zapytania z warunkami. Przejdź (move) do początku strony. I pobierz (fetch) n danych.

[Goliatus]

A gdzie jest haczyk przy kursorach? 

[bies]

A gdzie jest haczyk przy kursorach? 

W postgresie to w ogóle nie wiem (poznaję go przy okazji odpowiadania na pytania na forum ). Natomiast, widziałem różne operacje (o koszmarnym stopniu komplikacji) robione na Oracle'u w PL/SQL-u przez kolegów z pracy. Sam się zastanawiałem dlaczego to działa, na dodatek tak dobrze. Strzelam, że łatwiej jest skompilować i optymalizować operacje na danych w języku imperatywnym niż deklaratywnym. Ale do końca to sam nie wiem.

Tak czy inaczej, wypróbuj.

[agent_J]

A gdzie jest haczyk przy kursorach? 

W postgresie to w ogóle nie wiem (poznaję go przy okazji odpowiadania na pytania na forum ). Natomiast, widziałem różne operacje (o koszmarnym stopniu komplikacji) robione na Oracle'u w PL/SQL-u przez kolegów z pracy. Sam się zastanawiałem dlaczego to działa, na dodatek tak dobrze. Strzelam, że łatwiej jest skompilować i optymalizować operacje na danych w języku imperatywnym niż deklaratywnym. Ale do końca to sam nie wiem.

Tak czy inaczej, wypróbuj.

Może używają stored procedures, które są wiele szybsze niż przesyłanie zapytań do parsowania.

[Goliatus]

Przecież nie chodzi o szybkość parsowania zapytania tylko o to, czy silnik przy przeszukiwaniu tabeli wykorzystuje indeks czy nie, a to czy jest to wywoływane przez zapytanie czy poprzez procedure(np. w plpgqslu) to nie ma znaczenia. Dodatkowo procedury zrobione w plpgsqlu mają taką przypadłość, że jeśli dasz tam kilka updatów i/lub insertów dla kilku różnych tabel to silnik zakłada locka na wszystkie na wejściu do procki i zdejmuje dopiero po wyjściu. Nie wiem czemu, ale wygląda na to, że w taki dziwny sposób są one kompilowane.

A paginacje ostatecznie zrobiłem tak:

osobna tabela, w której będę zapisywal informacje o stronach (numer_strony, pierwszy_rekord, ostatni_rekord), ale ze względu na dodatkowe warunki to jest kiepskie rozwiązanie

[Reg]

Ciekawy pomysł. W jaki sposób uaktualniasz tą tabelę? Obliczasz na której stronie znajdzie się dodawany czy usuwany rekord i uaktualniasz informacje o wszystkich następnych stronach w tej tabeli?