Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5 - Mailing list pgsql-general

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=0loops=1) 
         ->  Nested Loop  (cost=3218962.89..3343428.74 rows=46513 width=4) (actual time=123465.298..126884.739
rows=162974loops=1) 
               ->  Hash Join  (cost=3218962.80..3323993.25 rows=46513 width=4) (actual time=123465.275..126323.155
rows=162974loops=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.937rows=11182962 loops=1) 
                     ->  Hash  (cost=3217108.89..3217108.89 rows=529689 width=4) (actual time=123134.119..123134.119
rows=595652loops=1) 
                           Buckets: 1048576  Batches: 1  Memory Usage: 29133kB
                           ->  Seq Scan on tblpuorderstatushistory osh  (cost=0.00..3217108.89 rows=529689 width=4)
(actualtime=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.003rows=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@1006.org]
Sent: Tuesday, January 17, 2017 12:39 PM
To: Ravi Tammineni <rtammineni@partner.aligntech.com>
Cc: pgsql-general@postgresql.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.