performance - PostgreSQL 9.2 - Agg 查询速度慢，计数(distinct x)

Question

我们正处于从 MySQL 到 PG 9.2 的转换过程中。然而，我们在 PG 中的聚合查询遇到了障碍。与 MySQL 相比，它们在 PG 中运行的时间要长得多。我认为导致最大问题的是我们在聚合查询中的 count(distinct x)。这会导致额外的排序，无论我们将 WORK_MEM 设置为什么，它都会溢出到磁盘上。我们还尝试将 enable_sort 设置为“关闭”并将 enable_seqscan 设置为“关闭”。此外，我们定期运行 VACUUM ANALYZE。最后，我们无法利用索引，因为我们的查询有许多暗淡的字段，而且其中大部分是连接在一起的。表是 40 到 80 磨。请任何建议会有所帮助!这是解释分析的示例。

查询

SELECT
    concat(
        cast(coalesce(trim(tablea.state_id),'na') as varchar),
        '-',
        cast(
            coalesce(
                CASE WHEN char_length(cast(tablea.case_num as varchar)) = 12
                    THEN left(cast(tablea.case_num as varchar), 2)
                    else left(cast(tablea.case_num as varchar), 1)
                END,'na'
            ) as varchar
        )
    ),
    to_char(tablea.visit_date,'yyyy-mm-01') ,
    coalesce(tablea.payor_code,0) ,
    coalesce(tablea.los,'other') ,
    case when cpt_group is null or cpt_group = ''
        then '0'
        else split_part(tablea.cpt_group,'-',1)
    end ,
    case when cpt_group is null or cpt_group = ''
        then '0'
        else split_part(tablea.cpt_group,'-',2)
    end ,
    case when (tablea.erp_code) isnull
        then 'na'
        else concat(
            cast(coalesce(trim(tablea.state_id), 'na') as varchar),
            '-',
            coalesce(cast(tablea.erp_code as varchar), 'na') ,
            '-',
            coalesce(cast(tablea.mlp_code as varchar), 'na')
            )
    end ,
    coalesce(tablea.erp_attnd,'n') ,
    count(distinct concat(tablea.state_id,tablea.case_num)) ,
    count( unique_rec_id),
    sum(tablea.units) ,
    sum(tablea.total_amt) ,
    sum(tablea.total_rvu) ,
    sum(tablea.budget_rvu),
    sum(tablea.work_rvu) ,
    sum(tablea.payroll_rvu) ,
    sum(extract(day from charges.insert_ts - charges.visit_date))
FROM tablea
GROUP BY 1,2,3,4,5,6 ,7 ,8

表结构

state_id character varying(8),
unique_rec_id bigint NOT NULL,
case_num bigint,
seq_nbr smallint,
charge_code character varying(18),
cpt_type character varying(2),
cpt_code character varying(19),
cpt_version_code character varying(2),
erp_code integer,
mlp_code integer,
shared_svcs character varying(1),
visit_date date,
units numeric(6,2),
unit_amt numeric(7,2),
total_amt numeric(7,2),
unit_rvu numeric(6,2),
total_rvu numeric(6,2),
place_of_serv smallint,
insert_ts timestamp(6) without time zone,
update_ts timestamp(6) without time zone,
alw_amt numeric(8,2),
pmt_amt numeric(8,2),
adj_amt numeric(8,2),
payor_code smallint,
coder_id smallint,
erp_attnd character varying(1),
tos smallint,
etos smallint,
los character varying(5),
budget_rvu numeric(6,2),
work_rvu numeric(6,2),
payroll_rvu numeric(6,2),
cpt_group character varying(7),
CONSTRAINT charges_pkey PRIMARY KEY (unique_rec_id)

解释输出

"GroupAggregate  (cost=10007828904.29..10010019592.38 rows=3519178 width=77) (actual time=1270982.096..1448667.608 rows=7848213 loops=1)"
"  Output: (pg_catalog.concat((COALESCE(btrim((state_id)::text), 'na'::text))::character varying, '-', (COALESCE(CASE WHEN (char_length(((case_num)::character varying)::text) = 12) THEN "left"(((case_num)::character varying)::text, 2) ELSE "left"(((case_num)::character varying)::text, 1) END, 'na'::text))::character varying)), (to_char((visit_date)::timestamp with time zone, 'yyyy-mm-01'::text)), (COALESCE((payor_code)::integer, 0)), (COALESCE(los, 'other'::character varying)), (CASE WHEN ((cpt_group IS NULL) OR ((cpt_group)::text = ''::text)) THEN '0'::text ELSE split_part((cpt_group)::text, '-'::text, 1) END), (CASE WHEN ((cpt_group IS NULL) OR ((cpt_group)::text = ''::text)) THEN '0'::text ELSE split_part((cpt_group)::text, '-'::text, 2) END), (CASE WHEN (erp_code IS NULL) THEN 'na'::text ELSE pg_catalog.concat((COALESCE(btrim((state_id)::text), 'na'::text))::character varying, '-', COALESCE((erp_code)::character varying, 'na'::character varying), '-', COALESCE((mlp_code)::character varying, 'na'::character varying)) END), (COALESCE(erp_attnd, 'n'::character varying)), count(DISTINCT pg_catalog.concat(state_id, case_num)), count(unique_rec_id), sum(units), sum(total_amt), sum(total_rvu), sum(budget_rvu), sum(work_rvu), sum(payroll_rvu), sum(date_part('day'::text, (insert_ts - (visit_date)::timestamp without time zone)))"
"  Buffers: shared hit=866434, temp read=558843 written=558843"
"  ->  Sort  (cost=10007828904.29..10007916883.73 rows=35191776 width=77) (actual time=1270982.019..1314712.552 rows=36729163 loops=1)"
"        Output: (pg_catalog.concat((COALESCE(btrim((state_id)::text), 'na'::text))::character varying, '-', (COALESCE(CASE WHEN (char_length(((case_num)::character varying)::text) = 12) THEN "left"(((case_num)::character varying)::text, 2) ELSE "left"(((case_num)::character varying)::text, 1) END, 'na'::text))::character varying)), (to_char((visit_date)::timestamp with time zone, 'yyyy-mm-01'::text)), (COALESCE((payor_code)::integer, 0)), (COALESCE(los, 'other'::character varying)), (CASE WHEN ((cpt_group IS NULL) OR ((cpt_group)::text = ''::text)) THEN '0'::text ELSE split_part((cpt_group)::text, '-'::text, 1) END), (CASE WHEN ((cpt_group IS NULL) OR ((cpt_group)::text = ''::text)) THEN '0'::text ELSE split_part((cpt_group)::text, '-'::text, 2) END), (CASE WHEN (erp_code IS NULL) THEN 'na'::text ELSE pg_catalog.concat((COALESCE(btrim((state_id)::text), 'na'::text))::character varying, '-', COALESCE((erp_code)::character varying, 'na'::character varying), '-', COALESCE((mlp_code)::character varying, 'na'::character varying)) END), (COALESCE(erp_attnd, 'n'::character varying)), state_id, case_num, unique_rec_id, units, total_amt, total_rvu, budget_rvu, work_rvu, payroll_rvu, insert_ts, visit_date"
"        Sort Key: (pg_catalog.concat((COALESCE(btrim((charges.state_id)::text), 'na'::text))::character varying, '-', (COALESCE(CASE WHEN (char_length(((charges.case_num)::character varying)::text) = 12) THEN "left"(((charges.case_num)::character varying)::text, 2) ELSE "left"(((charges.case_num)::character varying)::text, 1) END, 'na'::text))::character varying)), (to_char((charges.visit_date)::timestamp with time zone, 'yyyy-mm-01'::text)), (COALESCE((charges.payor_code)::integer, 0)), (COALESCE(charges.los, 'other'::character varying)), (CASE WHEN ((charges.cpt_group IS NULL) OR ((charges.cpt_group)::text = ''::text)) THEN '0'::text ELSE split_part((charges.cpt_group)::text, '-'::text, 1) END), (CASE WHEN ((charges.cpt_group IS NULL) OR ((charges.cpt_group)::text = ''::text)) THEN '0'::text ELSE split_part((charges.cpt_group)::text, '-'::text, 2) END), (CASE WHEN (charges.erp_code IS NULL) THEN 'na'::text ELSE pg_catalog.concat((COALESCE(btrim((charges.state_id)::text), 'na'::text))::character varying, '-', COALESCE((charges.erp_code)::character varying, 'na'::character varying), '-', COALESCE((charges.mlp_code)::character varying, 'na'::character varying)) END), (COALESCE(charges.erp_attnd, 'n'::character varying))"
"        Sort Method: external merge  Disk: 4470728kB"
"        Buffers: shared hit=866434, temp read=558843 written=558843"
"        ->  Seq Scan on public.charges  (cost=10000000000.00..10003417837.76 rows=35191776 width=77) (actual time=0.062..182871.790 rows=36729163 loops=1)"
"              Output: pg_catalog.concat((COALESCE(btrim((state_id)::text), 'na'::text))::character varying, '-', (COALESCE(CASE WHEN (char_length(((case_num)::character varying)::text) = 12) THEN "left"(((case_num)::character varying)::text, 2) ELSE "left"(((case_num)::character varying)::text, 1) END, 'na'::text))::character varying), to_char((visit_date)::timestamp with time zone, 'yyyy-mm-01'::text), COALESCE((payor_code)::integer, 0), COALESCE(los, 'other'::character varying), CASE WHEN ((cpt_group IS NULL) OR ((cpt_group)::text = ''::text)) THEN '0'::text ELSE split_part((cpt_group)::text, '-'::text, 1) END, CASE WHEN ((cpt_group IS NULL) OR ((cpt_group)::text = ''::text)) THEN '0'::text ELSE split_part((cpt_group)::text, '-'::text, 2) END, CASE WHEN (erp_code IS NULL) THEN 'na'::text ELSE pg_catalog.concat((COALESCE(btrim((state_id)::text), 'na'::text))::character varying, '-', COALESCE((erp_code)::character varying, 'na'::character varying), '-', COALESCE((mlp_code)::character varying, 'na'::character varying)) END, COALESCE(erp_attnd, 'n'::character varying), state_id, case_num, unique_rec_id, units, total_amt, total_rvu, budget_rvu, work_rvu, payroll_rvu, insert_ts, visit_date"
"              Buffers: shared hit=866434"
"Total runtime: 1450131.131 ms"

Answer 1

我做了表面优化:

SELECT
    concat(coalesce(state_id, 'na'), '-', case_num),
    visit_date,
    coalesce(payor_code, 0),
    coalesce(los, 'other'),
    case when cpt_group_1 is null or cpt_group = ''
        then '0'
        else cpt_group_1
    end,
    case when cpt_group_2 is null or cpt_group = ''
        then '0'
        else cpt_group_2
    end,
    erp_code ,
    coalesce(erp_attnd, 'n'),
    count_case_num,
    rec_id,
    units,
    total_amt,
    total_rvu,
    budget_rvu,
    work_rvu,
    payroll_rvu,
    days
from (
    select
        tablea.state_id,
        CASE WHEN tablea.case_num between 100000000000 and 999999999999
            THEN left(cast(tablea.case_num as text), 2)
            else left(cast(tablea.case_num as text), 1)
        END case_num,
        date_trunc('month', tablea.visit_date) visit_date,
        tablea.payor_code,
        tablea.los,
        split_part(tablea.cpt_group, '-', 1) cpt_group_1,
        split_part(tablea.cpt_group, '-', 2) cpt_group_2,
        case when tablea.erp_code is null
            then 'na'
            else concat(
                coalesce(tablea.state_id, 'na'),
                '-',
                coalesce(cast(tablea.erp_code as text), 'na'),
                '-',
                coalesce(cast(tablea.mlp_code as text), 'na')
                )
        end erp_code,
        tablea.erp_attnd,
        count(distinct (tablea.state_id, tablea.case_num)) count_case_num,
        count(unique_rec_id) rec_id,
        sum(tablea.units) units,
        sum(tablea.total_amt) total_amt,
        sum(tablea.total_rvu) total_rvu,
        sum(tablea.budget_rvu) budget_rvu,
        sum(tablea.work_rvu) work_rvu,
        sum(tablea.payroll_rvu) payroll_rvu,
        sum(extract(day from charges.insert_ts - charges.visit_date)) days
    FROM tablea
    GROUP BY 1, 2, 3, 4, 5, 6 ,7 ,8, 9
) s

你担心的部分我改自:

count(distinct concat(tablea.state_id,tablea.case_num))

到:

count(distinct (tablea.state_id, tablea.case_num))