Thread: Slow query (planner insisting on using 'external merge' sort type)
Hi Folks, This is my first time posting here, so hopefully I manage to convey all the information needed. We have a simple query that just started giving us problems in production when the number of rows gets too large (>100k). The issue seems to be that the planner wants to sort the rows using a sequential scan, rather than the index provided specifically for this query. This isn't a problem with low numbers of rows, but eventually the query outgrows work_mem and uses the disk, slowing does the query greatly. I know the common answer is to increase work_mem... but since this tables growth is unpredictable, that isn't a viable strategy. I've tried increasing shared_buffers and effective_cache_size, but that doesn't appear to effect the plan chosen here. Setting random_page_cost=1.0 works, but I'm hoping for a more general solution that doesn't require setting that locally each time I run the query. I guess my real question is wether or not there is any way to get the planner to take into account the fact that it's going to need to do an 'external merge', and that it is going to take a LONG time? Table and Index Schemas: CREATE TABLE events ( id serial NOT NULL, name character varying(64), eventspy_id integer NOT NULL, camera_id integer NOT NULL, start_time timestamp without time zone NOT NULL, millisecond smallint NOT NULL, uid smallint NOT NULL, update_time timestamp without time zone NOT NULL DEFAULT now(), length integer NOT NULL, objects text NOT NULL, priority smallint NOT NULL, type character varying(45) NOT NULL DEFAULT 'alarm'::character varying, status event_status NOT NULL DEFAULT 'new'::event_status, confidence smallint NOT NULL DEFAULT 100::smallint, CONSTRAINT events_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); CREATE INDEX events_confidnce ON events USING btree (confidence); CREATE INDEX events_summary ON events USING btree (name COLLATE pg_catalog."default", eventspy_id, camera_id, type COLLATE pg_catalog."default", status); Query: SELECT name, type, eventspy_id, camera_id, status, COUNT(id), MAX(update_time), MIN(start_time), MAX(start_time) FROM events WHERE confidence>=0 GROUP BY name, eventspy_id, camera_id, type, status; Explain Analyze outputs (links as requested): Default plan: http://explain.depesz.com/s/ib3k Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP Software/Hardware: PGSql 9.2.1, Windows 8.1, 8GB RAM All pgsql settings are at their defaults. Thanks for any help you can provide, -Ian Pushee
-----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Ian Pushee Sent: Friday, June 19, 2015 10:34 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Slow query (planner insisting on using 'external merge' sort type) Hi Folks, This is my first time posting here, so hopefully I manage to convey all the information needed. We have a simple query that just started giving us problems in production when the number of rows gets too large (>100k). The issue seems to be that the planner wants to sort the rows using a sequential scan, rather than the index provided specificallyfor this query. This isn't a problem with low numbers of rows, but eventually the query outgrows work_mem anduses the disk, slowing does the query greatly. I know the common answer is to increase work_mem... but since this tablesgrowth is unpredictable, that isn't a viable strategy. I've tried increasing shared_buffers and effective_cache_size, but that doesn't appear to effect the plan chosen here. Setting random_page_cost=1.0 works, but I'm hoping for a more general solution that doesn't require setting that locally each timeI run the query. I guess my real question is wether or not there is any way to get the planner to take into account thefact that it's going to need to do an 'external merge', and that it is going to take a LONG time? Table and Index Schemas: CREATE TABLE events ( id serial NOT NULL, name character varying(64), eventspy_id integer NOT NULL, camera_id integer NOT NULL, start_time timestamp without time zone NOT NULL, millisecond smallint NOT NULL, uid smallint NOT NULL, update_time timestamp without time zone NOT NULL DEFAULT now(), length integer NOT NULL, objects text NOT NULL, priority smallint NOT NULL, type character varying(45) NOT NULL DEFAULT 'alarm'::character varying, status event_status NOT NULL DEFAULT 'new'::event_status, confidence smallint NOT NULL DEFAULT 100::smallint, CONSTRAINT events_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); CREATE INDEX events_confidnce ON events USING btree (confidence); CREATE INDEX events_summary ON events USING btree (name COLLATE pg_catalog."default", eventspy_id, camera_id, type COLLATE pg_catalog."default", status); Query: SELECT name, type, eventspy_id, camera_id, status, COUNT(id), MAX(update_time), MIN(start_time), MAX(start_time) FROM eventsWHERE confidence>=0 GROUP BY name, eventspy_id, camera_id, type, status; Explain Analyze outputs (links as requested): Default plan: http://explain.depesz.com/s/ib3k Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP Software/Hardware: PGSql 9.2.1, Windows 8.1, 8GB RAM All pgsql settings are at their defaults. Thanks for any help you can provide, -Ian Pushee --- 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
Re: Slow query (planner insisting on using 'external merge' sort type)
From
Andreas Kretschmer
Date:
> Explain Analyze outputs (links as requested): > Default plan: http://explain.depesz.com/s/ib3k > Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP > > Software/Hardware: PGSql 9.2.1, Windows 8.1, 8GB RAM > All pgsql settings are at their defaults. increase work_mem. per session via set work_mem = 'xxxMB'; or in postgresql.conf, reload.
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
On 6/19/2015 10:47 AM, Andreas Kretschmer wrote: >> Explain Analyze outputs (links as requested): >> Default plan: http://explain.depesz.com/s/ib3k >> Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP >> >> Software/Hardware: PGSql 9.2.1, Windows 8.1, 8GB RAM >> All pgsql settings are at their defaults. > increase work_mem. per session via set work_mem = 'xxxMB'; or in > postgresql.conf, reload. > > Hi Andreas, The number of rows in the events table isn't constrained, so unfortunately it isn't feasible to set work_mem high enough to allow an in-memory sort. Forcing the planner to use the index works to produce a fast query, so I'm wondering if there is a more general way to getting the planner to take into account that work_mem isn't big enough to fit the query which will result in a MUCH more costly external merge. Thanks, -Ian
-----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Ian Pushee Sent: Friday, June 19, 2015 10:54 AM To: pgsql-performance@postgresql.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 definitelymodify 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
-----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Igor Neyman Sent: Friday, June 19, 2015 11:07 AM To: Ian Pushee; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type) -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Ian Pushee Sent: Friday, June 19, 2015 10:54 AM To: pgsql-performance@postgresql.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 definitelymodify 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
On 6/19/15 9:57 AM, Ian Pushee wrote: > > > On 6/19/2015 10:47 AM, Andreas Kretschmer wrote: >>> Explain Analyze outputs (links as requested): >>> Default plan: http://explain.depesz.com/s/ib3k >>> Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP >>> >>> Software/Hardware: PGSql 9.2.1, Windows 8.1, 8GB RAM >>> All pgsql settings are at their defaults. >> increase work_mem. per session via set work_mem = 'xxxMB'; or in >> postgresql.conf, reload. >> >> > > Hi Andreas, > > The number of rows in the events table isn't constrained, so > unfortunately it isn't feasible to set work_mem high enough to allow an > in-memory sort. Forcing the planner to use the index works to produce a > fast query, so I'm wondering if there is a more general way to getting > the planner to take into account that work_mem isn't big enough to fit > the query which will result in a MUCH more costly external merge. What Andreas is saying is the reason the sort is so expensive is because it spilled to disk. If you don't have enough memory to do the sort in-memory, then you probably don't have enough memory to buffer the table either, which means the index scan is going to be a LOT more expensive than a sort. That said, the better your IO system is the lower you need to set random_page_cost. With a good raid setup 2.0 is a good starting point, and I've run as low as 1.1. I've never run a system on all SSD, but I've heard others recommend setting it as low as 1.0 on an all SSD setup. It's also worth noting that there's some consensus that the optimizer is generally too eager to switch from an index scan to a seqscan. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.com
On 24/06/15 09:05, Jim Nasby wrote: > On 6/19/15 9:57 AM, Ian Pushee wrote: >> >> >> On 6/19/2015 10:47 AM, Andreas Kretschmer wrote: >>>> Explain Analyze outputs (links as requested): >>>> Default plan: http://explain.depesz.com/s/ib3k >>>> Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP >>>> >>>> Software/Hardware: PGSql 9.2.1, Windows 8.1, 8GB RAM >>>> All pgsql settings are at their defaults. >>> increase work_mem. per session via set work_mem = 'xxxMB'; or in >>> postgresql.conf, reload. >>> >>> >> >> Hi Andreas, >> >> The number of rows in the events table isn't constrained, so >> unfortunately it isn't feasible to set work_mem high enough to allow an >> in-memory sort. Forcing the planner to use the index works to produce a >> fast query, so I'm wondering if there is a more general way to getting >> the planner to take into account that work_mem isn't big enough to fit >> the query which will result in a MUCH more costly external merge. > > What Andreas is saying is the reason the sort is so expensive is because > it spilled to disk. If you don't have enough memory to do the sort > in-memory, then you probably don't have enough memory to buffer the > table either, which means the index scan is going to be a LOT more > expensive than a sort. > > That said, the better your IO system is the lower you need to set > random_page_cost. With a good raid setup 2.0 is a good starting point, > and I've run as low as 1.1. I've never run a system on all SSD, but I've > heard others recommend setting it as low as 1.0 on an all SSD setup. > > It's also worth noting that there's some consensus that the optimizer is > generally too eager to switch from an index scan to a seqscan. Mind you, this eagerness could be caused by the OP having effective_cache_size set to the default. This should be changed (set to a few GB...)! Cheers Mark