Re: Queries are taking way longer in 9.6 than 9.5

Chris,

Here is the explain analyze plan.

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3391172.70..3391172.71 rows=1 width=8) (actual time=128011.913..128011.913 rows=1 loops=1)
-> Nested Loop Semi Join (cost=3218963.06..3391149.45 rows=46513 width=0) (actual time=128011.907..128011.907 rows=0 loops=1)
-> Nested Loop (cost=3218962.89..3343428.74 rows=46513 width=4) (actual time=123465.298..126884.739 rows=162974 loops=1)
-> Hash Join (cost=3218962.80..3323993.25 rows=46513 width=4) (actual time=123465.275..126323.155 rows=162974 loops=1)
Hash Cond: (os.order_status_history_id = osh.order_status_history_id)
-> Seq Scan on tblpuorderstatus os (cost=0.00..96501.53 rows=11185842 width=8) (actual time=0.011..822.937 rows=11182962 loops=1)
-> Hash (cost=3217108.89..3217108.89 rows=529689 width=4) (actual time=123134.119..123134.119 rows=595652 loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 29133kB
-> Seq Scan on tblpuorderstatushistory osh (cost=0.00..3217108.89 rows=529689 width=4) (actual time=0.026..122867.714 rows=595652 loops=1)
Filter: ((cancelled_date IS NULL) AND (cc_accept_date IS NOT NULL) AND (vip_order_type = 17))
Rows Removed by Filter: 126867211
-> Index Scan using tblcnpatientordermap_pkey on tblcnpatientordermap po (cost=0.09..0.41 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=162974)
Index Cond: (vip_order_id = os.vip_order_id)
-> Nested Loop Semi Join (cost=0.17..1.02 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=162974)
-> Index Scan using tblcndoctorpatientmap_pkey on tblcndoctorpatientmap d (cost=0.09..0.39 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=162974)
Index Cond: (vip_patient_id = po.vip_patient_id)
-> Index Scan using tblcnaccounts_pkey on tblcnaccounts a (cost=0.08..0.36 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=162974)
Index Cond: (master_user_id = d.master_user_id)
Filter: ((user_name)::text = 'rdoyleda'::text)
Rows Removed by Filter: 1

Thanks
ravi

-----Original Message-----
From: Chris Mair [mailto:chris(at)1006(dot)org]
Sent: Tuesday, January 17, 2017 12:39 PM
To: Ravi Tammineni <rtammineni(at)partner(dot)aligntech(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

Hi.

> explain analyze
>
> SELECT count(*)
>
> [...]

> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------
>
> Aggregate (cost=1177.25..1177.26 rows=1 width=0)
>
> [...]

I'm not seeing the "(actual ... )" part here.
THe plan you show is from an explain, not an explain analyze...

Can you provide the explain analyze output?

Bye,
Chris.