Szybki sposób na sprawdzenie liczby wierszy tabeli w PostgreSQL

Aby obliczyć procent, muszę znać liczbę wierszy w tabeli. Jeśli całkowita liczba jest większa niż pewna predefiniowana stała, użyję stałej wartości. W przeciwnym razie użyję rzeczywistej liczby wierszy.

Mogę użyć SELECT count(*) FROM table. Ale jeśli moja stała wartość to 500 000, a w tabeli mam 5 000 000 000 wierszy, liczenie wszystkich wierszy spowoduje stratę czasu.

Czy można przerwać liczenie, gdy tylko moja stała wartość zostanie przekroczona?

Potrzebuję dokładnej liczby wierszy tylko wtedy, gdy jest poniżej podanego limitu. W przeciwnym razie, jeśli liczba jest powyżej limitu, zamiast tego używam wartości granicznej i chcę odpowiedzi jak najszybciej.

Coś takiego:

SELECT text,count(*), percentual_calculus()  
FROM token  
GROUP BY text  
ORDER BY count DESC;

Zliczanie wierszy w dużych tabelach jest znane w PostgreSQL jako powolne. Aby uzyskać dokładną liczbę, musi wykonać pełne zliczenie wierszy ze względu na charakter MVCC . Istnieje sposób, aby radykalnie to przyspieszyć, jeśli liczba nie musi być dokładna, jak się wydaje w twoim przypadku.

Zamiast uzyskiwać dokładną liczbę ( powolne przy dużych stołach):

SELECT count(*) AS exact_count FROM myschema.mytable;

Otrzymujesz przybliżone oszacowanie w ten sposób ( niezwykle szybko ):

SELECT reltuples::bigint AS estimate FROM pg_class where relname='mytable';

To, jak dokładne jest oszacowanie, zależy od tego, czy biegasz ANALYZEwystarczająco. Zwykle jest bardzo blisko.
Zobacz FAQ Wiki PostgreSQL .
Lub dedykowana strona wiki dla wydajności count (*) .

Jeszcze lepiej

W artykule w PostgreSQL Wiki jest był nieco niechlujny . Zignorował możliwość, że może istnieć wiele tabel o tej samej nazwie w jednej bazie danych - w różnych schematach. Aby to uwzględnić:

SELECT c.reltuples::bigint AS estimate
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname = 'mytable'
AND    n.nspname = 'myschema'

Albo jeszcze lepiej

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

Szybszy, prostszy, bezpieczniejszy, bardziej elegancki. Zobacz podręcznik dotyczący typów identyfikatorów obiektów .

Użyj to_regclass('myschema.mytable')w Postgres 9.4+, aby uniknąć wyjątków dla nieprawidłowych nazw tabel:

• Jak sprawdzić, czy tabela istnieje w danym schemacie

TABLESAMPLE SYSTEM (n) w Postgres 9.5+

SELECT 100 * count(*) AS estimate FROM mytable TABLESAMPLE SYSTEM (1);

Podobnie jak w komentarzu @a_horse , nowo dodana klauzula SELECTpolecenia może być przydatna, jeśli statystyki w programie pg_classnie są z jakiegoś powodu wystarczająco aktualne. Na przykład:

• Żadnego autovacuumbiegania.
• Natychmiast po dużym INSERTlub DELETE.
• TEMPORARYstoły (które nie są objęte autovacuum).

To patrzy tylko na losowy n % ( 1w przykładzie) wybór bloków i liczy wiersze w nim. Większa próbka zwiększa koszt i zmniejsza błąd, Twój wybór. Dokładność zależy od wielu czynników:

• Rozkład rozmiaru wiersza. Jeśli zdarzy się, że dany blok ma szersze niż zwykle rzędy, liczba jest mniejsza niż zwykle itp.
• Martwe krotki lub FILLFACTORzajmują miejsce na blok. Jeśli rozkład jest nierównomierny w tabeli, oszacowanie może być nieprawidłowe.
• Ogólne błędy zaokrąglania.

W większości przypadków oszacowanie z pg_classbędzie szybsze i dokładniejsze.

Odpowiedz na aktualne pytanie

Najpierw muszę znać liczbę wierszy w tej tabeli, jeśli całkowita liczba jest większa niż pewna wstępnie zdefiniowana stała,

I czy to ...

... jest możliwe w momencie, gdy licznik przekroczy moją stałą wartość, zatrzyma zliczanie (i nie będzie czekać na zakończenie liczenia, aby poinformować, że liczba wierszy jest większa).

Tak. Możesz użyć podzapytania zLIMIT :

SELECT count(*) FROM (SELECT 1 FROM token LIMIT 500000) t;

Postgres faktycznie przestaje liczyć poza podany limit, otrzymasz dokładną i aktualną liczbę dla maksymalnie n wierszy (w przykładzie 500000) i n w przeciwnym razie. pg_classJednak nie tak szybko, jak szacowano .

Zrobiłem to raz w aplikacji postgres, uruchamiając:

EXPLAIN SELECT * FROM foo;

Następnie zbadaj dane wyjściowe za pomocą wyrażenia regularnego lub podobnej logiki. W przypadku prostego polecenia SELECT * pierwsza linia wyniku powinna wyglądać mniej więcej tak:

Seq Scan on uids  (cost=0.00..1.21 rows=8 width=75)

Możesz użyć tej rows=(\d+)wartości jako przybliżonego oszacowania liczby wierszy, które zostaną zwrócone, a następnie wykonaj rzeczywistą wartość tylko SELECT COUNT(*)wtedy, gdy oszacowanie jest, powiedzmy, mniejsze niż 1,5-krotność progu (lub dowolnej liczby, którą uznasz za sensowną dla Twojej aplikacji).

W zależności od złożoności zapytania liczba ta może być coraz mniej dokładna. W rzeczywistości w mojej aplikacji, gdy dodaliśmy łączenia i złożone warunki, stało się to tak niedokładne, że było całkowicie bezwartościowe, nawet wiedzieć, jak w ramach potęgi 100, ile wierszy byśmy zwrócili, więc musieliśmy porzucić tę strategię.

Ale jeśli zapytanie jest na tyle proste, że Pg może przewidzieć, z pewnym rozsądnym marginesem błędu, ile wierszy zwróci, może to zadziałać.

Odniesienie zaczerpnięte z tego bloga.

Możesz użyć poniżej, aby wyszukać liczbę wierszy.

Korzystanie z pg_class:

 SELECT reltuples::bigint AS EstimatedCount
    FROM   pg_class
    WHERE  oid = 'public.TableName'::regclass;

Korzystanie z pg_stat_user_tables:

SELECT 
    schemaname
    ,relname
    ,n_live_tup AS EstimatedCount 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

W Oracle można użyć rownumdo ograniczenia liczby zwracanych wierszy. Domyślam się, że podobna konstrukcja istnieje również w innych SQL. Tak więc w podanym przykładzie możesz ograniczyć liczbę zwracanych wierszy do 500001 i zastosować a count(*)następnie:

SELECT (case when cnt > 500000 then 500000 else cnt end) myCnt
FROM (SELECT count(*) cnt FROM table WHERE rownum<=500001)

Jak szeroka jest kolumna tekstu?

Z GROUP BY niewiele można zrobić, aby uniknąć skanowania danych (przynajmniej skanowania indeksu).

Polecam:

1. Jeśli to możliwe, zmiana schematu w celu usunięcia duplikatów danych tekstowych. W ten sposób zliczanie będzie się odbywać na wąskim polu klucza obcego w tabeli „wiele”.

2. Możesz też utworzyć wygenerowaną kolumnę z HASH tekstu, a następnie GROUP BY kolumnę z krzyżykiem. Ponownie, ma to na celu zmniejszenie obciążenia pracą (przejrzyj indeks wąskiej kolumny)

Edytować:

Twoje pierwotne pytanie nie do końca pasowało do Twojej zmiany. Nie jestem pewien, czy wiesz, że funkcja COUNT, gdy jest używana z funkcją GROUP BY, zwróci liczbę elementów na grupę, a nie liczbę elementów w całej tabeli.

Możesz uzyskać liczbę za pomocą poniższego zapytania (bez * lub jakichkolwiek nazw kolumn).

select from table_name;

W przypadku SQL Server (2005 lub nowszy) szybką i niezawodną metodą jest:

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('MyTableName')   
AND (index_id=0 or index_id=1);

Szczegóły dotyczące sys.dm_db_partition_stats są wyjaśnione w witrynie MSDN

Zapytanie dodaje wiersze ze wszystkich części (prawdopodobnie) podzielonej na partycje tabeli.

index_id = 0 to nieuporządkowana tabela (Heap), a index_id = 1 to uporządkowana tabela (indeks klastrowy)

Jeszcze szybsze (ale zawodne) metody są szczegółowo opisane tutaj.