Question

SQL语句优化，使用postgresql数据库，查询下面sql,需要20多分钟：

select
sum(case when d.acpt_ind='1' and (b.SYSTEM_CD is null or b.SYSTEM_CD='A') and (d.cuss_ind=1 or d.WebCKI_ind=1) and m.flt_seg_day_id is not null then 1
when c.SYSTEM_CD is not null and d.acpt_ind='1' and a.airln_cd in ('MF','CZ','OQ') and d.cuss_ind=0 and d.WebCKI_ind=0 and c.cki_typ in ('C','W') and m.flt_seg_day_id is not null then 1
else 0 end) as airpt_sec_bc,
sum(case when d.acpt_ind='1' and (b.SYSTEM_CD is null or b.SYSTEM_CD='A') and (d.cuss_ind=1 or d.WebCKI_ind=1) and m.flt_seg_day_id is not null then 1
when c.SYSTEM_CD is not null and d.acpt_ind='1' and a.airln_cd in ('MF','CZ','OQ') and d.cuss_ind=0 and d.WebCKI_ind=0 and c.cki_typ in ('C','W') and m.flt_seg_day_id is not null then 1
else 0 end) as airpt_sec_bag
from dss_pbidt.dcs_flt a
inner join
(select flt_seg_day_id,pax_hist_id,acpt_ind,chkin_pid_nbr,cuss_ind,webcki_ind from dss_pdata.enr_check_in_event
union all
select flt_seg_day_id,pax_hist_id,acpt_ind,chkin_pid_nbr,cuss_ind,webcki_ind from dss_phist.enr_check_in_event_h2015
union all
select flt_seg_day_id,pax_hist_id,acpt_ind,chkin_pid_nbr,cuss_ind,webcki_ind from dss_phist.enr_check_in_event_h2014)d
on( a.flt_seg_day_id=d.flt_seg_day_id
and
d.acpt_ind=1
)
left outer join (select distinct trim(both from PID_NBR) as PID_NBR,SYSTEM_CD from dss_pdata.PRT_CUSS_PID where cz_ind=0)b
on (trim(both from d.chkin_pid_nbr) = b.PID_NBR)
left outer join (select distinct trim(both from PID_NBR) as PID_NBR,SYSTEM_CD,cki_typ from dss_pdata.PRT_CUSS_PID where cz_ind=1)c
on (trim(both from d.chkin_pid_nbr) = c.PID_NBR)
left outer join
(select distinct s.flt_seg_day_id as flt_seg_day_id,s.pax_hist_id as pax_hist_id from
(select opr_dt,flt_seg_day_id,pax_hist_id,hist_actn_cd,opr_pid_nbr from dss_pdata.enr_pax_cki_history
union all
select opr_dt,flt_seg_day_id,pax_hist_id,hist_actn_cd,opr_pid_nbr from dss_phist.enr_pax_cki_history_h2015
union all
select opr_dt,flt_seg_day_id,pax_hist_id,hist_actn_cd,opr_pid_nbr from dss_phist.enr_pax_cki_history_h2014 ) s
where s.opr_dt>='2015-01-01' and s.opr_dt<'2015-02-01' and ((s.hist_actn_cd='BC' and s.opr_pid_nbr in (select distinct(pid_nbr) from
dss_pdata.prt_cuss_pid where system_cd in ('P','H') and cz_ind='0' )) or s.hist_actn_cd='BAG')
)m
on
(
d.flt_seg_day_id=m.flt_seg_day_id
and
d.pax_hist_id=m.pax_hist_id
)
where a.lcl_dep_date between '20150101' and '20150201'

Answer 1

postgresql（8.2）的配置文件中有一个参数log_min_duration_statement，意思是只log执行时间大于设定值的语句，如果设为0，表示log所有语句；如果设为-1，表示不log任何语句。
看起来，这个配置选项对性能的调整是很有用的，比如可以设置：
log_min_duration_statement = 1000
则只log执行时间大于1s的语句，重点优化这些sql语句就好了。
然而，奇怪的，这个选项不太容易生效！经过反复试验，原来需要如下配置：
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
log_connections = off
#log_disconnections = off
log_duration = off
log_line_prefix = '%t [%p]: [%l-1] ' # Special values:
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = PID
# %t = timestamp (no milliseconds)
# %m = timestamp with milliseconds
# %i = command tag
# %c = session id
# %l = session line number
# %s = session start timestamp
# %x = transaction id
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
log_statement = 'none' # none, mod, ddl, all
#log_statement = 'all' # none, mod, ddl, all
#log_hostname = off
注意看上面的其中两个选项的设置：
log_duration = off
log_statement = 'none'
这两个选项的意思是不log任何sql语句和执行时间，但是恰恰是关闭了这两个，log_min_duration_statement才会生效！可能postgresql内部 对这两个选项做了“互斥”处理吧。