PostgreSQL Bugs - BUG #1855: usage of indexes

Author

BUG #1855: usage of indexes

Vladimir Kanazir

2005-08-29, 1:24 pm

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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-08-29, 8:26 pm

"Vladimir Kanazir" <canny@vlajko.com> writes:
> I can't stand it any more. Please fix damn indexes once for all, if you are
> able to.

You expect to get a polite response to this sort of thing? Especially
when you didn't show us any actual information (like EXPLAIN ANALYZE
output --- EXPLAIN alone does not prove that you've got a problem).

Please go read the available performance-tuning information. You might
find that twiddling random_page_cost would help the planner get closer
to reality on your platform, for example.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Alvaro Herrera

2005-08-29, 8:26 pm

On Mon, Aug 29, 2005 at 02:45:33PM +0100, Vladimir Kanazir wrote:

> WTF? Is it so hard to use damn indexes? With who I need to sleep to make
> this work?

<commercial RDBMS system> support department, maybe?

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org> )
Este mail se entrega garantizadamente 100% libre de sarcasmo.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links