Re: Strange statistics

Henrik wrote:
> Hi list,
>
> I'm having a table with a lots of file names in it. (Aprox 3 million) in
> a 8.3.1 db.
>
> Doing this simple query shows that the statistics is way of but I can
> get them right even when I raise the statistics to 1000.
>
> db=# alter table tbl_file alter file_name set statistics 1000;
> ALTER TABLE
> db=# analyze tbl_file;
> ANALYZE
> db=# explain analyze select * from tbl_file where lower(file_name) like
> lower('to%');
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
>
> Bitmap Heap Scan on tbl_file (cost=23.18..2325.13 rows=625 width=134)
> (actual time=7.938..82.386 rows=17553 loops=1)
> Filter: (lower((file_name)::text) ~~ 'to%'::text)
> -> Bitmap Index Scan on tbl_file_idx (cost=0.00..23.02 rows=625
> width=0) (actual time=6.408..6.408 rows=17553 loops=1)
> Index Cond: ((lower((file_name)::text) ~>=~ 'to'::text) AND
> (lower((file_name)::text) ~<~ 'tp'::text))
> Total runtime: 86.230 ms
> (5 rows)
>
>
> How can it be off by a magnitude of 28??

These are statistics and represent an only estimate! In this case, the
planner seems to be doing the right thing(tm) anyway.

Statistics is a frequently misunderstood subject and usually provides
excellent material to draw plain wrong conclusions. There is a good
chance that due to the physical layout of your data, the algorithms in
the statistics collector, the existence of uncertainty and some more
unknown factors your statistics will be biased. This is a situations
where you noticed it.

Running "SELECT * FROM pg_stats;" will give you the statistics the
planner uses and can provide some hints to why the planner has chosen
these estimates.
Probably statistics will vary between ANALYZE runs. Its also possible to
try "CLUSTER" and friends. Try different queries and look at the deviations.

All in all, you should really start worrying when the planner starts
planning inefficient queries. Since its a filename, it might be highly
irregular (random) and a low statistics target might be good enough anyways.

Unfortunately I'm not a statistics expert...

- Joris