group by will not use an index?

Поиск
Список
Период
Сортировка
От tsuraan
Тема group by will not use an index?
Дата
Msg-id 84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com
обсуждение исходный текст
Ответы Re: group by will not use an index?  ("Adam Rich" <adam.r@sbcglobal.net>)
Re: group by will not use an index?  (Scott Marlowe <smarlowe@g2switchworks.com>)
Список pgsql-performance
I have a table of messages with paths and inserted dates (among other things), like so:

CREATE TABLE Messages (
    msgkey BIGSERIAL PRIMARY KEY,
    path TEXT NOT NULL,
    inserted TIMESTAMP WITHOUT TIMEZONE DEFAULT NOW()
);

I run a query to determine which days actually saw emails come in, like so:

SELECT DATE(inserted) FROM Messages GROUP BY DATE(inserted);

That's obviously not very efficient, so I made an index:

CREATE INDEX messages_date_inserted_ind ON Messages(DATE(inserted));

However, GROUP BY does not use this index:

=# explain analyze select date(inserted) from messages group by date(inserted);
                                                          QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=104773.10..104789.51 rows=1313 width=8) (actual time=31269.476..31269.557 rows=44 loops=1)
   ->  Seq Scan on messages  (cost=0.00..101107.25 rows=1466340 width=8) (actual time=23.923..25248.400 rows=1467036 loops=1)
 Total runtime: 31269.735 ms
(3 rows)


Is it possible to get pg to use an index in a group by?  I don't see why it wouldn't be possible, but maybe I'm missing something.

Using pg 8.1.4...

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Jeremy Haile"
Дата:
Сообщение: Re: High inserts, bulk deletes - autovacuum vs scheduled
Следующее
От: "Adam Rich"
Дата:
Сообщение: Re: group by will not use an index?