Обсуждение: Why my index does not increase performance?

Hi, All!

My database has a big table (more 1 million records) that contains
a date field. This field has approximately 150 unique values (dates
of five monthes) per table. I created index as follow:

    create index mytable_ind on mytable (mydate date_ops);

Next, I perform following query:

    select max(mydate) from mytable;

And time length of this query is about 100 seconds! I think postgres
looks up ALL records in the table to find max value and does not use
created index. Why? Database is vacuumed, PostgreSQL 6.3.2 on Solaris
2.5.1/x86, CPU P166, RAM 64 Mb.

Yuri.

> Hi, All!
>
> My database has a big table (more 1 million records) that contains
> a date field. This field has approximately 150 unique values (dates
> of five monthes) per table. I created index as follow:
>
>     create index mytable_ind on mytable (mydate date_ops);
>
> Next, I perform following query:
>
>     select max(mydate) from mytable;
>
> And time length of this query is about 100 seconds! I think postgres
> looks up ALL records in the table to find max value and does not use
> created index. Why? Database is vacuumed, PostgreSQL 6.3.2 on Solaris
> 2.5.1/x86, CPU P166, RAM 64 Mb.

It is on our TODO list.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026