-----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Igor Neyman Sent: Friday, June 19, 2015 11:07 AM To: Ian Pushee; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: Slow query (planner insisting on using 'external merge' sort type) -----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Ian Pushee Sent: Friday, June 19, 2015 10:54 AM To: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
