[Solved] PostgreSQL: Dirtied blocks when using many OR conditions in query

EverSQL Database Performance Knowledge Base

PostgreSQL: Dirtied blocks when using many OR conditions in query

Database type:

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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
SELECT
        * 
    FROM
        limits_actions 
    WHERE
        (
            limits_actions.createcoupon_limitval > 0 
            OR limits_actions.redeemcoupon_limitval > 0 
            OR limits_actions.setdiscount_limitval > 0 
            OR limits_actions.setdiscounteffect_limitval > 0 
            OR limits_actions.customeffect_limitval > 0 
            OR limits_actions.createloyaltypoints_limitval > 0 
            OR limits_actions.createloyaltypointseffect_limitval > 0 
            OR limits_actions.redeemloyaltypoints_limitval > 0 
            OR limits_actions.redeemloyaltypointseffect_limitval > 0 
            OR limits_actions.callapi_limitval > 0 
            OR limits_actions.awardgiveaway_limitval > 0 
            OR limits_actions.addfreeitemeffect_limitval > 0
        )

Related Articles



* original question posted on StackOverflow here.