postgresql - PostgreSQL:在查询中使用多个 OR 条件时出现脏块

我正在使用 PostgreSQL 13.7。

在尝试优化由多个子查询组成的查询时,我注意到减少最后部分的 OR 子句的数量可以显着提高性能(从 800 毫秒到 70 毫秒)。有问题的部分是这样的(只有部分片段,完整的查询以及分析报告将在最后添加):

SELECT
        *
    FROM
        limits_actions
    WHERE (createcoupon_limitval > 0
        OR redeemcoupon_limitval > 0
        OR setdiscount_limitval > 0
        OR setdiscounteffect_limitval > 0
        OR customeffect_limitval > 0
        OR createloyaltypoints_limitval > 0
        OR createloyaltypointseffect_limitval > 0
        OR redeemloyaltypoints_limitval > 0
        OR redeemloyaltypointseffect_limitval > 0
        OR callapi_limitval > 0
        OR awardgiveaway_limitval > 0
        OR addfreeitemeffect_limitval > 0);

请注意,limit_actions 是只有几行的子查询的结果。最后一部分是为了不获取不必要的行并节省数据传输而添加的一种优化。一旦我将 OR 条件的数量减少到 4 个或更少,我就会看到巨大的改进。

解释分析这两种变体表明,只要有超过 4 个 OR 子句,查询就会导致脏块。主要区别在于:

More than 4 OR clauses:
    Shared Hit Blocks       113
    Shared Read Blocks      163
    Shared Dirtied Blocks   65
--------------------------------
4 OR clauses or less:
    Shared Hit Blocks       259
    Shared Read Blocks      0
    Shared Dirtied Blocks   0

我想了解这是如何以及为什么会发生的。尤其是 OR 子句如何产生这样的效果。根据我有限的理解,脏块意味着无效的缓存。那是对的吗?

为了完整起见,这是我正在执行的整个查询:

EXPLAIN (ANALYZE,
    COSTS,
    VERBOSE,
    BUFFERS
)
WITH campaign_limits AS (
    SELECT
        id,
        action,
        campaignid,
        couponid,
        referralid,
        profileid,
        counter,
        limitval,
        identifier
    FROM
        limit_counters
    WHERE
        campaignid IN(789, 793, 726, 727, 890, 790, 785, 794, 781, 786, 792, 832, 772, 903, 992, 791, 787, 771, 963, 784, 775, 776, 779, 926, 749, 889, 1010, 1011, 788, 783, 782, 984, 780, 396, 725, 445, 773, 763, 770, 778, 993, 1019, 1021, 1022)
        AND couponid IS NULL
        AND identifier IS NULL
        AND profileid IS NULL
        AND referralid IS NULL
    ORDER BY
        action ASC), -- O(rows*)
    limits_actions AS (
        SELECT
            campaignid,
            sum(
                CASE WHEN action = 'createCoupon' THEN
                    limitval
                ELSE
                    0
                END) AS createcoupon_limitval,
            sum(
                CASE WHEN action = 'createCoupon' THEN
                    counter
                ELSE
                    0
                END) AS createcoupon_counter,
            sum(
                CASE WHEN action = 'createReferral' THEN
                    limitval
                ELSE
                    0
                END) AS createreferral_limitval,
            sum(
                CASE WHEN action = 'createReferral' THEN
                    counter
                ELSE
                    0
                END) AS createreferral_counter,
            sum(
                CASE WHEN action = 'redeemCoupon' THEN
                    limitval
                ELSE
                    0
                END) AS redeemcoupon_limitval,
            sum(
                CASE WHEN action = 'redeemCoupon' THEN
                    counter
                ELSE
                    0
                END) AS redeemcoupon_counter,
            sum(
                CASE WHEN action = 'redeemReferral' THEN
                    limitval
                ELSE
                    0
                END) AS redeemreferral_limitval,
            sum(
                CASE WHEN action = 'redeemReferral' THEN
                    counter
                ELSE
                    0
                END) AS redeemreferral_counter,
            sum(
                CASE WHEN action = 'setDiscount' THEN
                    limitval
                ELSE
                    0
                END) AS setdiscount_limitval,
            sum(
                CASE WHEN action = 'setDiscount' THEN
                    counter
                ELSE
                    0
                END) AS setdiscount_counter,
            sum(
                CASE WHEN action = 'setDiscountEffect' THEN
                    limitval
                ELSE
                    0
                END) AS setdiscounteffect_limitval,
            sum(
                CASE WHEN action = 'setDiscountEffect' THEN
                    counter
                ELSE
                    0
                END) AS setdiscounteffect_counter,
            sum(
                CASE WHEN action = 'createLoyaltyPoints' THEN
                    limitval
                ELSE
                    0
                END) AS createloyaltypoints_limitval,
            sum(
                CASE WHEN action = 'createLoyaltyPoints' THEN
                    counter
                ELSE
                    0
                END) AS createloyaltypoints_counter,
            sum(
                CASE WHEN action = 'createLoyaltyPointsEffect' THEN
                    limitval
                ELSE
                    0
                END) AS createloyaltypointseffect_limitval,
            sum(
                CASE WHEN action = 'createLoyaltyPointsEffect' THEN
                    counter
                ELSE
                    0
                END) AS createloyaltypointseffect_counter,
            sum(
                CASE WHEN action = 'customEffect' THEN
                    limitval
                ELSE
                    0
                END) AS customeffect_limitval,
            sum(
                CASE WHEN action = 'customEffect' THEN
                    counter
                ELSE
                    0
                END) AS customeffect_counter,
            sum(
                CASE WHEN action = 'callApi' THEN
                    limitval
                ELSE
                    0
                END) AS callapi_limitval, sum(
                CASE WHEN action = 'callApi' THEN
                    counter
                ELSE
                    0
                END) AS callapi_counter, sum(
                CASE WHEN action = 'redeemLoyaltyPoints' THEN
                    limitval
                ELSE
                    0
                END) AS redeemloyaltypoints_limitval, sum(
                CASE WHEN action = 'redeemLoyaltyPoints' THEN
                    counter
                ELSE
                    0
                END) AS redeemloyaltypoints_counter, sum(
                CASE WHEN action = 'redeemLoyaltyPointsEffect' THEN
                    limitval
                ELSE
                    0
                END) AS redeemloyaltypointseffect_limitval, sum(
                CASE WHEN action = 'redeemLoyaltyPointsEffect' THEN
                    counter
                ELSE
                    0
                END) AS redeemloyaltypointseffect_counter, sum(
                CASE WHEN action = 'awardGiveaway' THEN
                    limitval
                ELSE
                    0
                END) AS awardgiveaway_limitval, sum(
                CASE WHEN action = 'awardGiveaway' THEN
                    counter
                ELSE
                    0
                END) AS awardgiveaway_counter, sum(
                CASE WHEN action = 'addFreeItem' THEN
                    limitval
                ELSE
                    0
                END) AS addfreeitemeffect_limitval, sum(
                CASE WHEN action = 'addFreeItem' THEN
                    counter
                ELSE
                    0
                END) AS addfreeitemeffect_counter
        FROM
            campaign_limits
        GROUP BY
            campaignid
)
    SELECT
        *
    FROM
        limits_actions
    WHERE (createcoupon_limitval > 0
        OR redeemcoupon_limitval > 0
        OR setdiscount_limitval > 0
        OR setdiscounteffect_limitval > 0
        OR customeffect_limitval > 0
        OR createloyaltypoints_limitval > 0
        OR createloyaltypointseffect_limitval > 0
        OR redeemloyaltypoints_limitval > 0
        OR redeemloyaltypointseffect_limitval > 0
        OR callapi_limitval > 0
        OR awardgiveaway_limitval > 0
        OR addfreeitemeffect_limitval > 0);

以及分析结果:

HashAggregate  (cost=542192.34..542207.69 rows=340 width=232) (actual time=759.467..759.502 rows=41 loops=1)
"  Output: limit_counters.campaignid, sum(CASE WHEN (limit_counters.action = 'createCoupon'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createCoupon'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createReferral'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createReferral'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemCoupon'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemCoupon'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemReferral'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemReferral'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'setDiscount'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'setDiscount'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'setDiscountEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'setDiscountEffect'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createLoyaltyPoints'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createLoyaltyPoints'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createLoyaltyPointsEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createLoyaltyPointsEffect'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'customEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'customEffect'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'callApi'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'callApi'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPoints'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPoints'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPointsEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPointsEffect'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'awardGiveaway'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'awardGiveaway'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'addFreeItem'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'addFreeItem'::text) THEN limit_counters.counter ELSE '0'::double precision END)"
  Group Key: limit_counters.campaignid
"  Filter: ((sum(CASE WHEN (limit_counters.action = 'createCoupon'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'redeemCoupon'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'createReferral'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'redeemReferral'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'setDiscount'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'setDiscountEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'customEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'createLoyaltyPoints'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'createLoyaltyPointsEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPoints'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPointsEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'callApi'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'awardGiveaway'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'addFreeItem'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision))"
  Batches: 1  Memory Usage: 61kB
  Rows Removed by Filter: 3
  Buffers: shared hit=270
  ->  Sort  (cost=331926.02..334262.31 rows=934517 width=97) (actual time=759.062..759.086 rows=300 loops=1)
        Output: NULL::integer, limit_counters.action, limit_counters.campaignid, NULL::bigint, NULL::bigint, NULL::bigint, limit_counters.counter, limit_counters.limitval, NULL::text
        Sort Key: limit_counters.action
        Sort Method: quicksort  Memory: 53kB
        Buffers: shared hit=270
        ->  Index Scan using limit_counters_non_nulls_campaignid_idx on public.limit_counters  (cost=0.28..210046.61 rows=934517 width=97) (actual time=758.530..758.895 rows=300 loops=1)
              Output: NULL::integer, limit_counters.action, limit_counters.campaignid, NULL::bigint, NULL::bigint, NULL::bigint, limit_counters.counter, limit_counters.limitval, NULL::text
"              Index Cond: (limit_counters.campaignid = ANY ('{789,793,726,727,890,790,785,794,781,786,792,832,772,903,992,791,787,771,963,784,775,776,779,926,749,889,1010,1011,788,783,782,984,780,396,725,445,773,763,770,778,993,1019,1021,1022}'::bigint[]))"
              Buffers: shared hit=270
Planning:
  Buffers: shared hit=1
Planning Time: 0.707 ms
JIT:
  Functions: 12
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 5.870 ms, Inlining 16.245 ms, Optimization 378.807 ms, Emission 363.469 ms, Total 764.391 ms
Execution Time: 765.627 ms

这是删除了一些最终 OR 子句的相同查询:

EXPLAIN (ANALYZE,
    COSTS,
    VERBOSE,
    BUFFERS
)
WITH campaign_limits AS (
    SELECT
        id,
        action,
        campaignid,
        couponid,
        referralid,
        profileid,
        counter,
        limitval,
        identifier
    FROM
        limit_counters
    WHERE
        campaignid IN(789, 793, 726, 727, 890, 790, 785, 794, 781, 786, 792, 832, 772, 903, 992, 791, 787, 771, 963, 784, 775, 776, 779, 926, 749, 889, 1010, 1011, 788, 783, 782, 984, 780, 396, 725, 445, 773, 763, 770, 778, 993, 1019, 1021, 1022)
        AND couponid IS NULL
        AND identifier IS NULL
        AND profileid IS NULL
        AND referralid IS NULL
    ORDER BY
        action ASC),
    limits_actions AS (
        SELECT
            campaignid,
            sum(
                CASE WHEN action = 'createCoupon' THEN
                    limitval
                ELSE
                    0
                END) AS createcoupon_limitval,
            sum(
                CASE WHEN action = 'createCoupon' THEN
                    counter
                ELSE
                    0
                END) AS createcoupon_counter,
            sum(
                CASE WHEN action = 'createReferral' THEN
                    limitval
                ELSE
                    0
                END) AS createreferral_limitval,
            sum(
                CASE WHEN action = 'createReferral' THEN
                    counter
                ELSE
                    0
                END) AS createreferral_counter,
            sum(
                CASE WHEN action = 'redeemCoupon' THEN
                    limitval
                ELSE
                    0
                END) AS redeemcoupon_limitval,
            sum(
                CASE WHEN action = 'redeemCoupon' THEN
                    counter
                ELSE
                    0
                END) AS redeemcoupon_counter,
            sum(
                CASE WHEN action = 'redeemReferral' THEN
                    limitval
                ELSE
                    0
                END) AS redeemreferral_limitval,
            sum(
                CASE WHEN action = 'redeemReferral' THEN
                    counter
                ELSE
                    0
                END) AS redeemreferral_counter,
            sum(
                CASE WHEN action = 'setDiscount' THEN
                    limitval
                ELSE
                    0
                END) AS setdiscount_limitval,
            sum(
                CASE WHEN action = 'setDiscount' THEN
                    counter
                ELSE
                    0
                END) AS setdiscount_counter,
            sum(
                CASE WHEN action = 'setDiscountEffect' THEN
                    limitval
                ELSE
                    0
                END) AS setdiscounteffect_limitval,
            sum(
                CASE WHEN action = 'setDiscountEffect' THEN
                    counter
                ELSE
                    0
                END) AS setdiscounteffect_counter,
            sum(
                CASE WHEN action = 'createLoyaltyPoints' THEN
                    limitval
                ELSE
                    0
                END) AS createloyaltypoints_limitval,
            sum(
                CASE WHEN action = 'createLoyaltyPoints' THEN
                    counter
                ELSE
                    0
                END) AS createloyaltypoints_counter,
            sum(
                CASE WHEN action = 'createLoyaltyPointsEffect' THEN
                    limitval
                ELSE
                    0
                END) AS createloyaltypointseffect_limitval,
            sum(
                CASE WHEN action = 'createLoyaltyPointsEffect' THEN
                    counter
                ELSE
                    0
                END) AS createloyaltypointseffect_counter,
            sum(
                CASE WHEN action = 'customEffect' THEN
                    limitval
                ELSE
                    0
                END) AS customeffect_limitval,
            sum(
                CASE WHEN action = 'customEffect' THEN
                    counter
                ELSE
                    0
                END) AS customeffect_counter,
            sum(
                CASE WHEN action = 'callApi' THEN
                    limitval
                ELSE
                    0
                END) AS callapi_limitval, sum(
                CASE WHEN action = 'callApi' THEN
                    counter
                ELSE
                    0
                END) AS callapi_counter, sum(
                CASE WHEN action = 'redeemLoyaltyPoints' THEN
                    limitval
                ELSE
                    0
                END) AS redeemloyaltypoints_limitval, sum(
                CASE WHEN action = 'redeemLoyaltyPoints' THEN
                    counter
                ELSE
                    0
                END) AS redeemloyaltypoints_counter, sum(
                CASE WHEN action = 'redeemLoyaltyPointsEffect' THEN
                    limitval
                ELSE
                    0
                END) AS redeemloyaltypointseffect_limitval, sum(
                CASE WHEN action = 'redeemLoyaltyPointsEffect' THEN
                    counter
                ELSE
                    0
                END) AS redeemloyaltypointseffect_counter, sum(
                CASE WHEN action = 'awardGiveaway' THEN
                    limitval
                ELSE
                    0
                END) AS awardgiveaway_limitval, sum(
                CASE WHEN action = 'awardGiveaway' THEN
                    counter
                ELSE
                    0
                END) AS awardgiveaway_counter, sum(
                CASE WHEN action = 'addFreeItem' THEN
                    limitval
                ELSE
                    0
                END) AS addfreeitemeffect_limitval, sum(
                CASE WHEN action = 'addFreeItem' THEN
                    counter
                ELSE
                    0
                END) AS addfreeitemeffect_counter
        FROM
            campaign_limits
        GROUP BY
            campaignid
)
    SELECT
        *
    FROM
        limits_actions
    WHERE (createcoupon_limitval > 0
        OR redeemcoupon_limitval > 0
        OR createreferral_limitval > 0
        OR redeemreferral_limitval > 0);

以及分析结果:

HashAggregate  (cost=495466.49..495473.31 rows=274 width=232) (actual time=46.782..46.817 rows=38 loops=1)
"  Output: limit_counters.campaignid, sum(CASE WHEN (limit_counters.action = 'createCoupon'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createCoupon'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createReferral'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createReferral'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemCoupon'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemCoupon'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemReferral'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemReferral'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'setDiscount'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'setDiscount'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'setDiscountEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'setDiscountEffect'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createLoyaltyPoints'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createLoyaltyPoints'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createLoyaltyPointsEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'createLoyaltyPointsEffect'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'customEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'customEffect'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'callApi'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'callApi'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPoints'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPoints'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPointsEffect'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'redeemLoyaltyPointsEffect'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'awardGiveaway'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'awardGiveaway'::text) THEN limit_counters.counter ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'addFreeItem'::text) THEN limit_counters.limitval ELSE '0'::double precision END), sum(CASE WHEN (limit_counters.action = 'addFreeItem'::text) THEN limit_counters.counter ELSE '0'::double precision END)"
  Group Key: limit_counters.campaignid
"  Filter: ((sum(CASE WHEN (limit_counters.action = 'createCoupon'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'redeemCoupon'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'createReferral'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision) OR (sum(CASE WHEN (limit_counters.action = 'redeemReferral'::text) THEN limit_counters.limitval ELSE '0'::double precision END) > '0'::double precision))"
  Batches: 1  Memory Usage: 61kB
  Rows Removed by Filter: 6
  Buffers: shared hit=270
  ->  Sort  (cost=331926.02..334262.31 rows=934517 width=97) (actual time=46.287..46.310 rows=300 loops=1)
        Output: NULL::integer, limit_counters.action, limit_counters.campaignid, NULL::bigint, NULL::bigint, NULL::bigint, limit_counters.counter, limit_counters.limitval, NULL::text
        Sort Key: limit_counters.action
        Sort Method: quicksort  Memory: 53kB
        Buffers: shared hit=270
        ->  Index Scan using limit_counters_non_nulls_campaignid_idx on public.limit_counters  (cost=0.28..210046.61 rows=934517 width=97) (actual time=45.651..46.120 rows=300 loops=1)
              Output: NULL::integer, limit_counters.action, limit_counters.campaignid, NULL::bigint, NULL::bigint, NULL::bigint, limit_counters.counter, limit_counters.limitval, NULL::text
"              Index Cond: (limit_counters.campaignid = ANY ('{789,793,726,727,890,790,785,794,781,786,792,832,772,903,992,791,787,771,963,784,775,776,779,926,749,889,1010,1011,788,783,782,984,780,396,725,445,773,763,770,778,993,1019,1021,1022}'::bigint[]))"
              Buffers: shared hit=270
Planning:
  Buffers: shared hit=1
Planning Time: 0.630 ms
JIT:
  Functions: 12
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 5.904 ms, Inlining 0.000 ms, Optimization 1.310 ms, Emission 44.317 ms, Total 51.531 ms
Execution Time: 52.933 ms

回答1

性能的真正问题是即时编译。它在两个查询中都支配着您的运行时间。关闭 jit。然后,如果您还有问题,请在关闭 jit 后发布新计划。 (计划可能不会改变,但时间会改变)。

此外,多次重复这两个顺序的查询。看起来第二个查询更快,仅仅是因为第一个查询用第二个需要的数据预热了缓存。 OR 子句的数量可能与此无关,只是您碰巧按特定顺序运行它们。第一个不仅预热缓存,而且还设置提示位(这是导致脏页的原因)。它似乎还加热了 JIT 功能所依赖的任何缓存(据我所知,它不受 PostgreSQL 的控制)

相似文章