pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!
Date: Wed, 23 May 2018 06:39:21 -0700 (MST)
Message-ID: <1527082761283-0.post@n3.nabble.com> (raw )
In-Reply-To: <1527073930538-0.post@n3.nabble.com >
References: <1526883356469-0.post@n3.nabble.com >
<1526917693149-0.post@n3.nabble.com >
<1526985179479-0.post@n3.nabble.com >
<1527073930538-0.post@n3.nabble.com >
Hi Matthew,
Yeah and you said right!. I have analyzed the entire database and also
created appropriate indexes for the columns used in WHERE/JOIN clauses.
Okay I will just provide the fourth union part of the query which you can
analyze easier(this not that big).
Please find the query part. And refer to the table definitions in my
previous posts.
Query:
select
0 as id,
header.id as header_id,
'0' as emp_id,
0 as sno,
users.alias_id as alias,
partner.name as name,
(select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
from tms_timesheet_details where work_order_no != 'CORPORATE' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) billed_hrs,
(select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
from tms_timesheet_details where release_no = 'unbillable_time' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as unbilled_hrs,
(select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
from tms_timesheet_details where release_no = 'paid_time_off' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as paid_time_off,
(select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
from tms_timesheet_details where release_no = 'unpaid_leave' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as unpaid_leave,
(select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
from tms_timesheet_details where release_no = 'bereavement_time' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as breavement_time,
(select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
from tms_timesheet_details where res_employee_id=users.res_employee_id
and date >='2018-04-16' and date <='2018-04-30' and release_no in
('sick_leave','casual_leave','privilege_leave','optional_holiday') ) as
leave,
'Not Submitted' state,
header.res_employee_id as header_emp_id,
'Not Submitted' as header_status
from res_users users,
res_partner partner,
tms_timesheet_status status,
tms_timesheet_header header
where
header.res_employee_id=users.res_employee_id
and status.id=header.status_id
and users.partner_id=partner.id
and status.name='Draft'
and header.timesheet_period_id=127
and header.res_employee_id in ('14145', '14147',
'ON-14148', '11331', '11332', '11333', 'ON-11334', '65432', '65416',
'54643', '23266', '4681', '56464', '64649', '89564', '98798', '13333',
'44466', '87852', '65464', '65464', '44655', '8201', '65465', 'ON-78785',
'13233', 'ON-5544', 'ON-54654', '23131', '98765', '25134', '13218', '84645',
'4687', '6546', '4988', '89796', '79878', '7198', '15726', '2132', '5310',
'13056', '4446', '16825', '16740', '3912', '19601', '13200', '12981',
'ON-3332', '13166', 'ON-3144', 'ON-1251', 'ON-2799', 'ON-2338', '7286',
'ON-2381', 'ON-3102', 'ON-2938', '64782', '5407', '54641', '46379',
'G151151', '5007', '6011', '5050', '20869', '20204', '12410', '10488',
'14582', '13574', '12982', '7884', '7788', '13417', '7922', '16744',
'16746', '16756', '8292', '16745', '19989', '8297', '5020', '14184',
'17161', '20767', '20753', '20289', '19979', '19975', '20272', '4292',
'G9341010', '14791', '5121', 'ON-1767', 'ON-581', 'ON-700', 'ON-437',
'ON-562', 'ON-1726', 'OFF-1060', 'ON-147', 'OFF-612', 'OFF-635', 'OFF-857',
'ON-900280', 'ON-1934', 'ON-1922', 'ON-2258', 'OFF-2537', 'ON-2872',
'ON-2450', 'ON-2265', 'OFF-2900', 'ON-2551', 'ON-1867', 'ON-2086',
'ON-2348', 'OFF-2706', 'ON-2244', 'ON-2134', 'ON-2654', 'ON-2346',
'ON-1984', 'ON-1243', 'OFF-1266', 'ON-1276', 'ON-2452', 'ON-2179',
'ON-2931', 'ON-2164', 'ON-2468', 'ON-1473', 'ON-1481', 'ON-1521', 'ON-2455',
'ON-2104', 'ON-2295', 'ON-1540', 'ON-900501', 'ON-1351', 'OFF-1364',
'ON-2704', 'ON-1757', 'ON-1690', 'ON-1670', 'ON-1671', 'ON-1689', 'ON-1704',
'ON-1714', 'ON-1655', 'ON-1709', 'ON-1737', 'ON-1725', 'ON-1750', 'ON-1731',
'ON-1715', 'ON-1745', 'ON-1751', 'ON-2191', 'OFF-2686', 'ON-1815',
'ON-2052', 'ON-2019', 'ON-1820', 'ON-1717', 'ON-1713', 'ON-1661',
'OFF-1664', 'ON-1703', 'ON-1734', 'ON-1735', 'ON-1656', 'ON-1705',
'ON-1733', 'ON-1708', 'ON-1666', 'ON-1667', 'ON-1658', 'ON-900487',
'ON-900214', 'ON-1676', 'ON-2378', 'ON-1654', 'ON-2417', 'ON-1488',
'ON-1500', 'ON-1506', 'ON-2875', 'ON-1531', 'ON-2099', 'ON-2195', 'ON-2038',
'ON-1490', 'ON-1489', 'ON-1501', 'ON-1627', 'ON-1929', 'ON-900431',
'ON-1462', 'ON-1466', 'OFF-1468', 'ON-1420', 'ON-1479', 'ON-900543',
'ON-1485', 'ON-1493', 'ON-2347', 'ON-1499', 'ON-2324', 'ON-2733', 'ON-1736',
'ON-1720', 'ON-1674', 'ON-1849', 'ON-1836', 'ON-1846', 'ON-2140',
'OFF-2856', 'ON-2128', 'OFF-2524', 'ON-1845', 'ON-2336', 'ON-1945',
'ON-2008', 'ON-1900', 'ON-2117', 'ON-1837', 'ON-2199', 'ON-2200', 'ON-1821',
'ON-2060', 'ON-1804', 'ON-1803', 'ON-2364', 'ON-2068', 'ON-2474', 'ON-1895',
'ON-1838', 'ON-2024', 'ON-2653', 'ON-1621', 'OFF-1145', 'OFF-994',
'OFF-999', 'ON-1003', 'ON-812', 'OFF-1033', 'ON-1048', 'OFF-1058',
'ON-1053', 'ON-1071', 'ON-1088', 'ON-256', 'ON-207', 'ON-206', 'ON-184',
'OFF-268', 'ON-285', 'OFF-286', 'ON-649', 'ON-301', 'OFF-645', 'ON-338',
'OFF-323', 'ON-347', 'ON-351', 'ON-350', 'ON-354', 'ON-719', 'ON-723',
'ON-137', 'ON-112', 'ON-141', 'ON-752', 'ON-791', 'OFF-802', 'OFF-822',
'ON-573', 'ON-616', 'OFF-587', 'ON-641', 'ON-664', 'ON-336', 'OFF-676',
'ON-687', 'ON-695', 'ON-439', 'ON-406', 'ON-659', 'OFF-890', 'ON-900',
'ON-935', 'ON-228', 'ON-942', 'ON-954', 'OFF-957', 'ON-961', 'ON-830',
'OFF-966', 'OFF-969', 'OFF-951', 'ON-1043', 'OFF-1042', 'ON-1055',
'ON-1109', 'ON-2212', 'ON-2036', 'OFF-1221', 'ON-1238', 'ON-1331',
'OFF-1353', 'ON-1343', 'ON-2014', 'ON-1995', 'ON-2133', 'OFF-2189',
'ON-1581', 'OFF-1595', 'ON-1556', 'ON-1580', 'OFF-1591', 'ON-2437',
'ON-900466', 'ON-1611', 'OFF-1612', 'ON-1624', 'ON-2765', 'ON-1927',
'ON-2361', 'ON-2054', 'ON-1633', 'ON-1503', 'OFF-2546', 'ON-1512',
'ON-1536', 'ON-2543', 'ON-2558', 'ON-2237', 'ON-1535', 'ON-2436',
'OFF-1547', 'ON-2380', 'ON-2116', 'ON-2820', 'ON-1563', 'ON-900512',
'ON-1568', 'ON-1570', 'ON-900514', 'ON-1130', 'ON-1632', 'ON-2359',
'ON-3176', 'ON-2132', 'ON-2012', 'ON-1762', 'ON-900230', 'ON-2299',
'ON-3552', 'ON-2557', 'ON-2129', 'ON-1918', 'OFF-2552', 'ON-2235',
'OFF-2773', 'ON-2123', 'ON-2658', 'ON-1866', 'ON-2506', 'OFF-2703',
'ON-2882', 'ON-2649', 'ON-2997', 'ON-1925', 'OFF-3096', 'ON-3297',
'ON-3359', 'ON-3352', 'ON-3357', 'ON-3378', 'ON-3071', 'OFF-2702',
'ON-2801', 'ON-2689', 'ON-2416', 'ON-3305', 'OFF-2695', 'ON-2069',
'ON-3318', 'OFF-3681', 'ON-1541', 'ON-2248', 'ON-2249', 'ON-2250',
'ON-2259', 'ON-2280', 'ON-3345', 'OFF-3545', 'ON-2286', 'ON-2293',
'ON-2277', 'ON-1180', 'ON-2304', 'OFF-3575', 'OFF-2384', 'OFF-2513',
'ON-2444', 'OFF-3218', 'ON-2497', 'ON-2708', 'ON-2774', 'ON-2667',
'ON-2803', 'OFF-3044', 'ON-2290', 'ON-2791', 'ON-2810', 'ON-2767',
'ON-2415', 'ON-2489', 'ON-2180', 'ON-2131', 'ON-2207', 'ON-2233', 'ON-3045',
'ON-3675', 'ON-2260', 'ON-2700', 'ON-2418', 'ON-2924', 'OFF-2828',
'ON-2536', 'ON-3127', 'ON-2472', 'ON-2482', 'ON-3098', 'ON-2473', 'ON-3073',
'ON-2855', 'OFF-2709', 'ON-2789', 'ON-2589', 'ON-2409', 'ON-3455',
'OFF-3556', 'ON-2510', 'ON-3120', 'ON-2457', 'ON-2303', 'ON-2044',
'ON-2313', 'ON-2326', 'ON-2312', 'OFF-2391', 'ON-2438', 'OFF-3548',
'ON-2581', 'ON-2525', 'ON-2538', 'ON-2433', 'ON-3300', 'ON-2487', 'ON-2754',
'OFF-3049', 'ON-2370', 'ON-3151', 'ON-3100', 'ON-3101', 'ON-1044',
'ON-2431', 'ON-2371', 'ON-2714', 'OFF-3544', 'OFF-2388', 'ON-2790',
'OFF-2918', 'ON-2681', 'ON-2512', 'ON-2511', 'ON-2521', 'OFF-2539',
'ON-3551', 'OFF-3549', 'OFF-3462', 'ON-2745', 'ON-2778', 'OFF-2821',
'ON-900498', 'ON-2812', 'OFF-2955', 'ON-2840', 'ON-2847', 'ON-3309',
'OFF-2917', 'OFF-2857', 'ON-2795', 'ON-2793', 'ON-2796', 'ON-2873',
'ON-2874', 'OFF-2870', 'ON-2889', 'ON-2719', 'ON-2824', 'ON-2861',
'ON-2865', 'ON-2866', 'OFF-2826', 'OFF-2898', 'ON-3301', 'OFF-2961',
'ON-2878', 'OFF-2886', 'ON-2914', 'ON-2909', 'OFF-2906', 'ON-2922',
'OFF-3682', 'ON-2937', 'ON-2913', 'OFF-2916', 'ON-2923', 'OFF-3006',
'OFF-3046', 'OFF-3042', 'OFF-3050', 'OFF-2642', 'ON-3093', 'ON-2685',
'OFF-3112', 'ON-3576', 'OFF-3094', 'OFF-3126', 'ON-3129', 'ON-3152',
'ON-3153', 'ON-3171', 'ON-3177', 'ON-3217', 'ON-2617', 'ON-3654', 'ON-3677',
'ON-1817', 'ON-3684', 'ON-3686', 'ON-3685', 'ON-3278', 'ON-3317', 'ON-3316',
'ON-3325', 'ON-3349', 'ON-3351', 'ON-3391', 'ON-3398', 'ON-3451', 'ON-3414',
'ON-3452', 'ON-3412', 'ON-3453', 'ON-3417', 'OFF-3473', 'ON-3457',
'ON-3523', 'ON-3546', 'ON-3554', 'ON-3553', 'ON-900552', 'G12941370',
'6479', '14192', '87546', '19755', '16751', '2095', '12244', '12363',
'17510', '19935', '7973', '13189', '19733', '19928', '21124', '16725',
'7244', '3027', '11426', '12732', '8530', '10301', '19555', '19706',
'20097', '13156', '14690', '4183', '8340', '18026', '12297', '6577',
'11301', '12980', '18138', '5603', '17587', '19118', '12210', '7292',
'17577', '16578', '7895', '200186', '20100', '34541', '19370', '11111',
'1492', '1111', '2556', '3445643643', '20379', 'ON-2338P', '20899')
And the explain plan for the above query can be found in the below link.
Link: https://explain.depesz.com/s/y3J8 <http:// ;
Please help me tune this query or logic to rewrite at the painful area in
the query.
Thanks in Advance!
Regards,
Pavan
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
view thread (25+ messages) latest in thread
Message-ID: <1527082761283-0.post@n3.nabble.com>
Permalink: ../1527082761283-0.post@n3.nabble.com/
Also on: postgresql.org/message-id/1527082761283-0.post@n3.nabble.com
copy link · copy postgr.es
reply 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-performance@postgresql.org
Cc: pavan.postgresdba@gmail.com
Subject: Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!
In-Reply-To: <1527082761283-0.post@n3.nabble.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