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
$ 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
