That query looks strange to me (a group by without an aggregate). See if this is
any faster:
SELECT DISTINCT DATE(inserted) FROM Messages
I won't hold my breath though, I don't think there's any way around the full table scan
in Postgres, because the index does not contain enough information about transactional
state, so table access is always required (unlike virtually every other type of db)
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...