On 02/08/2018 08:35 AM, dbyzaa@xxxxxxx wrote:
Hi,everybody recently, I found some slow SQL runtime as long as 4
seconds in postgres log. However, when I was executing on PSQL client,
it takes only 3.6 milliseconds, I did the following things.
1. add auto_explain
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 100
auto_explain.log_analyze = true
auto_explain.log_buffers = true
2. set lock debug
log_lock_waits = on
deadlock_timeout = 500ms
log_min_duration_statement = 100
Then i find it takes only 2 milliseconds(auto_explain),but in
postgres log it takes 4 seconds, please help me find this problem!!!
What client is running the SQL that shows up in the log?
env information
$ uname -a
Linux 3.10.0-693.el7.x86_64 #1 SMP Wed Aug 2 06:49:08 PDT 2017 x86_64
x86_64 x86_64 GNU/Linux
postgres=> select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.2, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat
4.4.7-18), 64-bit
slow sql log
2018-02-06 14:52:52.097
CST,"postgres","database",63747,"KFCS2-14:58241",5a794e91.f903,3,"SELECT",2018-02-06
14:43:29 CST,5/1111554,0,LOG,00000,"duration: 4347.635 ms execute
<unnamed>: select orderprodu0_.order_id as order_id1_19_,
orderprodu0_.amount1 as amount2_19_, orderprodu0_.amount2 as
amount3_19_, orderprodu0_.amount3 as amount4_19_, orderprodu0_.amount4
as amount5_19_, orderprodu0_.amount5 as amount6_19_,
orderprodu0_.auto_confirm_time as auto_con7_19_,
orderprodu0_.bestowed_sum_score as bestowed8_19_,
orderprodu0_.board_train_code as board_tr9_19_, orderprodu0_.bureau_code
as bureau_10_19_, orderprodu0_.coach_no as coach_n11_19_,
orderprodu0_.comments_info1 as comment12_19_,
orderprodu0_.comments_info2 as comment13_19_,
orderprodu0_.company_abbreviation as company14_19_,
orderprodu0_.company_id as company15_19_, orderprodu0_.complain_flag as
complai16_19_, orderprodu0_.day_night as day_nig17_19_,
orderprodu0_.deliver_mode as deliver18_19_,
orderprodu0_.destination_code as destina19_19_,
orderprodu0_.destination_name as destina20_19_,
orderprodu0_.destination_type as destina21_19_, orderprodu0_.drawee_name
as drawee_22_19_, orderprodu0_.evaluate_flag as evaluat23_19_,
orderprodu0_.exception_flag as excepti24_19_, orderprodu0_.flag1 as
flag25_19_, orderprodu0_.flag2 as flag26_19_, orderprodu0_.flag3 as
flag27_19_, orderprodu0_.flag4 as flag28_19_, orderprodu0_.flag5 as
flag29_19_, orderprodu0_.freight as freight30_19_,
orderprodu0_.from_station_name as from_st31_19_,
orderprodu0_.from_tele_code as from_te32_19_, orderprodu0_.group_no as
group_n33_19_, orderprodu0_.invoice_email as invoice34_19_,
orderprodu0_.invoice_flag as invoice35_19_, orderprodu0_.invoice_state
as invoice36_19_, orderprodu0_.last_modify_time as last_mo37_19_,
orderprodu0_.made_flag as made_fl38_19_, orderprodu0_.mobile as
mobile39_19_, orderprodu0_.natural_day as natural40_19_,
orderprodu0_.office_no as office_41_19_, orderprodu0_.operator_id as
operato42_19_, orderprodu0_.order_batch as order_b43_19_,
orderprodu0_.order_code as order_c44_19_, orderprodu0_.order_date as
order_d45_19_, orderprodu0_.order_day as order_d46_19_,
orderprodu0_.order_pay_method as order_p47_19_,
orderprodu0_.order_pay_type as order_p48_19_, orderprodu0_.order_state
as order_s49_19_, orderprodu0_.order_total_price as order_t50_19_,
orderprodu0_.orders_out_time as orders_51_19_,
orderprodu0_.passenger_name as passeng52_19_,
orderprodu0_.passenger_phone as passeng53_19_, orderprodu0_.pay_out_time
as pay_out54_19_, orderprodu0_.payment_info_id as payment55_19_,
orderprodu0_.print_time as print_t56_19_,
orderprodu0_.product_total_price as product57_19_,
orderprodu0_.return_reason as return_58_19_, orderprodu0_.sales_channels
as sales_c59_19_, orderprodu0_.seat_no as seat_no60_19_,
orderprodu0_.seat_type_code as seat_ty61_19_, orderprodu0_.sell_station
as sell_st62_19_, orderprodu0_.sequence_no as sequenc63_19_,
orderprodu0_.service_date as service64_19_, orderprodu0_.service_time as
service65_19_, orderprodu0_.start_time as start_t66_19_,
orderprodu0_.start_train_date as start_t67_19_,
orderprodu0_.start_train_time as start_t68_19_,
orderprodu0_.statistics_date as statist69_19_,
orderprodu0_.synchronous_flag as synchro70_19_,
orderprodu0_.synchronous_time as synchro71_19_, orderprodu0_.ticket_no
as ticket_72_19_, orderprodu0_.to_station_name as to_stat73_19_,
orderprodu0_.to_tele_code as to_tele74_19_, orderprodu0_.train_code as
train_c75_19_, orderprodu0_.train_date as train_d76_19_,
orderprodu0_.train_no as train_n77_19_, orderprodu0_.train_setname as
train_s78_19_, orderprodu0_.transmit_flag as transmi79_19_,
orderprodu0_.transmit_time as transmi80_19_, orderprodu0_.user_id as
user_id81_19_, orderprodu0_.user_name as user_na82_19_,
orderprodu0_.valid_flag as valid_f83_19_, orderprodu0_.window_no as
window_84_19_ from tablename orderprodu0_ where
orderprodu0_.company_id=$1 and orderprodu0_.destination_code=$2 and
(orderprodu0_.order_state not in ('0' , '1' , '2' , '3')) and
orderprodu0_.service_date>=$3 and orderprodu0_.service_date<=$4 order by
orderprodu0_.service_date asc","parameters: $1 = '86716', $2 = 'ZAF', $3
= '20180201', $4 = '20180228'",,,,,,,,""
auto_expain log
2018-02-06 14:52:53.731
CST,"postgres","dataname",63747,"KFCS2-14:58241",5a794e91.f903,4,"BIND",2018-02-06
14:43:29 CST,5/1111554,0,LOG,00000,"duration: 4347.624 ms plan:
Query Text: select orderprodu0_.order_id as order_id1_19_,
orderprodu0_.amount1 as amount2_19_, orderprodu0_.amount2 as
amount3_19_, orderprodu0_.amount3 as amount4_19_, orderprodu0_.amount4
as amount5_19_, orderprodu0_.amount5 as amount6_19_,
orderprodu0_.auto_confirm_time as auto_con7_19_,
orderprodu0_.bestowed_sum_score as bestowed8_19_,
orderprodu0_.board_train_code as board_tr9_19_, orderprodu0_.bureau_code
as bureau_10_19_, orderprodu0_.coach_no as coach_n11_19_,
orderprodu0_.comments_info1 as comment12_19_,
orderprodu0_.comments_info2 as comment13_19_,
orderprodu0_.company_abbreviation as company14_19_,
orderprodu0_.company_id as company15_19_, orderprodu0_.complain_flag as
complai16_19_, orderprodu0_.day_night as day_nig17_19_,
orderprodu0_.deliver_mode as deliver18_19_,
orderprodu0_.destination_code as destina19_19_,
orderprodu0_.destination_name as destina20_19_,
orderprodu0_.destination_type as destina21_19_, orderprodu0_.drawee_name
as drawee_22_19_, orderprodu0_.evaluate_flag as evaluat23_19_,
orderprodu0_.exception_flag as excepti24_19_, orderprodu0_.flag1 as
flag25_19_, orderprodu0_.flag2 as flag26_19_, orderprodu0_.flag3 as
flag27_19_, orderprodu0_.flag4 as flag28_19_, orderprodu0_.flag5 as
flag29_19_, orderprodu0_.freight as freight30_19_,
orderprodu0_.from_station_name as from_st31_19_,
orderprodu0_.from_tele_code as from_te32_19_, orderprodu0_.group_no as
group_n33_19_, orderprodu0_.invoice_email as invoice34_19_,
orderprodu0_.invoice_flag as invoice35_19_, orderprodu0_.invoice_state
as invoice36_19_, orderprodu0_.last_modify_time as last_mo37_19_,
orderprodu0_.made_flag as made_fl38_19_, orderprodu0_.mobile as
mobile39_19_, orderprodu0_.natural_day as natural40_19_,
orderprodu0_.office_no as office_41_19_, orderprodu0_.operator_id as
operato42_19_, orderprodu0_.order_batch as order_b43_19_,
orderprodu0_.order_code as order_c44_19_, orderprodu0_.order_date as
order_d45_19_, orderprodu0_.order_day as order_d46_19_,
orderprodu0_.order_pay_method as order_p47_19_,
orderprodu0_.order_pay_type as order_p48_19_, orderprodu0_.order_state
as order_s49_19_, orderprodu0_.order_total_price as order_t50_19_,
orderprodu0_.orders_out_time as orders_51_19_,
orderprodu0_.passenger_name as passeng52_19_,
orderprodu0_.passenger_phone as passeng53_19_, orderprodu0_.pay_out_time
as pay_out54_19_, orderprodu0_.payment_info_id as payment55_19_,
orderprodu0_.print_time as print_t56_19_,
orderprodu0_.product_total_price as product57_19_,
orderprodu0_.return_reason as return_58_19_, orderprodu0_.sales_channels
as sales_c59_19_, orderprodu0_.seat_no as seat_no60_19_,
orderprodu0_.seat_type_code as seat_ty61_19_, orderprodu0_.sell_station
as sell_st62_19_, orderprodu0_.sequence_no as sequenc63_19_,
orderprodu0_.service_date as service64_19_, orderprodu0_.service_time as
service65_19_, orderprodu0_.start_time as start_t66_19_,
orderprodu0_.start_train_date as start_t67_19_,
orderprodu0_.start_train_time as start_t68_19_,
orderprodu0_.statistics_date as statist69_19_,
orderprodu0_.synchronous_flag as synchro70_19_,
orderprodu0_.synchronous_time as synchro71_19_, orderprodu0_.ticket_no
as ticket_72_19_, orderprodu0_.to_station_name as to_stat73_19_,
orderprodu0_.to_tele_code as to_tele74_19_, orderprodu0_.train_code as
train_c75_19_, orderprodu0_.train_date as train_d76_19_,
orderprodu0_.train_no as train_n77_19_, orderprodu0_.train_setname as
train_s78_19_, orderprodu0_.transmit_flag as transmi79_19_,
orderprodu0_.transmit_time as transmi80_19_, orderprodu0_.user_id as
user_id81_19_, orderprodu0_.user_name as user_na82_19_,
orderprodu0_.valid_flag as valid_f83_19_, orderprodu0_.window_no as
window_84_19_ from tablename orderprodu0_ where
orderprodu0_.company_id=$1 and orderprodu0_.destination_code=$2 and
(orderprodu0_.order_state not in ('0' , '1' , '2' , '3')) and
orderprodu0_.service_date>=$3 and orderprodu0_.service_date<=$4 order by
orderprodu0_.service_date asc
Sort (cost=200.02..200.03 rows=4 width=1661) (actual time=1.991..2.076
rows=338 loops=1)
Sort Key: service_date
Sort Method: quicksort Memory: 220kB
Buffers: shared hit=345
-> Bitmap Heap Scan on tablename orderprodu0_ (cost=4.25..199.98 rows=4
width=1661) (actual time=0.182..0.944 rows=338 loops=1)
Recheck Cond: ((company_id = '86716'::bigint) AND
((service_date)::text >= '20180201'::text) AND ((service_date)::text <=
'20180228'::text))
Filter: (((destination_code)::text = 'ZAF'::text) AND
((order_state)::text <> ALL ('{0,1,2,3}'::text[])))
Rows Removed by Filter: 23
Heap Blocks: exact=340
Buffers: shared hit=345
-> Bitmap Index Scan on tablename_index1 (cost=0.00..4.25 rows=66
width=0) (actual time=0.107..0.107 rows=398 loops=1)
Index Cond: ((company_id = '86716'::bigint) AND
((service_date)::text >= '20180201'::text) AND ((service_date)::text <=
'20180228'::text))
Buffers: shared hit=5",,,,,,,,,""
run on psql client
# explain (analyze,buffers,timing,costs) select orderprodu0_.order_id as
order_id1_19_, orderprodu0_.amount1 as amount2_19_, orderprodu0_.amount2
as amount3_19_, orderprodu0_.amount3 as amount4_19_,
orderprodu0_.amount4 as amount5_19_, orderprodu0_.amount5 as
amount6_19_, orderprodu0_.auto_confirm_time as auto_con7_19_,
orderprodu0_.bestowed_sum_score as bestowed8_19_,
orderprodu0_.board_train_code as board_tr9_19_, orderprodu0_.bureau_code
as bureau_10_19_, orderprodu0_.coach_no as coach_n11_19_,
orderprodu0_.comments_info1 as comment12_19_,
orderprodu0_.comments_info2 as comment13_19_,
orderprodu0_.company_abbreviation as company14_19_,
orderprodu0_.company_id as company15_19_, orderprodu0_.complain_flag as
complai16_19_, orderprodu0_.day_night as day_nig17_19_,
orderprodu0_.deliver_mode as deliver18_19_,
orderprodu0_.destination_code as destina19_19_,
orderprodu0_.destination_name as destina20_19_,
orderprodu0_.destination_type as destina21_19_, orderprodu0_.drawee_name
as drawee_22_19_, orderprodu0_.evaluate_flag as evaluat23_19_,
orderprodu0_.exception_flag as excepti24_19_, orderprodu0_.flag1 as
flag25_19_, orderprodu0_.flag2 as flag26_19_, orderprodu0_.flag3 as
flag27_19_, orderprodu0_.flag4 as flag28_19_, orderprodu0_.flag5 as
flag29_19_, orderprodu0_.freight as freight30_19_,
orderprodu0_.from_station_name as from_st31_19_,
orderprodu0_.from_tele_code as from_te32_19_, orderprodu0_.group_no as
group_n33_19_, orderprodu0_.invoice_email as invoice34_19_,
orderprodu0_.invoice_flag as invoice35_19_, orderprodu0_.invoice_state
as invoice36_19_, orderprodu0_.last_modify_time as last_mo37_19_,
orderprodu0_.made_flag as made_fl38_19_, orderprodu0_.mobile as
mobile39_19_, orderprodu0_.natural_day as natural40_19_,
orderprodu0_.office_no as office_41_19_, orderprodu0_.operator_id as
operato42_19_, orderprodu0_.order_batch as order_b43_19_,
orderprodu0_.order_code as order_c44_19_, orderprodu0_.order_date as
order_d45_19_, orderprodu0_.order_day as order_d46_19_,
orderprodu0_.order_pay_method as order_p47_19_,
orderprodu0_.order_pay_type as order_p48_19_, orderprodu0_.order_state
as order_s49_19_, orderprodu0_.order_total_price as order_t50_19_,
orderprodu0_.orders_out_time as orders_51_19_,
orderprodu0_.passenger_name as passeng52_19_,
orderprodu0_.passenger_phone as passeng53_19_, orderprodu0_.pay_out_time
as pay_out54_19_, orderprodu0_.payment_info_id as payment55_19_,
orderprodu0_.print_time as print_t56_19_,
orderprodu0_.product_total_price as product57_19_,
orderprodu0_.return_reason as return_58_19_, orderprodu0_.sales_channels
as sales_c59_19_, orderprodu0_.seat_no as seat_no60_19_,
orderprodu0_.seat_type_code as seat_ty61_19_, orderprodu0_.sell_station
as sell_st62_19_, orderprodu0_.sequence_no as sequenc63_19_,
orderprodu0_.service_date as service64_19_, orderprodu0_.service_time as
service65_19_, orderprodu0_.start_time as start_t66_19_,
orderprodu0_.start_train_date as start_t67_19_,
orderprodu0_.start_train_time as start_t68_19_,
orderprodu0_.statistics_date as statist69_19_,
orderprodu0_.synchronous_flag as synchro70_19_,
orderprodu0_.synchronous_time as synchro71_19_, orderprodu0_.ticket_no
as ticket_72_19_, orderprodu0_.to_station_name as to_stat73_19_,
orderprodu0_.to_tele_code as to_tele74_19_, orderprodu0_.train_code as
train_c75_19_, orderprodu0_.train_date as train_d76_19_,
orderprodu0_.train_no as train_n77_19_, orderprodu0_.train_setname as
train_s78_19_, orderprodu0_.transmit_flag as transmi79_19_,
orderprodu0_.transmit_time as transmi80_19_, orderprodu0_.user_id as
user_id81_19_, orderprodu0_.user_name as user_na82_19_,
orderprodu0_.valid_flag as valid_f83_19_, orderprodu0_.window_no as
window_84_19_ from sljy_order_product orderprodu0_ where
orderprodu0_.company_id='86716' and orderprodu0_.destination_code='ZAF'
and (orderprodu0_.order_state not in ('0' , '1' , '2' , '3')) and
orderprodu0_.service_date>='20180201' and
orderprodu0_.service_date<='20180228' order by orderprodu0_.service_date
asc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=232.43..232.44 rows=5 width=1661) (actual time=3.459..3.511
rows=490 loops=1)
Sort Key: service_date
Sort Method: quicksort Memory: 305kB
Buffers: shared hit=506
-> Bitmap Heap Scan on tablename orderprodu0_ (cost=4.39..232.37
rows=5 width=1661) (actual time=0.243..2.123 rows=490 loops=1)
Recheck Cond: ((company_id = '86716'::bigint) AND
((service_date)::text >= '20180201'::text) AND ((service_date)::text <=
'20180228'::text))
Filter: (((destination_code)::text = 'ZAF'::text) AND
((order_state)::text <> ALL ('{0,1,2,3}'::text[])))
Rows Removed by Filter: 34
Heap Blocks: exact=500
Buffers: shared hit=506
-> Bitmap Index Scan on tablename_index1 (cost=0.00..4.39
rows=77 width=0) (actual time=0.178..0.178 rows=622 loops=1)
Index Cond: ((company_id = '86716'::bigint) AND
((service_date)::text >= '20180201'::text) AND ((service_date)::text <=
'20180228'::text))
Buffers: shared hit=6
Planning time: 0.307 ms
Execution time: 3.588 ms
(15 rows)
Time: 4.489 ms
------------------------------------------------------------------------
dbyzaa@xxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
