Hoe vind je het aantal rijen voor al je tafels in Postgres

Ik ben op zoek naar een manier om het aantal rijen voor al mijn tabellen in Postgres te vinden. Ik weet dat ik dit tafel voor tafel kan doen met:

SELECT count(*) FROM table_name;

maar ik zou graag het aantal rijen voor alle tafels willen zien en dan op basis daarvan bestellen om een ​​idee te krijgen van hoe groot al mijn tafels zijn.

Antwoord 1, autoriteit 100%

Er zijn drie manieren om dit soort telling te krijgen, elk met hun eigen afwegingen.

Als je een echte telling wilt, moet je de SELECT-instructie uitvoeren zoals je die voor elke tabel hebt gebruikt. Dit komt omdat PostgreSQL de zichtbaarheidsinformatie van rijen in de rij zelf bewaart, niet ergens anders, dus een nauwkeurige telling kan alleen relatief zijn aan een transactie. U krijgt een telling van wat die transactie ziet op het moment dat deze wordt uitgevoerd. Je zou dit kunnen automatiseren zodat het tegen elke tabel in de database wordt uitgevoerd, maar waarschijnlijk heb je dat niveau van nauwkeurigheid niet nodig of wil je zo lang wachten.

De tweede benadering merkt op dat de verzamelaar van statistieken op enig moment ongeveer bijhoudt hoeveel rijen “live” (niet verwijderd of verouderd door latere updates) zijn. Deze waarde kan iets afwijken bij zware activiteit, maar is over het algemeen een goede schatting:

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

Dat kan je ook laten zien hoeveel rijen dood zijn, wat zelf een interessant getal is om te controleren.

De derde manier is om op te merken dat het systeem ANALYZE-commando, dat vanaf PostgreSQL 8.3 regelmatig wordt uitgevoerd door het autovacuümproces om tabelstatistieken bij te werken, ook een rijschatting berekent. Je kunt die zo pakken:

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

Welke van deze zoekopdrachten het beste is om te gebruiken, is moeilijk te zeggen. Normaal gesproken neem ik die beslissing op basis van of er meer nuttige informatie is die ik ook wil gebruiken in pg_class of in pg_stat_user_tables. Voor basisteldoeleinden, gewoon om te zien hoe groot dingen in het algemeen zijn, moeten beide nauwkeurig genoeg zijn.

Antwoord 2, autoriteit 17%

Hier is een oplossing waarvoor geen functies nodig zijn om een ​​nauwkeurige telling voor elke tafel te krijgen:

select table_schema, 
       table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t

query_to_xmlvoert de doorgegeven SQL-query uit en retourneert een XML met het resultaat (het aantal rijen voor die tabel). De buitenste xpath()zal dan de telinformatie uit die XML extraheren en deze omzetten in een getal

De afgeleide tabel is niet echt nodig, maar maakt de xpath()een beetje gemakkelijker te begrijpen – anders zou de hele query_to_xml()moeten worden doorgegeven aan de xpath()functie.

Antwoord 3, autoriteit 5%

Bekijk Het antwoord van Greg Smithvoor schattingen.

Om exacte aantallen te krijgen, worden de andere antwoorden tot nu toe geplaagd door enkele problemen, waarvan sommige ernstig zijn (zie hieronder). Hier is een versie die hopelijk beter is:

CREATE FUNCTION rowcount_all(schema_name text default 'public')
  RETURNS table(table_name text, cnt bigint) as
$$
declare
 table_name text;
begin
  for table_name in SELECT c.relname FROM pg_class c
    JOIN pg_namespace s ON (c.relnamespace=s.oid)
    WHERE c.relkind = 'r' AND s.nspname=schema_name
  LOOP
    RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I',
       table_name, schema_name, table_name);
  END LOOP;
end
$$ language plpgsql;

Er is een schemanaam nodig als parameter, of publicals er geen parameter is opgegeven.

Als u wilt werken met een specifieke lijst met schema’s of een lijst die uit een query komt zonder de functie te wijzigen, kan deze vanuit een query als volgt worden aangeroepen:

WITH rc(schema_name,tbl) AS (
  select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;

Dit levert een uitvoer van 3 kolommen op met het schema, de tabel en het aantal rijen.

Hier zijn enkele problemen in de andere antwoorden die deze functie vermijdt:

• Tabel- en schemanamen mogen niet in uitvoerbare SQL worden geïnjecteerd zonder aanhalingstekens, ofwel met quote_identof met de modernere functie format()met zijn %Iopmaaktekenreeks. Anders kan een kwaadwillende zijn tafel de naam tablename;DROP TABLE other_tablegeven, wat perfect geldig is als tafelnaam.

• Zelfs zonder de SQL-injectie en problemen met grappige tekens, kan de tabelnaam voorkomen in varianten die per geval verschillen. Als een tabel abcdheet en een andere abcd, moet de SELECT count(*) FROM...een naam tussen aanhalingstekens gebruiken, anders wordt het sla abcdover en tel abcdtwee keer. De indeling %Idoet dit automatisch.

• information_schema.tablesvermeldt naast tabellen ook aangepaste samengestelde typen, zelfs wanneer table_type 'BASE TABLE'(!) is. Als gevolg hiervan kunnen we information_schema.tablesniet herhalen, anders lopen we het risico dat we select count(*) from name_of_composite_typehebben en dat zou mislukken. OTOH pg_class where relkind='r'altijd goed zou moeten werken.

• Het type COUNT() is bigint, niet int. Er kunnen tabellen met meer dan 2,15 miljard rijen bestaan ​​(het is echter een slecht idee om er een telling(*) op uit te voeren).

• Er hoeft geen permanent type te worden gemaakt voor een functie om een ​​resultatenset met meerdere kolommen te retourneren. RETURNS TABLE(definition...)is een beter alternatief.

Antwoord 4, autoriteit 3%

Als u mogelijk verouderde gegevens niet erg vindt, kunt u toegang tot dezelfde statistieken die worden gebruikt door de query-optimizer.

Zoiets als:

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;

Antwoord 5

Dit werkte voor mij

SELECTEER schemanaam,relnaam,n_live_tup VANAF pg_stat_user_tables ORDER OP
n_live_tup DESC;

Antwoord 6

Ik weet de URL niet meer van waaruit ik dit heb verzameld. Maar ik hoop dat dit je zou moeten helpen:

CREATE TYPE table_count AS (table_name TEXT, num_rows INTEGER); 
CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD; 
    t_name RECORD; 
    r table_count%ROWTYPE; 
BEGIN
    FOR t_name IN 
        SELECT 
            c.relname
        FROM
            pg_catalog.pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE 
            c.relkind = ''r''
            AND n.nspname = ''public'' 
        ORDER BY 1 
        LOOP
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.relname 
            LOOP 
            END LOOP; 
            r.table_name := t_name.relname; 
            r.num_rows := the_count.count; 
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql; 

Als u select count_em_all();uitvoert, krijgt u het aantal rijen van al uw tabellen.

Antwoord 7

Ik heb een kleine variatie gemaakt om alle tabellen op te nemen, ook voor niet-openbare tabellen.

CREATE TYPE table_count AS (table_schema TEXT,table_name TEXT, num_rows INTEGER); 
CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD; 
    t_name RECORD; 
    r table_count%ROWTYPE; 
BEGIN
    FOR t_name IN 
        SELECT table_schema,table_name
        FROM information_schema.tables
        where table_schema !=''pg_catalog''
          and table_schema !=''information_schema''
        ORDER BY 1,2
        LOOP
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.table_schema||''.''||t_name.table_name
            LOOP 
            END LOOP; 
            r.table_schema := t_name.table_schema;
            r.table_name := t_name.table_name; 
            r.num_rows := the_count.count; 
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql; 

gebruik select count_em_all();om het aan te roepen.

Ik hoop dat je dit nuttig vindt.
Paul

Antwoord 8

Geëxtraheerd uit mijn commentaar in het antwoord van GregSmith om het leesbaarder te maken:

with tbl as (
  SELECT table_schema,table_name 
  FROM information_schema.tables
  WHERE table_name not like 'pg_%' AND table_schema IN ('public')
)
SELECT 
  table_schema, 
  table_name, 
  (xpath('/row/c/text()', 
    query_to_xml(format('select count(*) AS c from %I.%I', table_schema, table_name), 
    false, 
    true, 
    '')))[1]::text::int AS rows_n 
FROM tbl ORDER BY 3 DESC;

Met dank aan @a_horse_with_no_name

Antwoord 9

U kunt deze zoekopdracht gebruiken om alle tafelnamen met hun tellingen te genereren

select ' select  '''|| tablename  ||''', count(*) from ' || tablename ||' 
union' from pg_tables where schemaname='public'; 

het resultaat van de bovenstaande zoekopdracht is

select  'dim_date', count(*) from dim_date union 
select  'dim_store', count(*) from dim_store union
select  'dim_product', count(*) from dim_product union
select  'dim_employee', count(*) from dim_employee union

U moet de laatste unie verwijderen en de puntkomma aan het einde toevoegen !!

select  'dim_date', count(*) from dim_date union 
select  'dim_store', count(*) from dim_store union
select  'dim_product', count(*) from dim_product union
select  'dim_employee', count(*) from dim_employee  **;**

RUN !!!

Antwoord 10

Hier is een veel eenvoudigere manier.

tables="$(echo '\dt' | psql -U "${PGUSER}" | tail -n +4 | head -n-2 | tr -d ' ' | cut -d '|' -f2)"
for table in $tables; do
printf "%s: %s\n" "$table" "$(echo "SELECT COUNT(*) FROM $table;" | psql -U "${PGUSER}" | tail -n +3 | head -n-2 | tr -d ' ')"
done

uitvoer zou er zo uit moeten zien

auth_group: 0
auth_group_permissions: 0
auth_permission: 36
auth_user: 2
auth_user_groups: 0
auth_user_user_permissions: 0
authtoken_token: 2
django_admin_log: 0
django_content_type: 9
django_migrations: 22
django_session: 0
mydata_table1: 9011
mydata_table2: 3499

u kunt het gedeelte psql -U "${PGUSER}"zo nodig bijwerken om toegang te krijgen tot uw database

merk op dat de syntaxis head -n-2mogelijk niet werkt in macOS, u kunt waarschijnlijk gewoon een andere implementatiedaar

Getest op psql (PostgreSQL) 11.2 onder CentOS 7

als je het gesorteerd wilt hebben op tabel, wikkel het dan gewoon met sort

for table in $tables; do
printf "%s: %s\n" "$table" "$(echo "SELECT COUNT(*) FROM $table;" | psql -U "${PGUSER}" | tail -n +3 | head -n-2 | tr -d ' ')"
done | sort -k 2,2nr

uitvoer;

mydata_table1: 9011
mydata_table2: 3499
auth_permission: 36
django_migrations: 22
django_content_type: 9
authtoken_token: 2
auth_user: 2
auth_group: 0
auth_group_permissions: 0
auth_user_groups: 0
auth_user_user_permissions: 0
django_admin_log: 0
django_session: 0

Antwoord 11

Ik vind het antwoordvan Daniel Vérité goed.
Maar als u geen CREATE-instructie kunt gebruiken, kunt u ofwel een bash-oplossinggebruiken of, als u een Windows-gebruiker bent , een powershell:

# You don't need this if you have pgpass.conf
$env:PGPASSWORD = "userpass"
# Get table list
$tables = & 'C:\Program Files\PostgreSQL\9.4\bin\psql.exe' -U user -w -d dbname -At -c "select table_name from information_schema.tables where table_type='BASE TABLE' AND table_schema='schema1'"
foreach ($table in $tables) {
    & 'C:\path_to_postresql\bin\psql.exe' -U root -w -d dbname -At -c "select '$table', count(*) from $table"
}

Antwoord 12

Ik wilde het totaal van alle tabellen + een lijst met tabellen met hun tellingen. Een beetje zoals een prestatiegrafiek van waar de meeste tijd aan werd besteed

WITH results AS ( 
  SELECT nspname AS schemaname,relname,reltuples
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE 
      nspname NOT IN ('pg_catalog', 'information_schema') AND
      relkind='r'
     GROUP BY schemaname, relname, reltuples
)
SELECT * FROM results
UNION
SELECT 'all' AS schemaname, 'all' AS relname, SUM(reltuples) AS "reltuples" FROM results
ORDER BY reltuples DESC

Je kunt natuurlijk ook een LIMIT-clausule op de resultaten in deze versie zetten, zodat je zowel de grootste novertreders als het totaal krijgt.

Een ding dat hierbij moet worden opgemerkt, is dat u het een tijdje moet laten staan ​​​​na bulkimport. Ik heb dit getest door gewoon 5000 rijen toe te voegen aan een database over verschillende tabellen met behulp van echte importgegevens. Het toonde 1800 records gedurende ongeveer een minuut (waarschijnlijk een configureerbaar venster)

Dit is gebaseerd op https://stackoverflow.com/a/2611745/1548557werk, dus bedankt en herkenning voor de zoekopdracht die moet worden gebruikt binnen de CTE

13

Hieronder vraag geeft ons rijtelling en -grootte voor elke tabel

Selecteer Table_schema, TACE_NAME,
pg_relation_size (‘”‘ || table_schema || ‘”. “‘ || tabel_name || ‘”‘) / 1024/1024 size_mb,
(XPath (‘/ rij / C / Text ()’, query_to_xml (formaat (‘selecteer telling (*) als C van% I.% I’, TACE_SCHEMA, TACE_NAME),
false, true, ”)))))) [1] :: Tekst :: int als rows_n
van informatie_schema.tables
bestel op maat_mb Desc;