pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: tsuraan <tsuraan@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: group by will not use an index?
Date: Tue, 09 Jan 2007 17:42:04 -0600
Message-ID: <1168386124.20602.185.camel@state.g2switchworks.com> (raw)
In-Reply-To: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com>
References: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com>

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.



view thread (7+ messages)  latest in thread

Message-ID: <1168386124.20602.185.camel@state.g2switchworks.com>
Permalink:  ../1168386124.20602.185.camel@state.g2switchworks.com/
Also on:    postgresql.org/message-id/1168386124.20602.185.camel@state.g2switchworks.com

 · 

reply
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: smarlowe@g2switchworks.com, tsuraan@gmail.com
  Subject: Re: group by will not use an index?
  In-Reply-To: <1168386124.20602.185.camel@state.g2switchworks.com>

* 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