Re: What does Rows Counted mean

On Tue, Apr 5, 2016 at 11:38 AM, Rakesh Kumar <rakeshkumar464a3(at)gmail(dot)com>
wrote:

> This is on the object browser. When you navigate to the table and
> click on the table name, you will see the output on the right side and
> check the Properties tab.
>
> On Tue, Apr 5, 2016 at 10:37 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> > 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(dot)klaver(at)aklaver(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

FYI, in PgAdmin, there is an Option which determines if a _physical row
count_ should be performed if the _estimated_ row count is less than a
specified amount. It defaults to 2000.
To change it, Click on File, Options and under Browser click Properties.
There you can change the default.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.