Strange statistics

From: Henrik <henke(at)mac(dot)se>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange statistics
Date: 2008-06-03 13:44:13
Message-ID: 5B367B0C-2FD4-425E-8ECC-2D8441501D6A@mac.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2008-06-03 13:47:24 Re: Failing to recover after panic shutdown
Previous Message Henrik 2008-06-03 13:41:21 Re: Database growing. Need autovacuum help.