Re: What does Rows Counted mean

On 04/05/2016 07:11 AM, Rakesh Kumar wrote:
> pgAdmin shows this:
>
> Name:   tableA
> OID
> Owner
> Tablespace
> Rows (estimated) : 100000
> Rows (Counted)   : not counted
>
> What is Rows (Counted) and why it is showing not counted even though
> the table has been analyzed.

Where is the above coming from in pgAdmin?

http://www.postgresql.org/docs/9.5/interactive/sql-analyze.html

"ANALYZE collects statistics about the contents of tables in the
database, and stores the results in the pg_statistic system catalog. "

so:

production=# analyze verbose projection;
INFO:  analyzing "public.projection"
INFO:  "projection": scanned 403 of 403 pages, containing 25309 live
rows and 0 dead rows; 25309 rows in sample, 25309 estimated total rows
ANALYZE


Therefore the results of ANALYZE are snapshots in time and are
considered to be estimates.

EXPLAIN ANALYZE gives you an actual count:

http://www.postgresql.org/docs/9.5/interactive/sql-explain.html

production=# explain analyze select count(*) from projection where
p_item_no < 100;
                                                           QUERY PLAN


------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=438.72..438.73 rows=1 width=0) (actual
time=1.655..1.655 rows=1 loops=1)
    ->  Bitmap Heap Scan on projection  (cost=13.74..436.96 rows=703
width=0) (actual time=0.328..1.226 rows=679 loops=1)
          Recheck Cond: (p_item_no < 100)
          Heap Blocks: exact=120


          ->  Bitmap Index Scan on pj_pno_idx  (cost=0.00..13.56
rows=703 width=0) (actual time=0.271..0.271 rows=679 loops=1)

                Index Cond: (p_item_no < 100)


  Planning time: 0.181 ms


  Execution time: 1.749 ms


(8 rows)





production=# select count(*) from projection where p_item_no < 100;

  count


-------


    679


(1 row)


But, again that is a snapshot of a point in time:


production=# begin;
BEGIN
production=# delete from projection where p_item_no < 25;
DELETE 117
production=# explain analyze select count(*) from projection where
p_item_no < 100;
                                                           QUERY PLAN


------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=438.72..438.73 rows=1 width=0) (actual
time=1.517..1.518 rows=1 loops=1)
    ->  Bitmap Heap Scan on projection  (cost=13.74..436.96 rows=703
width=0) (actual time=0.336..1.159 rows=562 loops=1)
          Recheck Cond: (p_item_no < 100)
          Heap Blocks: exact=120
          ->  Bitmap Index Scan on pj_pno_idx  (cost=0.00..13.56
rows=703 width=0) (actual time=0.271..0.271 rows=679 loops=1)
                Index Cond: (p_item_no < 100)
  Planning time: 0.214 ms
  Execution time: 1.610 ms
(8 rows)

production=# select count(*) from projection where p_item_no < 100;
  count
-------
    562
(1 row)

Note the difference in actual row count between the Bitmap Index Scan
and the Bitmap Heap Scan, due to the above taking place in an open
transaction where the 117 'deleted' rows are still in play until I
either commit or rollback.


Unless the table is getting absolutely no activity a row count is going
to be tied to a point in time.

>
> thanks
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com