Subject: Re: group by will not use an index?
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: tsuraan <tsuraan@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
In-Reply-To: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com>
References: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1168386124.20602.185.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Tue, 09 Jan 2007 17:42:04 -0600

On Tue, 2007-01-09 at 17:05, tsuraan wrote:
> 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);

You're probably under the mistaken impression that PostgreSQL and can
retrieve all the data it needs from the index alone.  It can't.  Anytime
postgresql gets an index reference, it has to then visit the actual
table file to grab the individual entry.  That's because indexes don't
store mvcc visibility information, and due to the problems locking both
indexes and tables together would present, probably won't any time soon.