Postgres Pro
Downloads
Home   >   mailing lists

Re: [PERFORM] query performance issue - Mailing list pgsql-performance

From Pavel Stehule
Subject Re: [PERFORM] query performance issue
Date
Msg-id CAFj8pRC6xh=HDq1jwxW8n1Ct8=FbbdqHcx_VFHNCdNUz41rTWQ@mail.gmail.com
Whole thread Raw
In response to Re: [PERFORM] query performance issue  (Samir Magar <samirmagar8@gmail.com>)
Responses Re: [PERFORM] query performance issue  (Gunther <raj@gusw.net>)
List pgsql-performance
Tree view


2017-11-15 13:54 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
please find the EXPLAIN ANALYZE output.

On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

please send EXPLAIN ANALYZE output.

Regards

Pavel

2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
Hello,
I am having performance issues with one of the query.
The query is taking 39 min to fetch 3.5 mil records.

I want to reduce that time to 15 mins. 
could you please suggest something to its performance?

server configuration:
 CPUs = 4
memory = 16 GM
shared_buffers = 3 GB
work_mem = 100MB
effective_cache_size = 12 GB

we are doing the vacuum/analyze regularly on the database. 

attached is the query with its explain plan.



There is wrong plan due wrong estimation

for this query you should to penalize nested loop

set enable_nestloop to off;

before evaluation of this query


Thanks,
Samir Magar  


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




pgsql-performance by date:

Previous
From: Samir Magar
Date:
Subject: Re: [PERFORM] query performance issue
Next
From: Justin Pryzby
Date:
Subject: Re: [PERFORM] query performance issue