Tuesday, June 3, 2008

[GENERAL] Strange statistics

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

Cheers,
Henke

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

No comments: