PostgreSQL table row count

PostgreSQL table row count

 

In many cases, we need to know the number of records in a table. If you find that you have such a requirement, you should also ask how accurate such statistics are. If you are making an accounting report, you need to be very accurate. If you create a web page stenographer, some errors may also be allowed.

Use count (*)

The traditional method for calculating the number of rows in a table is to use count (*), but count (*) is very slow, especially for a large table.
Webstat = # select count (*) from rawlog;
Count
---------
2058704
(1 row)

Time: 7202.873 MS
From the above query, we can see that the count (*) speed is very slow, so you should try to avoid using count (*),
But it is still the most accurate method.

Use System tables

One alternative to count (*) is to query the pg_class system table to obtain the approximate number of rows. This value after each Vacuum Operation
Change. The error of the number of rows you calculated is the number of rows deleted or inserted between vacuum. If the number of rows you calculated allows such a difference,
This method is your best choice. Remember, do not use this method in accounting statistics. When the more vacuum you send
The more accurate the number of rows you get.

This value is stored in the reltuples field of pg_class. The following query statement lists all tables in public mode.
Number of rows:

Select relname, reltuples
From pg_class R join pg_namespace n
On (relnamespace = n. OID)
Where relkind = 'r' and N. nspname = 'public ';

The object type is table type ('R'), and the mode is public. If we want to view the number of rows in a table, we can
Use the following statement:

Select reltuples
From pg_class R
Where relkind = 'r' and relname = 'mytable ';

Use Triggers

If you have to get the exact number of records and do not want to use count (*), you can consider using triggers for maintenance.
The number of records in the table. This method requires the creation of an insert trigger to increase the number and delete trigger to reduce
Quantity. The specific quantity can be stored in a separate table.

Create a row_counts table. The row_counts table contains a table name field: relname, a row Record Number segment:
Reltuples. First, you need to create a table, then create a trigger, and finally test the number of records in the table.

Create Table row_counts (
Relname text primary key,
Reltuples numeric );

I wrote a trigger function to process the insert and delete events of the table. We can easily determine the operation through tg_op.
Type, tg_relname to obtain the table name. Both are the special variables of the trigger.

Create or replace function count_trig ()
Returns trigger
$
Declare
Begin
If tg_op = 'insert' then
Execute 'Update row_counts set reltuples = reltuples + 1 where relname = ''' | tg_relname | '''';
Return new;
Elsif tg_op = 'delete' then
Execute 'Update row_counts set reltuples = reltuples-1 where relname = ''' | tg_relname | '''';
Return old;
End if;
End;
$
Language 'plpgsql ';

Similarly, I only wrote one function to add this trigger to the table. You do not need to do this. If so, you can
Write the same function to delete the trigger.

Create or replace function add_count_trigs ()
Returns void
$
Declare
REC record;
Q text;
Begin
For REC in select relname
From pg_class R join pg_namespace n on (relnamespace = n. OID)
Where relkind = 'r' and N. nspname = 'public' Loop
Q: = 'create trigger' | Rec. relname | '_ count before insert or delete on ';
Q: = q | Rec. relname | 'for each row execute procedure count_trig ()';
Execute Q;
End loop;
Return;
End;
$
Language 'plpgsql ';

After the vacuum Statement is issued, use the following statement to test the number of records in the table:

Insert into row_counts select relname, reltuples from pg_class;

There may also be some errors. For example, anything completed between vacuum and trigger creation will be ignored. to make accurate statistics, you need
Stop all activities on the server.

Even if you can run vacuum on each table, you can write a function to perform similar operations if you are not sure whether vacuum is running.
. This function is slower than vacuum, and there will be some errors if your database activities are frequent.

Create or replace function init_row_counts ()
Returns void
$
Declare
REC record;
CREC record;
Begin
For REC in select relname
From pg_class R join pg_namespace n on (relnamespace = n. OID)
Where relkind = 'r' and N. nspname = 'public' Loop
For CREC in execute 'select count (*) as rows from '| Rec. relname Loop
-- Nothing here, move along
End loop;
Insert into row_counts values (Rec. relname, CREC. Rows );
End loop;

Return;
End;
$
Language 'plpgsql ';

This function searches all tables in pg_class and uses count (*) to obtain the number of records for each table. Put the above one, and below
The operation sequence is listed:

Create a table with the number of record rows.
Create a trigger function.
If it is possible to stop the activity of the server.
Vacuum table.
In a transaction, add a trigger to the table to convert the number of records.

In this way, you can query the table with the number of row records or the number of records in the corresponding table.

Original article address:

Http://www.varlena.com/varlena/GeneralBits/120.php