From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Ravi Tammineni <rtammineni@partner.aligntech.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
To: pgadmin-support@postgresql.org <pgadmin-support@postgresql.org>
Subject: Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5
Date: Tue, 17 Jan 2017 11:42:20 -0800
Message-ID: <83d13711-6431-d944-ce42-6e73bc649bf2@aklaver.com> (raw)
In-Reply-To: <MWHPR02MB2655591A4E27B425FABD5E93807C0@MWHPR02MB2655.namprd02.prod.outlook.com>
References: <MWHPR02MB2655591A4E27B425FABD5E93807C0@MWHPR02MB2655.namprd02.prod.outlook.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 01/17/2017 11:31 AM, Ravi Tammineni wrote:
> Hi,
>
>
>
> We have recently upgraded to 9.6 and few queries are performing very
> poorly. Query execution time has increased more 4 or 5 times in 9.6.
> Qeruy execution plan is also completely changed. I am not sure whether
> its because of Parallel feature or any bug in 9.6. There are few similar
> kind of queries and all of them are taking more time in 9.6.
How did you upgrade from 9.5 --> 9.6?
Did you run ANALYZE on the tables after the upgrade?
Tend to doubt parallel query is at issue here, as it is turned off by
default:
https://www.postgresql.org/docs/9.6/static/runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER...
For more info:
https://www.postgresql.org/docs/9.6/static/parallel-query.html>
>
>
> Following query is taking 70ms in 9.5 and the same query is taking 2
> minutes in 9.6. Execution plan is totally different in 9.6 and seems
> like there is a major flaw while generating the execution plan. Instead
> of filtering the low cardinality, its filtering from biggest table.
> Somehow nested loop joins are screwed up.
>
>
>
>
>
> I really appreciate your help.
>
>
>
> explain analyze
>
> SELECT count(*)
>
> FROM
>
> tblCnPatientOrderMap po
>
> JOIN tblPuOrderStatus os ON po.vip_order_id = os.vip_order_id
>
> JOIN tblPuOrderStatusHistory osh ON os.order_status_history_id =
> osh.order_status_history_id
>
> WHERE
>
> exists(SELECT 1
>
> FROM
>
> tblCnDoctorPatientMap d
>
> WHERE d.vip_patient_id = po.vip_patient_id
>
> AND exists(SELECT 1
>
> FROM
>
> tblCnAccounts a
>
> WHERE a.master_user_id = d.master_user_id AND
>
> a.user_name = 'dddddd'))
>
> AND osh.vip_order_type IN (17)--assist
>
> --AND osh.tx_submit_date IS NOT NULL
>
> AND osh.cancelled_date IS NULL
>
> AND osh.cc_accept_date IS NOT NULL;
>
>
>
>
>
> ########################## 9.5 Execution plan
>
>
>
>
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------
>
> Aggregate (cost=1177.25..1177.26 rows=1 width=0)
>
> -> Nested Loop (cost=67.83..1177.25 rows=5 width=0)
>
> -> Nested Loop (cost=67.71..254.81 rows=1322 width=4)
>
> -> Nested Loop (cost=67.63..104.45 rows=1322 width=4)
>
> -> HashAggregate (cost=67.54..68.12 rows=192 width=4)
>
> Group Key: d.vip_patient_id
>
>
>
> -> Nested Loop (cost=0.17..67.44 rows=192
> width=4)
>
> -> Index Scan using unq_user_name on
> tblcnaccounts a (cost=0.08..4.09 rows=1 width=4)
>
> Index Cond: ((user_name)::text =
> 'dddddd'::text)
>
>
>
> -> Index Only Scan using
> idx_tblcndoctorpatientmap on tblcndoctorpatientmap d (cost=0.09..62.78
> rows=192 width=8)
>
> Index Cond: (master_user_id =
> a.master_user_id)
>
> -> Index Scan using idx_tblcnpatientordermap on
> tblcnpatientordermap po (cost=0.09..0.17 rows=7 width=8)
>
> Index Cond: (vip_patient_id = d.vip_patient_id)
>
> -> Index Scan using tblpuorderstatus_pkey on
> tblpuorderstatus os (cost=0.09..0.11 rows=1 width=8)
>
> Index Cond: (vip_order_id = po.vip_order_id)
>
> -> Index Scan using tblpuorderstatushistory_pkey on
> tblpuorderstatushistory osh (cost=0.11..0.69 rows=1 width=4)
>
> Index Cond: (order_status_history_id =
> os.order_status_history_id)
>
> Filter: ((cancelled_date IS NULL) AND (cc_accept_date IS
> NOT NULL) AND (vip_order_type = 17))
>
>
>
> #####################################################################################################################################
>
>
>
> ########################## 9.6 Execution plan
>
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------------------------------------
>
> Aggregate (cost=3185034.17..3185034.17 rows=1 width=8)
>
> -> Nested Loop Semi Join (cost=3012833.92..3185010.91 rows=46511
> width=0)
>
>
>
> -> Nested Loop (cost=3012833.75..3137291.51 rows=46511 width=4)
>
>
>
> -> Hash Join (cost=3012833.67..3117860.77 rows=46511
> width=4)
>
> Hash Cond: (os.order_status_history_id =
> osh.order_status_history_id)
>
>
>
> -> Seq Scan on tblpuorderstatus os
> (cost=0.00..96498.46 rows=11185486 width=8)
>
> -> Hash (cost=3010979.77..3010979.77 rows=529686
> width=4)
>
> -> Gather (cost=1000.00..3010979.77
> rows=529686 width=4)
>
> Workers Planned: 2
>
> -> Parallel Seq Scan on
> tblpuorderstatushistory osh (cost=0.00..2957011.17 rows=220702 width=4)
>
>
>
> Filter: ((cancelled_date IS NULL)
> AND (cc_accept_date IS NOT NULL) AND (vip_order_type = 17))
>
>
>
> -> Index Scan using tblcnpatientordermap_pkey on
> tblcnpatientordermap po (cost=0.09..0.41 rows=1 width=8)
>
>
>
> Index Cond: (vip_order_id = os.vip_order_id)
>
>
>
> -> Nested Loop Semi Join (cost=0.17..1.02 rows=1 width=4)
>
> -> Index Scan using tblcndoctorpatientmap_pkey on
> tblcndoctorpatientmap d (cost=0.09..0.39 rows=1 width=8)
>
> 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)
>
> Index Cond: (master_user_id = d.master_user_id)
>
> Filter: ((user_name)::text = 'dddddd'::text)
>
> (19 rows)
>
>
>
>
>
> Regards,
>
> ravi
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: adrian.klaver@aklaver.com, rtammineni@partner.aligntech.com, pgsql-general@postgresql.org, pgadmin-support@postgresql.org
Subject: Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5
In-Reply-To: <83d13711-6431-d944-ce42-6e73bc649bf2@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox