From: Adam Rich <adam.r@sbcglobal.net>
To: 'tsuraan' <tsuraan@gmail.com>
To: 'pgsql-performance' <pgsql-performance@postgresql.org>
Subject: Re: group by will not use an index?
Date: Tue, 9 Jan 2007 17:32:50 -0600
Message-ID: <031b01c73446$7a4250f0$6400a8c0@dualcore> (raw)
In-Reply-To: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com>
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)
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of tsuraan
Sent: Tuesday, January 09, 2007 5:06 PM
To: pgsql-performance
Subject: [PERFORM] group by will not use an index?
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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adam.r@sbcglobal.net, tsuraan@gmail.com
Subject: Re: group by will not use an index?
In-Reply-To: <031b01c73446$7a4250f0$6400a8c0@dualcore>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox