What does Rows Counted mean

Started by Rakesh Kumarabout 10 years ago7 messagesgeneral

Jump to latest

Rakesh Kumar

rakeshkumar464a3@gmail.com

about 10 years ago

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.

thanks

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

about 10 years ago

In reply to: Rakesh Kumar (#1)

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

-> 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Rakesh Kumar

rakeshkumar464a3@gmail.com

about 10 years ago

In reply to: Adrian Klaver (#2)

Re: What does Rows Counted mean

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.klaver@aklaver.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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson

melvin6925@gmail.com

about 10 years ago

In reply to: Rakesh Kumar (#3)

Re: What does Rows Counted mean

On Tue, Apr 5, 2016 at 11:38 AM, Rakesh Kumar <rakeshkumar464a3@gmail.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.klaver@aklaver.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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.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.

Rakesh Kumar

rakeshkumar464a3@gmail.com

about 10 years ago

In reply to: Melvin Davidson (#4)

Re: What does Rows Counted mean

On Tue, Apr 5, 2016 at 1:10 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

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.

Ok that was it. Thanks.

Adrian Klaver

adrian.klaver@aklaver.com

about 10 years ago

In reply to: Melvin Davidson (#4)

Re: What does Rows Counted mean

On 04/05/2016 10:10 AM, Melvin Davidson wrote:

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.

So that means it does select count(*) from the_table every time you
click on the table properties if the estimated count is less then the
set amount?

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson

melvin6925@gmail.com

about 10 years ago

In reply to: Adrian Klaver (#6)

Re: What does Rows Counted mean

On Tue, Apr 5, 2016 at 1:37 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 04/05/2016 10:10 AM, Melvin Davidson wrote:

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.

So that means it does select count(*) from the_table every time you click
on the table properties if the estimated count is less then the set amount?

--

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

So that means it does select count(*) from the_table every time you click

on the table properties if the estimated count is less then the set amount?
I believe so. That is why the default is low (2k). If you set it higher, it
will naturally take longer.
I did not write or contribute to PgAdmin, so there is no point in
complaining about it. It's just a user option.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.