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?
Re: group by will not use an index? |
Список | 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...
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 по дате отправления: