3

PostgreSQL 9.4

I have a table (call it tbl) that's got the column registration_date timestamp. Now, for simplicity I set the following statistic parameter:

ALTER TABLE tbl ALTER COLUMN registration_date SET STATISTICS 2;
ANALYZE tbl;

and than I've got the following statistic for the table (from pg_stats)

avg_width n_distinct  most_common_vals most_common_freqs    
    8         -1             ""               ""                   
                         histogram_bounds
 2012-03-26 10:32:15.379,2013-11-05 19:33:09.828,2015-11-19 14:39:04.676

And the query

SELECT reltuples,relpages FROM pg_class WHERE relname = 'tbl'

returns this:

reltuples relpages
 240656     2476

Now, I execute the query:

EXPLAIN ANALYZE SELECT *
FROM tbl
WHERE tbl.registration_date > '2014-11-11' -- 2014-11-11 is not in histgrom_bounds

And got the following ananlyzed plan:

Seq Scan on tbl  (cost=0.00..5484.20 rows=60441 width=47) (actual time=3.872..26.406 rows=604 loops=1)
  Filter: (registration_date > '2014-11-11 00:00:00'::timestamp without time zone)
  Rows Removed by Filter: 240052
Planning time: 0.119 ms
Execution time: 26.439 ms

QUESTION: How is the estimated number of rows computed in my case? I don't understand the 60441 rows count in the plan. Note that

SELECT reltuples/4 FROM pg_class WHERE relname = 'tbl'

returns 60164 which is less than 60441.

0

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.