Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 8147A9FB25A for ; Tue, 9 Jan 2007 19:31:58 -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 23883-03 for ; Tue, 9 Jan 2007 19:31:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtp101.sbc.mail.re2.yahoo.com (smtp101.sbc.mail.re2.yahoo.com [68.142.229.104]) by postgresql.org (Postfix) with SMTP id F2D7C9FB251 for ; Tue, 9 Jan 2007 19:31:47 -0400 (AST) Received: (qmail 9936 invoked from network); 9 Jan 2007 23:31:46 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=sbcglobal.net; h=Received:X-YMail-OSG:From:To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE:In-Reply-To:Importance; b=6uzEM11P3r6fAt/WYYnl+N7ghXzWNReHyp0NksVw8LOanO1/txf4QjwGvp0JZ6W8c6P1xOpTl8N1kgKFvD/bWhRcAH9iSAOvNYNPkOFfwGFlPBbQxxmTHSX1AA8WW4VzNVHFz7zNKjSbRFP2qUHG/JcTexZPl89UVWsKbQVsSVQ= ; Received: from unknown (HELO dualcore) (adam.r@sbcglobal.net@75.2.68.162 with login) by smtp101.sbc.mail.re2.yahoo.com with SMTP; 9 Jan 2007 23:31:46 -0000 X-YMail-OSG: FgZnoZQVM1kjdpnfaZyllSlp.eiTWK15q248IxRaxFUBSxbqPI1I5my__uYtC2VlrS0xj_2qURsQb4OzjlRqmoHPAlR9IpPwXxoHFhdMz6gw2qpfsDSHIBwwV6laI.iig5Q09AqLskKcRPhw8aMSbKDikzxsww5n7iHKh_ca2in3eSl4LxXaiGudKvBQ From: "Adam Rich" To: "'tsuraan'" , "'pgsql-performance'" Subject: Re: group by will not use an index? Date: Tue, 9 Jan 2007 17:32:50 -0600 Message-ID: <031b01c73446$7a4250f0$6400a8c0@dualcore> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_031C_01C73414.2FA7E0F0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826 In-Reply-To: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com> Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.724 tagged_above=0 required=5 tests=AWL, BAYES_20, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, HTML_50_60, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200701/122 X-Sequence-Number: 22723 This is a multi-part message in MIME format. ------=_NextPart_000_031C_01C73414.2FA7E0F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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... ------=_NextPart_000_031C_01C73414.2FA7E0F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
That=20 query looks strange to me (a group by without an aggregate).  See = if this=20 is
any=20 faster:
 
SELECT=20 DISTINCT DATE(inserted) FROM Messages
 
I=20 won't hold my breath though, I don't think there's any way around the = full table=20 scan
in=20 Postgres, because the index does not contain enough information about=20 transactional
state,=20 so table access is always required (unlike virtually every other type of = db)
 
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20 tsuraan
Sent: Tuesday, January 09, 2007 5:06 = PM
To:=20 pgsql-performance
Subject: [PERFORM] group by will not use = an=20 index?

I have a table of messages with paths and = inserted=20 dates (among other things), like so:

CREATE TABLE Messages=20 (
    msgkey BIGSERIAL PRIMARY = KEY,
   =20 path TEXT NOT NULL,
    inserted TIMESTAMP WITHOUT = TIMEZONE=20 DEFAULT NOW()
);

I run a query to determine which days = actually saw=20 emails come in, like so:

SELECT DATE(inserted) FROM Messages = GROUP BY=20 DATE(inserted);

That's obviously not very efficient, so I made = an=20 index:

CREATE INDEX messages_date_inserted_ind ON=20 Messages(DATE(inserted));

However, GROUP BY does not use this=20 index:

=3D# explain analyze select date(inserted) from messages = group by=20 = date(inserted);
         =             &= nbsp;           &n= bsp;           &nb= sp;           =20 QUERY=20 = PLAN           &nb= sp;           &nbs= p;            = ;            =          =20 =
---------------------------------------------------------------------= ---------------------------------------------------------
 HashAg= gregate =20 (cost=3D104773.10..104789.51 rows=3D1313 width=3D8) (actual=20 time=3D31269.476..31269.557 rows=3D44 loops=3D1)
   = ->  Seq=20 Scan on messages  (cost=3D0.00..101107.25 rows=3D1466340 = width=3D8) (actual=20 time=3D23.923..25248.400 rows=3D1467036 loops=3D1)
 Total = runtime: 31269.735=20 ms
(3 rows)


Is it possible to get pg to use an index in = a group=20 by?  I don't see why it wouldn't be possible, but maybe I'm = missing=20 something.

Using pg 8.1.4...
------=_NextPart_000_031C_01C73414.2FA7E0F0--