Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 760D69FB231
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  9 Jan 2007 19:05:56 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 18958-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  9 Jan 2007 19:05:46 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.243])
	by postgresql.org (Postfix) with ESMTP id 364129FB204
	for <pgsql-performance@postgresql.org>;
	Tue,  9 Jan 2007 19:05:50 -0400 (AST)
Received: by an-out-0708.google.com with SMTP id c24so2385435ana
	for <pgsql-performance@postgresql.org>;
	Tue, 09 Jan 2007 15:05:49 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=E1yot/khsOsS67GgNZpwkdbOGC1zznvdqi+kRvW3USV/Edd9cBCjOHG2G0tYFZZhUV9jigPV7hRNEpIFhLQFczHE8fC23AnpUqlHTrT/X953GlCS7/eS5Mwp9RhpJ1MlF635QNzB+VkSb7+YU3RbxpHWcEZ5TFrfGtBPGgYVUic=
Received: by 10.78.18.3 with SMTP id 3mr4543584hur.1168383948227;
	Tue, 09 Jan 2007 15:05:48 -0800 (PST)
Received: by 10.78.179.8 with HTTP; Tue, 9 Jan 2007 15:05:48 -0800 (PST)
Message-ID: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com>
Date: Tue, 9 Jan 2007 17:05:48 -0600
From: tsuraan <tsuraan@gmail.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: group by will not use an index?
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_89136_10499068.1168383948131"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/121
X-Sequence-Number: 22722

------=_Part_89136_10499068.1168383948131
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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...

------=_Part_89136_10499068.1168383948131
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I have a table of messages with paths and inserted dates (among other things), like so:<br><br>CREATE TABLE Messages (<br>&nbsp;&nbsp;&nbsp; msgkey BIGSERIAL PRIMARY KEY,<br>&nbsp;&nbsp;&nbsp; path TEXT NOT NULL,<br>&nbsp;&nbsp;&nbsp; inserted TIMESTAMP WITHOUT TIMEZONE DEFAULT NOW()
<br>);<br><br>I run a query to determine which days actually saw emails come in, like so:<br><br>SELECT DATE(inserted) FROM Messages GROUP BY DATE(inserted);<br><br>That&#39;s obviously not very efficient, so I made an index:
<br><br>CREATE INDEX messages_date_inserted_ind ON Messages(DATE(inserted));<br><br>However, GROUP BY does not use this index:<br><br>=# explain analyze select date(inserted) from messages group by date(inserted);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
<br>------------------------------------------------------------------------------------------------------------------------------<br>&nbsp;HashAggregate&nbsp; (cost=104773.10..104789.51 rows=1313 width=8) (actual time=31269.476..31269.557
 rows=44 loops=1)<br>&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on messages&nbsp; (cost=0.00..101107.25 rows=1466340 width=8) (actual time=23.923..25248.400 rows=1467036 loops=1)<br>&nbsp;Total runtime: 31269.735 ms<br>(3 rows)<br><br><br>Is it possible to get pg to use an index in a group by?&nbsp; I don&#39;t see why it wouldn&#39;t be possible, but maybe I&#39;m missing something.
<br><br>Using pg 8.1.4...<br>

------=_Part_89136_10499068.1168383948131--