Re: Performance issue: index not used on GROUP BY...

On Thu, Aug 28, 2014 at 11:50 AM, gmb  wrote:
> Can somebody please confirm whether aggregate functions such as GROUP BY
> should use indexes ?

Yes, if the planner deems it faster than other approaches. It can make
wrong choices for many reasons, but usually when your planner tunables
like random_page_cost, effective_cache_size aren't set appropriately.

There's some advice here:
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Just for the purpose of testing, you could try "set enable_sort=false"
in your session and see if that makes it faster.

On Thu, Aug 28, 2014 at 12:08 PM, gmb  wrote:
>         Sort Key: co_id, client_id, doc_no,

Something went missing from this line...

>         Sort Method: external merge  Disk: 80304kB

Depends on your hardware and workloads, but more work_mem may also
improve queries to avoid sorts and hashes needing to use disk. But
beware, setting it too high may result in your server running out of
memory.

Regards,
Marti