Search Postgresql Archives

Re: Strange statistics

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




3 jun 2008 kl. 23.31 skrev Joris Dobbelsteen:

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.
Thanks Joris for your input. You are the second person that suggests CLUSTER for me. Maybe I should take a look. The problem is that our select queries are kinda random. Would CLUSTER help then also? Should I just CLUSTER on the moste used index or?

Thanks
/henke




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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux