BUG #1855: usage of indexes - Mailing list pgsql-bugs

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.