Alternatives to the COUNT function
Introduction COUNT is one of the aggregate functions used to aggregate and compute data In other databases like MySQL, the total row count in a table is a pre-stored value that can be directly retrieved, but in PostgreSQL, to count data accurately, it must inspect every page/tuple to count only the live tuples returned to you Differences COUNT(*) : counts all rows including NULL values COUNT(column) : excludes rows with NULL values Visibility Map (VM) This is a file containing a sequence of bit arrays for each page, containing the following information: Bit 1: whether this page is all-visible (1 or 0) Bit 2: whether this page is all-frozen (1 or 0) How It Works When using COUNT: If there is no index, this is the worst-case scenario because PostgreSQL must use a sequential scan to load pages into RAM PostgreSQL uses a sequential scan and the visibility map to inspect each page, checking the all-visible bit to determine if dead tuples exist in that page If all-visible=0 , indicatin...