Zmierz rozmiar wiersza tabeli PostgreSQL

Mam tabelę PostgreSQL. select *jest bardzo wolny, select idale szybki i przyjemny. Myślę, że może być tak, że rozmiar rzędu jest bardzo duży i transport zajmuje trochę czasu lub może to być jakiś inny czynnik.

Potrzebuję wszystkich pól (lub prawie wszystkich), więc wybranie tylko podzbioru nie jest szybką poprawką. Wybieranie żądanych pól jest nadal wolne.

Oto mój schemat tabeli bez nazw:

integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
integer                  | not null default 0
text                     | default '{}'::text
text                     | 
timestamp with time zone | 
integer                  | 
timestamp with time zone | 
integer                  | 

Rozmiar pola tekstowego może być dowolnego rozmiaru. Ale w najgorszym przypadku nie więcej niż kilka kilobajtów.

pytania

1. Czy jest w tym coś, co krzyczy „szalenie nieefektywne”?
2. Czy istnieje sposób pomiaru wielkości strony w wierszu polecenia Postgres, aby pomóc mi w debugowaniu?

Q2: way to measure page size

PostgreSQL udostępnia szereg funkcji rozmiaru obiektu bazy danych . W tym zapytaniu spakowałem najciekawsze i na dole dodałem kilka funkcji dostępu do statystyk . (Dodatkowy moduł pgstattuple zapewnia jeszcze więcej przydatnych funkcji.)

To pokaże, że różne metody pomiaru „wielkości wiersza” prowadzą do bardzo różnych wyników. Wszystko zależy dokładnie od tego, co chcesz zmierzyć.

To zapytanie wymaga Postgres 9.3 lub nowszego . W przypadku starszych wersji patrz poniżej.

Używanie VALUESwyrażenia w LATERALpodzapytaniu , aby uniknąć przeliterowania obliczeń dla każdego wiersza.

Zamień public.tbl(dwukrotnie) na opcjonalnie kwalifikowaną nazwę tabeli, aby uzyskać kompaktowy widok zebranych statystyk dotyczących wielkości twoich wierszy. Możesz zawinąć to w funkcję plpgsql do wielokrotnego użytku, podać nazwę tabeli jako parametr i użyć EXECUTE...

SELECT l.metric, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t                     -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);

Wynik:

              metryczny | bytes / ct | bytes_pretty | bytes_per_row
----------------------------------- + ---------- + --- ----------- + ---------------
 core_relation_size | 44138496 | 42 MB | 91
 mapa widoczności | 0 | 0 bajtów | 0
 free_space_map | 32768 | 32 kB | 0
 table_size_incl_toast | 44179456 | 42 MB | 91
 indexes_size | 33128448 | 32 MB | 68
 total_size_incl_toast_and_indexes | 77307904 | 74 MB | 159
 live_rows_in_text_representation | 29987360 | 29 MB | 62
 ------------------------------ | | |
 liczba_wierszy | 483424 | |
 live_tuples | 483424 | |
 dead_tuples | 2677 | |

W przypadku starszych wersji (Postgres 9.2 lub starszy):

WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'public.tbl'::regclass AS tbl      -- provide table name as string
   FROM   public.tbl t                       -- provide table name as name
   ), y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS metric
     , unnest(val)                 AS "bytes/ct"
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;

Ten sam wynik.

P1: anything inefficient?

Możesz zoptymalizować kolejność kolumn, aby zaoszczędzić kilka bajtów na wiersz, obecnie zmarnowanych na wypełnienie wyrównania:

integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

Oszczędza to od 8 do 18 bajtów na wiersz. Nazywam to „kolumną tetris” . Detale:

• Konfigurowanie PostgreSQL pod kątem wydajności odczytu
• Obliczanie i oszczędzanie miejsca w PostgreSQL

Weź również pod uwagę:

• Gdyby wszystkie wartości miały 36 znaków, wyszukiwanie indeksu byłoby zauważalnie szybsze przy użyciu char vs varchar

Przybliżenie wielkości wiersza, w tym zawartości edytowanej w TOAST , jest łatwe do uzyskania poprzez zapytanie o długość reprezentacji TEKSTOWEJ całego wiersza:

SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value;

Jest to przybliżone przybliżenie liczby bajtów, które zostaną pobrane po stronie klienta podczas wykonywania:

SELECT * FROM tablename WHERE primary_key=:value;

... zakładając, że wywołujący zapytanie żąda wyników w formacie tekstowym, co robi większość programów (format binarny jest możliwy, ale w większości przypadków nie jest to warte kłopotu).

Tę samą technikę można zastosować do zlokalizowania Nwierszy „największych w tekście” tablename:

SELECT primary_key, octet_length(t.*::text) FROM tablename AS t
   ORDER BY 2 DESC LIMIT :N;

Może się zdarzyć kilka rzeczy. Ogólnie wątpię, aby długość była problemem proksymalnym. Podejrzewam, że zamiast tego masz problem związany z długością.

Mówisz, że pola tekstowe mogą mieć nawet kilka kilogramów. Wiersz nie może przekroczyć 8k w pamięci głównej i jest prawdopodobne, że twoje większe pola tekstowe zostały TOASTED lub przeniesione z pamięci głównej do pamięci rozszerzonej w oddzielnych plikach. To sprawia, że ​​twoja główna pamięć jest szybsza (więc wybór id jest w rzeczywistości szybszy, ponieważ dostęp do mniejszej liczby stron dysku), ale select * staje się wolniejszy, ponieważ jest więcej losowych operacji we / wy.

Jeśli Twoje całkowite rozmiary wierszy nadal są znacznie mniejsze niż 8 KB, możesz spróbować zmienić ustawienia przechowywania. Ostrzegam jednak, że przy wstawianiu zbyt dużego atrybutu do pamięci głównej może się zdarzyć coś złego, więc najlepiej go nie dotykać, jeśli nie musisz, a jeśli tak, ustaw odpowiednie limity za pomocą ograniczeń sprawdzania. Dlatego transport nie jest jedyną rzeczą. Może to być zestawianie wielu, wielu pól, które wymagają losowych odczytów. Duża liczba losowych odczytów może również powodować pominięcia pamięci podręcznej, a wymagana duża ilość pamięci może wymagać zmaterializowania się rzeczy na dysku, a duża liczba szerokich wierszy, jeśli istnieje połączenie (i jest to jedno, jeśli występuje TOAST) może wymagać kosztowniejszych łączyć wzory itp.

Pierwszą rzeczą, na którą chciałbym spojrzeć, było wybranie mniejszej liczby wierszy i sprawdzenie, czy to pomoże. Jeśli to zadziała, możesz spróbować dodać więcej pamięci RAM również do serwera, ale zacznę i zobaczę, gdzie wydajność zaczyna spadać z powodu zmian w planie i braku pamięci podręcznej w pierwszej kolejności.

Korzystanie z wyżej wymienionych funkcji rozmiaru obiektu bazy danych :

SELECT primary_key, pg_column_size(tablename.*) FROM tablename;