From: gmb <gmbouwer@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance issue: index not used on GROUP BY...
Date: Thu, 28 Aug 2014 04:29:19 -0700 (PDT)
Message-ID: <1409225359977-5816715.post@n5.nabble.com> (raw)
In-Reply-To: <CAGnEbohHuV0B7d1Yfdt-Cq=oO+Jb5ZEQQT+30bdGeRhnJy-+cQ@mail.gmail.com>
References: <1409215840535-5816702.post@n5.nabble.com>
<CAGnEbogEvqqdwGA_jrrdt_8S3dMvvSNdT=sna8jC87nRmAW+cA@mail.gmail.com>
<1409216939896-5816706.post@n5.nabble.com>
<CAGnEbohHuV0B7d1Yfdt-Cq=oO+Jb5ZEQQT+30bdGeRhnJy-+cQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Thanks for these suggestions
Unfortunately , I don't have a lot of memory available ( 65 connections ,
work_mem = 64MB in pg conf ).
>> I think index will be of no help here, as (1) you're reading whole table
>> anyway and (2) `amount` is not part of your index.
I did not think that the the field being used in the agg function should
also be part of the index.
I'll try this and check the result.
My problem is that dropping / adding indexes on this table takes a LOT of
time, so I'm stuck with doing the tests using the indexes as is, or doing
the tests on a smaller dataset.
On the smaller dataset ( 1.5 mill records on that table ) the planner did
not take the index into account, even when I omit the amount column:
CREATE INDEX ix_1
ON ddetail
USING btree
(co_id , client_id , doc_no , line_id , batch_no);
SELECT co_id , client_id , doc_no , line_id , batch_no
FROM ddetail
GROUP BY co_id , client_id , doc_no , line_id , batch_no ;
HashAggregate (cost=54695.74..56064.49 rows=136875 width=22)
-> Seq Scan on debfdetail (cost=0.00..37586.44 rows=1368744 width=22)
still does a seq scan instead of the index scan.
I guess it is possible that on the 1.4 million records, it is faster to do a
seq scan ?
So I guess I'll have to try and do this on the 10 mill table and check the
result there.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Performance-issue-index-not-used-on-GROUP-BY-tp5816702p58167...
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: <1409225359977-5816715.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