On Tue, Apr 5, 2016 at 11:38 AM, Rakesh Kumar <rakeshkumar46...@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.kla...@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.kla...@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.
