PostgreSQL: Dirtied blocks when using many OR conditions in query-CodePudding

I'm using PostgreSQL 13.7.

When trying to optimize a query that is made of several sub-queries, I noticed that reducing the number of OR clauses in the final part significantly improves performance (800ms to 70ms). The part in question is this (only a partial segment, the full query along with analyze reports will be added towards the end):

    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);

Note that limit_actions is the result of a subquery that would have only few rows. This final part was a sort of optimization added in order to not fetch unnecessary rows and save on transport of data. As soon as I reduce the number of OR conditions to 4 or less, I see a massive improvement.

EXPLAIN ANALYZing both variants shows that whenever there are more than 4 OR clauses the query is causing dirty blocks. Mainly the difference is:

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

I would like to understand how and why this happens. Especially how the OR clauses can have such effect. From my limited understanding, dirtied blocks means invalidated cache. Is that correct?

Here's the entire query I'm executing for completeness:

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);

And the analyze result:

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

Here's the same query with some of the final OR clauses removed:

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);

And the analyze result:

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

CodePudding user response：

The real problem with your performance is just-in-time compilation. It dominates your run time in both queries. Turn jit off. Then if you still have a question left, post the new plans after jit is turned off. (The plans probably won't change, but the timings will).

Also, repeat the queries in both orders several times. It looks like the 2nd query is faster merely because the first one warmed up the cache with the data the 2nd one would need. The number of OR clauses likely has nothing to do with this, it is just that you happened to run them in a certain order. Not only does the first warm the cache, but it also sets the hint bits (which is what leads to the dirtied pages). It also seems to warm whatever cache the JIT feature relies on (which is not under PostgreSQL's control as far as I know)