Re: Slow query (planner insisting on using 'external merge' sort type)

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Igor Neyman
Sent: Friday, June 19, 2015 11:07 AM
To: Ian Pushee; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Ian Pushee
Sent: Friday, June 19, 2015 10:54 AM
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

On 6/19/2015 10:46 AM, Igor Neyman wrote:
>
> Probably events_confidnce index is not very selective, that's why optimizer prefers seq scan.
> I'd try to create an index on (name, eventspy_id, camera_id, type, status).
>
> Also, the recent 9.2 is 9.2.13, you should upgrade.
>
> Regards,
> Igor Neyman

Hi Igor,

I already have an index for (name, eventspy_id, camera_id, type, status)... that is the index being used (apparently silently) when I set random_page_cost=1.0.

Thanks,
-Ian

--

Well, having 8GB Ram on the machine you probably should not be using default config parameters.
Depending on what else is this machine is being used for, and depending on queries you are running, you should definitely modify Postgres config.
If this machine is designated database server, I'd start with the following parameters modified from default values:

shared_buffers = 1024MB
temp_buffers = 8MB
work_mem = 64MB
effective_cache_size = 1024MB
random_page_cost = 2.5
cpu_tuple_cost = 0.03
cpu_index_tuple_cost = 0.05

and see how it goes.

Regards,
Igor Neyman

---

Oops, should be at least:

effective_cache_size = 5120MB

on dedicated server.

Regards,
Igor Neyman