From: gmb <gmbouwer@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Performance issue: index not used on GROUP BY...
Date: Thu, 28 Aug 2014 01:50:40 -0700 (PDT)
Message-ID: <1409215840535-5816702.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: gmbouwer@gmail.com
Subject: Re: Performance issue: index not used on GROUP BY...
In-Reply-To: <1409215840535-5816702.post@n5.nabble.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