indices usage

Hello,

I have the following setup:

isp_leased=# \d traffic_stat
Table "traffic_stat"
Attribute | Type | Modifier
---------------+--------------------------+---------------
timestamp | timestamp with time zone | default now()
customer_name | character varying(20) |
class | smallint | default 0
bytes | integer |
code | smallint | default 0
source_ip | inet |
dest_ip | inet |
Indices: traffic_stat_idx1,
traffic_stat_idx2

isp_leased=# \d traffic_stat_idx1
Index "traffic_stat_idx1"
Attribute | Type
---------------+--------------------------
customer_name | character varying(20)
timestamp | timestamp with time zone
class | smallint
btree

isp_leased=# \d traffic_stat_idx2
Index "traffic_stat_idx2"
Attribute | Type
-----------+--------------------------
timestamp | timestamp with time zone
btree

There are two problems with indices.
First, traffic_stat_idx2 is not used even after I set enable_seqscan to false:

isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and timestamp('2001-12-19');
NOTICE: QUERY PLAN:

Aggregate (cost=100012511.52..100012511.52 rows=1 width=0)
-> Seq Scan on traffic_stat (cost=100000000.00..100012399.96 rows=44622 width=0)

EXPLAIN

The question is why it doesn't make use of traffic_stat_idx2?

Second, traffic_stat_idx1 is used only when enable_seqscan is set to false:

isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and timestamp('2001-12-19') and customer_name='tu';
NOTICE: QUERY PLAN:

Aggregate (cost=25525.42..25525.42 rows=1 width=0)
-> Index Scan using traffic_stat_idx1 on traffic_stat (cost=0.00..25520.88 rows=1815 width=0)

EXPLAIN
isp_leased=# set enable_seqscan=1;
SET VARIABLE
isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and timestamp('2001-12-19') and customer_name='tu';
NOTICE: QUERY PLAN:

Aggregate (cost=13408.49..13408.49 rows=1 width=0)
-> Seq Scan on traffic_stat (cost=0.00..13403.95 rows=1815 width=0)

EXPLAIN
isp_leased=# select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and timestamp('2001-12-19') and customer_name='tu';
count
-------
5791
(1 row)

isp_leased=# select count(*) from traffic_stat;
count
--------
402952
(1 row)

I think cost estimation is wrong in this case. "vacuum analyze" was performed
shortly before the test. Postgresql version is 7.1.3.

--
Dmitry O Panov | mailto:dmitry(at)tsu(dot)tula(dot)ru
Tula State University | http://www.tsu.tula.ru/
Dept. of CS & NIT | Fidonet: Dmitry Panov, 2:5022/5.50