Created
March 25, 2026 11:34
-
-
Save adetokunbo/b0a1ac637e7f2ded90ffc67113d4dc78 to your computer and use it in GitHub Desktop.
EXPLAIN ANALYZE — computeRewardTotals (combined CTE with RETURNING)
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 — combined computeRewardTotals CTE | |
| Date: 2026-03-25 | |
| Setup: 10000 activity records, 100 distinct parties, round=42 | |
| ================================================================================ | |
| EXPLAIN ANALYZE — combined computeRewardTotals (round=42, historyId=1) | |
| ================================================================================ | |
| Insert on app_reward_round_totals (cost=8.27..8.29 rows=0 width=0) (actual time=0.218..0.219 rows=0 loops=1) | |
| CTE computed | |
| -> Index Scan using uq_app_activity_party on app_activity_party_totals (cost=0.15..8.18 rows=1 width=52) (actual time=0.012..0.034 rows=100 loops=1) | |
| Index Cond: ((history_id = '1'::bigint) AND (round_number = '42'::bigint)) | |
| CTE inserted_parties | |
| -> Insert on app_reward_party_totals (cost=0.00..0.02 rows=1 width=50) (actual time=0.051..0.051 rows=0 loops=1) | |
| -> CTE Scan on computed (cost=0.00..0.02 rows=1 width=50) (actual time=0.051..0.051 rows=0 loops=1) | |
| Filter: (reward_amount >= 0.5) | |
| Rows Removed by Filter: 100 | |
| -> Subquery Scan on "*SELECT*" (cost=0.07..0.08 rows=1 width=114) (actual time=0.070..0.071 rows=1 loops=1) | |
| -> Aggregate (cost=0.07..0.07 rows=1 width=92) (actual time=0.069..0.069 rows=1 loops=1) | |
| InitPlan 3 | |
| -> Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1) | |
| -> CTE Scan on computed computed_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.006 rows=100 loops=1) | |
| Filter: (reward_amount < 0.5) | |
| -> CTE Scan on inserted_parties (cost=0.00..0.02 rows=1 width=30) (actual time=0.052..0.052 rows=0 loops=1) | |
| Planning Time: 0.338 ms | |
| Execution Time: 0.269 ms | |
| ================================================================================ | |
| Summary: | |
| - Single index scan on uq_app_activity_party(history_id, round_number) | |
| - computed CTE materialized once, reused by inserted_parties and thresholded subquery | |
| - No second pass over the table (improvement over two separate queries) | |
| - 0.269ms total execution (vs 0.099ms + 0.410ms = 0.509ms for two separate queries) | |
| Comparison with separate queries (same session): | |
| - Separate: insertPartyRewards 0.099ms + insertRoundRewards 0.410ms = 0.509ms | |
| - Combined: 0.269ms (~47% faster) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment