The following bug has been logged online:
Bug reference: 1855
Logged by: Vladimir Kanazir
Email address: canny@vlajko.com
PostgreSQL version: 8.0.3
Operating system: linux
Description: usage of indexes
Details:
Guys,
I can't stand it any more. Please fix damn indexes once for all, if you are
able to.
Take a look into this:
\d history
Table "public.history"
Column | Type | Modifiers
-----------+-----------------------------+----------------------------------
----
-------------------
id | bigint | not null default
nextval('public.hist
ory_id_seq'::text)
date | date | default ('now'::text)::date
time | time without time zone | default ('now'::text)::time(6)
with t
ime zone
source | text | not null
dest | text | not null
message | bytea |
dcs | integer | default 0
esm | integer | default 0
s_ton | smallint | default 1
s_npi | smallint | default 1
d_ton | smallint | default 1
d_npi | smallint | default 1
status | integer | default -1
u_id | integer |
mess_id | text |
d_date | timestamp without time zone |
provider | integer | default -1
delivery | boolean | default true
p_id | integer |
msg_type | integer | default 1
ip | inet |
u_mess_id | text |
priority | smallint | default 2
price | numeric(20,10) |
Indexes:
"history_pkey" PRIMARY KEY, btree (id)
"history_date" btree (date)
"history_dest" btree (dest)
"history_dr" btree (date, mess_id, provider)
"history_mess_id" btree (mess_id)
"history_users" btree (u_id)
Foreign-key constraints:
"$1" FOREIGN KEY (u_id) REFERENCES users(id)
"$3" FOREIGN KEY (provider) REFERENCES providers(id)
"$4" FOREIGN KEY (p_id) REFERENCES protocols(id)
"$5" FOREIGN KEY (msg_type) REFERENCES msg_type(id)
Now, take a look into these queries:
explain select count(*) from history where date>='2005-06-01';
QUERY PLAN
------------------------------------------------------------------------
Aggregate (cost=372159.67..372159.67 rows=1 width=0)
-> Seq Scan on history (cost=0.00..357907.19 rows=5700991 width=0)
Filter: (date >= '2005-06-01'::date)
(3 rows)
But, if I use this query:
explain select count(*) from history where date>=current_date;
QUERY PLAN
----------------------------------------------------------------------------
-----------
Aggregate (cost=1621.61..1621.61 rows=1 width=0)
-> Index Scan using history_date on history (cost=0.00..1620.40
rows=482 width=0)
Index Cond: (date >= ('now'::text)::date)
(3 rows)
WTF? Is it so hard to use damn indexes? With who I need to sleep to make
this work?
Further tests are more interesting:
explain select count(*) from history where date>='2005-08-29';
QUERY PLAN
----------------------------------------------------------------------------
-----------
Aggregate (cost=1621.61..1621.61 rows=1 width=0)
-> Index Scan using history_date on history (cost=0.00..1620.40
rows=482 width=0)
Index Cond: (date >= '2005-08-29'::date)
(3 rows)
Also, works with:
explain select count(*) from history where date>='2005-08-28';
QUERY PLAN
----------------------------------------------------------------------------
---------------
Aggregate (cost=146686.79..146686.79 rows=1 width=0)
-> Index Scan using history_date on history (cost=0.00..146577.37
rows=43766 width=0)
Index Cond: (date >= '2005-08-28'::date)
(3 rows)
But, if I move one day more:
explain select count(*) from history where date>='2005-08-27';
QUERY PLAN
-----------------------------------------------------------------------
Aggregate (cost=358383.49..358383.49 rows=1 width=0)
-> Seq Scan on history (cost=0.00..357907.19 rows=190521 width=0)
Filter: (date >= '2005-08-27'::date)
(3 rows)
The database is vaccuumed every 12 hours.
Also, I had the same problem with 8.0.0 version.
