Обсуждение: 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@tsu.tula.ru
Tula State University  |  http://www.tsu.tula.ru/
Dept. of CS & NIT      |  Fidonet: Dmitry Panov, 2:5022/5.50

Dmitry Panov <dmitry@tsu.tula.ru> writes:
> isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and
timestamp('2001-12-19');

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

Try '2001-12-19'::timestamp, instead, or use CAST if you want to conform
to the standard.  The function syntax is not considered a constant for
reasons I won't delve into here.

> I think cost estimation is wrong in this case.

Could easy be.  Do you want to try it with 7.2 beta?

            regards, tom lane

On Thu, Dec 20, 2001 at 10:27:06AM -0500, Tom Lane wrote:
> Dmitry Panov <dmitry@tsu.tula.ru> writes:
> > isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and
timestamp('2001-12-19');
>
> > The question is why it doesn't make use of traffic_stat_idx2?
>
> Try '2001-12-19'::timestamp, instead, or use CAST if you want to conform
> to the standard.  The function syntax is not considered a constant for
> reasons I won't delve into here.

Thanks, this solved both problems.

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