Re: [PERFORM] Techniques to Avoid Temp Files

On Thu, Jun 18, 2015 at 12:38 PM, Duane Murphy wrote: > We are trying to improve performance by avoiding the temp file creation. > > LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8068.125071", size > 58988604 > STATEMENT: SELECT iiid.installed_item__id, item_detail.id, > item_detail.model_i

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

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@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 'ex

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

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@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'

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

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 the

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 I

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

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@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' sor

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

> 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 ses

[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

Re: [PERFORM] Techniques to Avoid Temp Files

Duane Murphy wrote: > We are trying to improve performance by avoiding the temp file creation. > > LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8068.125071", size > 58988604 > STATEMENT: SELECT iiid.installed_item__id, item_detail.id, > item_detail.model_id, item_detail.type > FROM instal