Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XMvhe-0007Ev-5w for pgsql-performance@arkaria.postgresql.org; Thu, 28 Aug 2014 09:09:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XMvhd-0001CZ-LY for pgsql-performance@arkaria.postgresql.org; Thu, 28 Aug 2014 09:09:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XMvhc-0001CQ-It for pgsql-performance@postgresql.org; Thu, 28 Aug 2014 09:09:08 +0000 Received: from sam.nabble.com ([216.139.236.26]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XMvhX-0007qC-KO for pgsql-performance@postgresql.org; Thu, 28 Aug 2014 09:09:07 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1XMvhT-0007Oj-T7 for pgsql-performance@postgresql.org; Thu, 28 Aug 2014 02:08:59 -0700 Date: Thu, 28 Aug 2014 02:08:59 -0700 (PDT) From: gmb To: pgsql-performance@postgresql.org Message-ID: <1409216939896-5816706.post@n5.nabble.com> In-Reply-To: References: <1409215840535-5816702.post@n5.nabble.com> Subject: Re: Performance issue: index not used on GROUP BY... MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org > Can you send `EXPLAIN (analyze, buffers)` for your query instead? > It'll show exactly what's going on. GroupAggregate (cost=303425.31..339014.43 rows=136882 width=48) (actual time=4708.181..6688.699 rows=287268 loops=1) Buffers: shared read=23899, temp read=30974 written=30974 -> Sort (cost=303425.31..306847.34 rows=1368812 width=48) (actual time=4708.170..5319.429 rows=1368744 loops=1) Sort Key: co_id, client_id, doc_no, Sort Method: external merge Disk: 80304kB Buffers: shared read=23899, temp read=30974 written=30974 -> Seq Scan on ddetail (cost=0.00..37587.12 rows=1368812 width=48) (actual time=0.122..492.964 rows=1368744 loops=1) Buffers: shared read=23899 Total runtime: 6708.244 ms My initial attempt was this (this is what I actually need): SELECT co_id , client_id , doc_no , line_id , batch_no , sum( amount ) FROM ddetail GROUP BY co_id , client_id , doc_no , line_id , batch_no ; but I removed column batch_no from the query because I thought this was the cause of the problem ( batch_no is not part of my PK ). Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Performance-issue-index-not-used-on-GROUP-BY-tp5816702p5816706.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