0

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
Alechko
  • 1,406
  • 1
  • 13
  • 27

1 Answers1

2

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)

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • I will look into turning off just in time compilation. Regarding repeating the queries and warm cache - I've retried this over and over with same results. Reducing the amount of OR clauses to 4 or less (doesn't matter which ones) produces for better performance. – Alechko May 18 '22 at 14:11
  • can you please refer me to a source that explains JIT and why turning it off might help my queries to run faster? – Alechko May 18 '22 at 14:14
  • 1
    Look at the time it took JIT do do its optimizations, right at the end of your `EXPLAIN` output. – Laurenz Albe May 18 '22 at 15:11
  • I see. This is strange! My service is hosted on Aiven. I just reached out to them and checked the configuration. It seems that JIT is not enabled for this service. That said, I can't argue with the output from EXPLAIN. I will dig in more. Thanks for the advice. – Alechko May 18 '22 at 15:42
  • Since I'm pretty sure about the difference in performance when I disable some or all of the OR clauses in the query and as you say it is related to JIT - could you please point me towards some place where I can educate myself about how and why this happens? Most of what I find online explains JIT in general but doesn't focus on how queries can affect its behavior (or vice versa). – Alechko May 18 '22 at 16:07
  • JIT in the context of PostgreSQL is documented at https://www.postgresql.org/docs/current/jit.html. But what I know about is mostly from bitter experience, not from documentation. – jjanes May 18 '22 at 17:15
  • I was confused because the host of our database advertised that JIT is disabled by default, but apparently it became enabled by default in some PG version. I disabled JIT manually and the issue went away. The reason that additional elements in the OR clause triggered the issue is because JIT was activated on queries that have a complexity value higher than some threshold... – Alechko Jun 14 '22 at 12:50