Created
March 26, 2026 05:35
-
-
Save adetokunbo/bc1f39d18e2e7abad1843e8c3a223a12 to your computer and use it in GitHub Desktop.
EXPLAIN ANALYZE — computeRewardTotals restructured (no second CTE pass)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| EXPLAIN ANALYZE — computeRewardTotals restructured (no second pass through computed CTE) | |
| Date: 2026-03-26 | |
| Setup: 10000 activity records, 100 distinct parties, round=42 | |
| Query: thresholded = totalIssuance - unclaimed - mintingAllowance (inline arithmetic) | |
| ================================================================================ | |
| EXPLAIN ANALYZE — computeRewardTotals restructured (round=42, historyId=1) | |
| ================================================================================ | |
| Insert on app_reward_round_totals (cost=8.22..8.24 rows=0 width=0) (actual time=0.266..0.267 rows=0 loops=1) | |
| CTE inserted_parties | |
| -> Insert on app_reward_party_totals (cost=0.15..8.20 rows=1 width=50) (actual time=0.058..0.059 rows=0 loops=1) | |
| -> Index Scan using uq_app_activity_party on app_activity_party_totals (cost=0.15..8.20 rows=1 width=50) (actual time=0.058..0.058 rows=0 loops=1) | |
| Index Cond: ((history_id = '1'::bigint) AND (round_number = '42'::bigint)) | |
| Filter: ((((total_app_activity_weight)::numeric(38,10) / 1000000.0) * 2.0) >= 0.5) | |
| Rows Removed by Filter: 100 | |
| -> Subquery Scan on "*SELECT*" (cost=0.03..0.05 rows=1 width=114) (actual time=0.065..0.066 rows=1 loops=1) | |
| -> Aggregate (cost=0.03..0.04 rows=1 width=120) (actual time=0.063..0.064 rows=1 loops=1) | |
| -> CTE Scan on inserted_parties (cost=0.00..0.02 rows=1 width=30) (actual time=0.059..0.059 rows=0 loops=1) | |
| Planning Time: 0.463 ms | |
| Execution Time: 0.326 ms | |
| ================================================================================ | |
| Summary: | |
| - Single index scan on uq_app_activity_party(history_id, round_number) | |
| - `computed` CTE is gone — filter applied directly in inserted_parties index scan | |
| - No second pass through computed (the old InitPlan 3 subquery is eliminated) | |
| - thresholded computed via Scala constants: totalIssuance - unclaimed - sum(minting) | |
| - 0.326ms total execution | |
| Comparison with previous combined CTE (2026-03-25, same setup): | |
| - Previous (with second scan through computed for thresholded): 0.269ms | |
| - Restructured (no second scan, inline arithmetic): 0.326ms | |
| - Performance is comparable; the key improvement is correctness (uses actual | |
| totalIssuance and unclaimed values from RewardIssuanceParams rather than | |
| re-scanning computed or hardcoding 0). | |
| Comparison with original separate queries (2026-03-25): | |
| - Separate: insertPartyRewards 0.099ms + insertRoundRewards 0.410ms = 0.509ms | |
| - Restructured combined: 0.326ms (~36% faster than separate) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment