Re: query performance issue

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2017-11-15 13:54 GMT+01:00 Samir Magar <samirmagar8@xxxxxxxxx>:

please find the EXPLAIN ANALYZE output.

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

Hi

please send EXPLAIN ANALYZE output.

Regards

Pavel

2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@xxxxxxxxx>:

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