Date: Thu, 28 Aug 2014 01:50:40 -0700 (PDT)
From: gmb <gmbouwer@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1409215840535-5816702.post@n5.nabble.com>
Subject: Performance issue: index not used on GROUP BY...
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi all

I have the following table with 10+ million records:

create table ddetail (
ddet_id serial,
co_id integer,
client_id integer,
doc_no varchar,
line_id integer,
batch_no integer,
amount NUMERIC , 
...,
constraint PRIMAR KEY (  co_id ,  client_id , doc_no ,  line_id, ddet_id )
) ;

When doing the following query on this table, performance is really slow:

SELECT  co_id ,  client_id , doc_no ,  line_id , sum( amount  )
FROM ddetail 
GROUP BY co_id ,  client_id , doc_no ,  line_id 

It seems as if the planner is not using the PRIMARY KEY as index which was
my assumption.

Can somebody please confirm whether aggregate functions such as GROUP BY
should use indexes ? 


Thanks in advance

gmb 


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Performance-issue-index-not-used-on-GROUP-BY-tp5816702.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance