Jak rozłożyć ctid na numery stron i wierszy?


16

Każdy wiersz w tabeli ma kolumnę systemową ctid typu, tidktóra reprezentuje fizyczną lokalizację wiersza:

create table t(id serial);
insert into t default values;
insert into t default values;
select ctid
     , id
from t;
ctid | ID
: ---- | -:
(0,1) | 1
(0,2) | 2)

dbfiddle tutaj

Jaki jest najlepszy sposób na poruszanie się tylko numer strony jako od ctidw najbardziej odpowiedni typ (np integer, bigintlub numeric(1000,0))?

Jedyny sposób mogę myśleć jest bardzo brzydka.


1
IIRC to typ wektora i nie mamy na nich metod akcesorów. Nie jestem pewien, czy możesz to zrobić za pomocą funkcji C. Craig na pewno powie :)
dezso

2
Czy możesz rzucić jako POINT? Na przykład. select ct[0], ct[1] from (select ctid::text::point as ct from pg_class where ...) y;
bma

1
Tytuł sugeruje, że szukasz zarówno numeru strony, jak i indeksu krotek , później zawężasz do numeru strony. Poszedłem z wersją w ciele, indeks krotek jest trywialnym rozszerzeniem.
Erwin Brandstetter

Odpowiedzi:


21
SELECT (ctid::text::point)[0]::bigint AS page_number FROM t;

Twój skrzypce z moim rozwiązaniem.

@bma już podpowiedział coś podobnego w komentarzu. Tutaj jest ...

Uzasadnienie dla tego typu

ctidjest typu tid(identyfikator krotki), wywoływany ItemPointerw kodzie C. Według dokumentacji:

To jest typ danych kolumny systemowej ctid. Identyfikator krotki to para ( numer bloku , indeks krotki w bloku ), który identyfikuje fizyczną lokalizację wiersza w tabeli.

Odważny nacisk moje. I:

( ItemPointer, znany również jakoCTID )

Blok ma 8 KB w standardowych instalacjach. Maksymalny rozmiar stołu to 32 TB . Logicznie wynika, że ​​numery bloków muszą zawierać co najmniej maksimum (obliczenia ustalone zgodnie z komentarzem @Daniel):

SELECT (2^45 / 2^13)::int      -- = 2^32 = 4294967294

Które pasowałoby do niepodpisanego integer. Podczas dalszych badań odkryłem w kodzie źródłowym, że ...

bloki są numerowane kolejno, od 0 do 0xFFFFFFFE .

Odważny nacisk moje. Co potwierdza pierwsze obliczenia:

SELECT 'xFFFFFFFE'::bit(32)::int8 -- max page number: 4294967294

Postgres używa liczby całkowitej ze znakiem i dlatego jest nieco krótszy. Nie mogłem jeszcze ustalić, czy reprezentacja tekstowa jest przesunięta, aby uwzględnić liczbę całkowitą ze znakiem. Dopóki ktoś tego nie wyjaśni, wrócę do bigint, co w każdym razie działa.

Odlew

W Postgres 9.3 nie ma zarejestrowanej obsady dla tego tidtypu:

SELECT *
FROM   pg_cast
WHERE  castsource = 'tid'::regtype
OR     casttarget = 'tid'::regtype;

 castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
(0 rows)

Nadal możesz przesyłać do text. W Postgres istnieje reprezentacja tekstowa :

Innym ważnym wyjątkiem jest to, że „automatyczne rzutowania konwersji we / wy”, wykonywane przy użyciu własnych funkcji we / wy typu danych w celu konwersji na tekst lub z innych typów ciągów, nie są jawnie reprezentowane pg_cast.

Reprezentacja tekstowa odpowiada reprezentacji punktu, który składa się z dwóch float8 liczb, a rzutowanie jest bezstratne.

Możesz uzyskać dostęp do pierwszej liczby punktów o indeksie 0. Przesyłaj do bigint . Voilá.

Występ

Przeprowadziłem szybki test na stole z 30 tys. Wierszy (najlepiej 5) na kilku alternatywnych wyrażeniach, które przyszły mi do głowy, w tym na Twoim oryginale:

SELECT (ctid::text::point)[0]::int                              --  25 ms
      ,right(split_part(ctid::text, ',', 1), -1)::int           --  28 ms
      ,ltrim(split_part(ctid::text, ',', 1), '(')::int          --  29 ms
      ,(ctid::text::t_tid).page_number                          --  31 ms
      ,(translate(ctid::text,'()', '{}')::int[])[1]             --  45 ms
      ,(replace(replace(ctid::text,'(','{'),')','}')::int[])[1] --  51 ms
      ,substring(right(ctid::text, -1), '^\d+')::int            --  52 ms
      ,substring(ctid::text, '^\((\d+),')::int                  -- 143 ms
FROM tbl;

intzamiast biginttutaj, w większości nieistotne dla celów testu. Nie powtarzałem się za bigint.
Rzutowanie t_tidopiera się na typie złożonym zdefiniowanym przez użytkownika, takim jak skomentował @Jake.
Istota tego: casting jest zwykle szybszy niż manipulacja sznurkiem. Wyrażenia regularne są drogie. Powyższe rozwiązanie jest najkrótsze i najszybsze.


1
Dzięki Erwin, przydatne rzeczy. Od tutaj wygląda ctidto 6 bajtów z 4 na stronie i 2 dla rzędu. Martwiłem się o casting, floatale chyba nie muszę tego robić z tego, co tu mówisz. Wygląda na to, że typ kompozytowy zdefiniowany przez użytkownika jest znacznie wolniejszy niż użycie point, czy też to uważasz?
Jack mówi, że spróbuj topanswers.xyz

@JackDouglas: Po dalszym dochodzeniu wróciłem do bigint. Rozważ aktualizację.
Erwin Brandstetter

1
@JackDouglas: Podoba mi się twój pomysł na obsadę kompozytową. Jest czysty i działa bardzo dobrze - nawet jeśli rzutowanie do pointiz powrotem int8jest jeszcze szybsze). Przesyłanie do predefiniowanych typów zawsze będzie nieco szybsze. Dodałem go do mojego testu, aby porównać. Zrobiłbym to (page_number bigint, row_number integer)na pewno.
Erwin Brandstetter

1
2^40jest tylko 1 TB, a nie 32 TB, co jest 2^45podzielone przez 2^13daje 2^32, dlatego dla numeru strony potrzebne są pełne 32 bity.
Daniel Vérité

1
Warto również zauważyć, że pg_freespacemap używa bigintdo blkno
Jack mówi, spróbuj wypróbować topanswers.xyz
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.