From: | Victor Yegorov <vyegorov(at)gmail(dot)com> |
---|---|
To: | gmb <gmbouwer(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Performance issue: index not used on GROUP BY... |
Date: | 2014-08-28 09:28:07 |
Message-ID: | CAGnEbohHuV0B7d1Yfdt-Cq=oO+Jb5ZEQQT+30bdGeRhnJy-+cQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2014-08-28 12:08 GMT+03:00 gmb <gmbouwer(at)gmail(dot)com>:
> 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 ;
>
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.
Try to avoid disk-based sort by increasing `work_mem` for your session, I
think value in the range 120MB-150MB should work:
SET work_mem TO '150MB';
Check `EXPLAIN` output after the change.
--
Victor Y. Yegorov
From | Date | Subject | |
---|---|---|---|
Next Message | Marti Raudsepp | 2014-08-28 09:29:46 | Re: Performance issue: index not used on GROUP BY... |
Previous Message | gmb | 2014-08-28 09:08:59 | Re: Performance issue: index not used on GROUP BY... |