Changes between Version 15 and Version 16 of OtherTopics


Ignore:
Timestamp:
09/15/26 06:12:20 (12 days ago)
Author:
232012
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v15 v16  
    10941094== Scenario 4 - Customer Habits and Points Spending
    10951095
     1096==== Query used
     1097
     1098{{{
     1099SET search_path TO project;
     1100
     1101EXPLAIN (ANALYZE, BUFFERS)
     1102WITH order_totals AS (
     1103    SELECT
     1104        order_id,
     1105        SUM(quantity * price_at_purchase) AS order_spend
     1106    FROM order_products
     1107    GROUP BY order_id
     1108),
     1109
     1110user_purchase_metrics AS (
     1111    SELECT
     1112        o.user_id,
     1113        COUNT(o.order_id) AS total_orders_placed,
     1114        COALESCE(SUM(o.points_earned), 0) AS lifetime_points_earned,
     1115        COALESCE(SUM(o.points_used), 0) AS lifetime_points_burned,
     1116        COALESCE(SUM(ot.order_spend), 0.00) AS total_monetary_spend
     1117
     1118    FROM orders o
     1119
     1120    LEFT JOIN order_totals ot
     1121        ON o.order_id = ot.order_id
     1122
     1123    WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '2 years'
     1124      AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
     1125
     1126    GROUP BY o.user_id
     1127),
     1128
     1129promo_summary AS (
     1130    SELECT
     1131        u.user_id,
     1132        u.username,
     1133        u.email,
     1134        EXTRACT(YEAR FROM u.date_created) AS account_creation_vintage,
     1135        u.date_created AS registration_date,
     1136        COALESCE(upm.total_orders_placed, 0) AS orders_count_2yr,
     1137        ROUND(COALESCE(upm.total_monetary_spend, 0.00), 2) AS lifetime_spend_amount,
     1138        c.points_collected AS current_unspent_points_balance,
     1139        COALESCE(upm.lifetime_points_earned, 0) AS points_earned_2yr,
     1140        COALESCE(upm.lifetime_points_burned, 0) AS points_burned_2yr
     1141
     1142    FROM consumers c
     1143    JOIN users u
     1144        ON c.user_id = u.user_id
     1145    LEFT JOIN user_purchase_metrics upm
     1146        ON c.user_id = upm.user_id
     1147
     1148    WHERE u.date_created >= CURRENT_DATE - INTERVAL '2 years'
     1149),
     1150
     1151promo_summary_with_flags AS (
     1152    SELECT
     1153        *,
     1154        CASE
     1155            WHEN current_unspent_points_balance >= 2000
     1156             AND orders_count_2yr >= 10
     1157                THEN 1
     1158            ELSE 0
     1159        END AS is_vip,
     1160
     1161        CASE
     1162            WHEN current_unspent_points_balance >= 1000
     1163             AND points_burned_2yr = 0
     1164                THEN 1
     1165            ELSE 0
     1166        END AS is_hoarder,
     1167
     1168        CASE
     1169            WHEN points_burned_2yr > 0
     1170             AND orders_count_2yr <= 2
     1171                THEN 1
     1172            ELSE 0
     1173        END AS is_churned
     1174
     1175    FROM promo_summary
     1176)
     1177
     1178SELECT
     1179    user_id,
     1180    username,
     1181    email,
     1182    account_creation_vintage,
     1183    registration_date,
     1184    orders_count_2yr,
     1185    lifetime_spend_amount,
     1186    current_unspent_points_balance,
     1187    points_earned_2yr,
     1188    points_burned_2yr,
     1189
     1190    CASE
     1191        WHEN is_vip = 1
     1192            THEN 'VIP Tier: High Future Discount Liability'
     1193        WHEN is_hoarder = 1
     1194            THEN 'Points Hoarder: Inactive Burn (High Risk)'
     1195        WHEN is_churned = 1
     1196            THEN 'Churned After Reward Use (No Retention)'
     1197        ELSE 'Standard Active Engagement Profile'
     1198    END AS customer_retention_segment
     1199
     1200FROM promo_summary_with_flags
     1201ORDER BY
     1202    account_creation_vintage DESC,
     1203    current_unspent_points_balance DESC;
     1204}}}
     1205
     1206
    10961207==== Without indexes
    10971208
    … …  
    10991210
    11001211{{{
    1101 Seq Scan on orders
    1102 rows=5009
    1103 Rows Removed by Filter: 20008
    1104 
    1105 Seq Scan on order_products
    1106 rows=75021
     1212Sort  (cost=3596.41..3597.95 rows=615 width=252) (actual time=89.786..89.792 rows=12 loops=1)
     1213  Sort Key: (EXTRACT(year FROM u.date_created)) DESC, c.points_collected DESC
     1214  Sort Method: quicksort  Memory: 26kB
     1215  Buffers: shared hit=763, temp read=91 written=161
     1216  ->  Hash Left Join  (cost=3516.03..3567.92 rows=615 width=252) (actual time=89.743..89.765 rows=12 loops=1)
     1217        Hash Cond: (c.user_id = upm.user_id)
     1218        Buffers: shared hit=763, temp read=91 written=161
     1219        ->  Hash Join  (cost=18.66..52.03 rows=615 width=92) (actual time=0.048..0.060 rows=12 loops=1)
     1220              Hash Cond: (c.user_id = u.user_id)
     1221              Buffers: shared hit=2
     1222              ->  Seq Scan on consumers c  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.014..0.018 rows=12 loops=1)
     1223                    Buffers: shared hit=1
     1224              ->  Hash  (cost=17.00..17.00 rows=133 width=76) (actual time=0.025..0.026 rows=13 loops=1)
     1225                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
     1226                    Buffers: shared hit=1
     1227                    ->  Seq Scan on users u  (cost=0.00..17.00 rows=133 width=76) (actual time=0.012..0.017 rows=13 loops=1)
     1228                          Filter: (date_created >= (CURRENT_DATE - '2 years'::interval))
     1229                          Buffers: shared hit=1
     1230        ->  Hash  (cost=3497.20..3497.20 rows=13 width=112) (actual time=89.681..89.682 rows=12 loops=1)
     1231              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     1232              Buffers: shared hit=761, temp read=91 written=161
     1233              ->  Subquery Scan on upm  (cost=3496.84..3497.20 rows=13 width=112) (actual time=89.664..89.675 rows=12 loops=1)
     1234                    Buffers: shared hit=761, temp read=91 written=161
     1235                    ->  HashAggregate  (cost=3496.84..3497.07 rows=13 width=112) (actual time=89.663..89.671 rows=12 loops=1)
     1236                          Group Key: o.user_id
     1237                          Batches: 1  Memory Usage: 24kB
     1238                          Buffers: shared hit=761, temp read=91 written=161
     1239                          ->  Hash Right Join  (cost=2830.77..3459.11 rows=3019 width=64) (actual time=64.469..88.254 rows=5009 loops=1)
     1240                                Hash Cond: (order_products.order_id = o.order_id)
     1241                                Buffers: shared hit=761, temp read=91 written=161
     1242                                ->  HashAggregate  (cost=2052.42..2365.02 rows=25008 width=40) (actual time=58.905..78.279 rows=25017 loops=1)
     1243                                      Group Key: order_products.order_id
     1244                                      Batches: 5  Memory Usage: 8241kB  Disk Usage: 760kB
     1245                                      Buffers: shared hit=552, temp read=91 written=161
     1246                                      ->  Seq Scan on order_products  (cost=0.00..1302.21 rows=75021 width=23) (actual time=0.014..6.205 rows=75021 loops=1)
     1247                                            Buffers: shared hit=552
     1248                                ->  Hash  (cost=740.61..740.61 rows=3019 width=32) (actual time=5.546..5.547 rows=5009 loops=1)
     1249                                      Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 338kB
     1250                                      Buffers: shared hit=209
     1251                                      ->  Seq Scan on orders o  (cost=0.00..740.61 rows=3019 width=32) (actual time=0.011..4.416 rows=5009 loops=1)
     1252                                            Filter: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '2 years'::interval)))
     1253                                            Rows Removed by Filter: 20008
     1254                                            Buffers: shared hit=209
     1255Planning:
     1256  Buffers: shared hit=26
     1257Planning Time: 1.193 ms
     1258Execution Time: 91.092 ms
    11071259}}}
    11081260
    … …  
    11151267}}}
    11161268
    1117 The query was executed 10 times and the average execution time without indexes was: **93.895 ms**
     1269The query was executed 10 times with each execution time shown bellow:
     1270
     1271{{{
     1272Execution 1: 91.092 ms
     1273
     1274Execution 2: 93.156 ms
     1275
     1276Execution 3: 92.625 ms
     1277
     1278Execution 4: 92.375 ms
     1279
     1280Execution 5: 92.991 ms
     1281
     1282Execution 6: 96.696 ms
     1283
     1284Execution 7: 94.933 ms
     1285
     1286Execution 8: 92.235 ms
     1287
     1288Execution 9: 94.536 ms
     1289
     1290Execution 10: 98.312 ms
     1291}}}
     1292
     1293The average execution time without indexes was: **93.895 ms**
    11181294
    11191295==== Indexes
    … …  
    11491325because the query needs to aggregate essentially the whole {{{order_products}}} table to calculate total spend per order.
    11501326
    1151 The query was executed 10 times and the average execution time with indexes was: **90.547 ms**
     1327All of the beforementioned can be seen in the output shown bellow:
     1328
     1329{{{
     1330Sort  (cost=3208.75..3210.29 rows=615 width=252) (actual time=91.173..91.179 rows=12 loops=1)
     1331  Sort Key: (EXTRACT(year FROM u.date_created)) DESC, c.points_collected DESC
     1332  Sort Method: quicksort  Memory: 26kB
     1333  Buffers: shared hit=617, temp read=91 written=161
     1334  ->  Hash Left Join  (cost=3128.37..3180.26 rows=615 width=252) (actual time=91.129..91.151 rows=12 loops=1)
     1335        Hash Cond: (c.user_id = upm.user_id)
     1336        Buffers: shared hit=617, temp read=91 written=161
     1337        ->  Hash Join  (cost=18.66..52.03 rows=615 width=92) (actual time=0.075..0.086 rows=12 loops=1)
     1338              Hash Cond: (c.user_id = u.user_id)
     1339              Buffers: shared hit=2
     1340              ->  Seq Scan on consumers c  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.022..0.025 rows=12 loops=1)
     1341                    Buffers: shared hit=1
     1342              ->  Hash  (cost=17.00..17.00 rows=133 width=76) (actual time=0.040..0.041 rows=13 loops=1)
     1343                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
     1344                    Buffers: shared hit=1
     1345                    ->  Seq Scan on users u  (cost=0.00..17.00 rows=133 width=76) (actual time=0.019..0.026 rows=13 loops=1)
     1346                          Filter: (date_created >= (CURRENT_DATE - '2 years'::interval))
     1347                          Buffers: shared hit=1
     1348        ->  Hash  (cost=3109.55..3109.55 rows=13 width=112) (actual time=91.038..91.040 rows=12 loops=1)
     1349              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     1350              Buffers: shared hit=615, temp read=91 written=161
     1351              ->  Subquery Scan on upm  (cost=3109.19..3109.55 rows=13 width=112) (actual time=91.023..91.033 rows=12 loops=1)
     1352                    Buffers: shared hit=615, temp read=91 written=161
     1353                    ->  HashAggregate  (cost=3109.19..3109.42 rows=13 width=112) (actual time=91.022..91.030 rows=12 loops=1)
     1354                          Group Key: o.user_id
     1355                          Batches: 1  Memory Usage: 24kB
     1356                          Buffers: shared hit=615, temp read=91 written=161
     1357                          ->  Hash Right Join  (cost=2443.11..3071.45 rows=3019 width=64) (actual time=66.611..89.626 rows=5009 loops=1)
     1358                                Hash Cond: (order_products.order_id = o.order_id)
     1359                                Buffers: shared hit=615, temp read=91 written=161
     1360                                ->  HashAggregate  (cost=2052.42..2365.02 rows=25008 width=40) (actual time=61.570..80.289 rows=25017 loops=1)
     1361                                      Group Key: order_products.order_id
     1362                                      Batches: 5  Memory Usage: 8241kB  Disk Usage: 760kB
     1363                                      Buffers: shared hit=552, temp read=91 written=161
     1364                                      ->  Seq Scan on order_products  (cost=0.00..1302.21 rows=75021 width=23) (actual time=0.023..6.502 rows=75021 loops=1)
     1365                                            Buffers: shared hit=552
     1366                                ->  Hash  (cost=352.96..352.96 rows=3019 width=32) (actual time=5.023..5.023 rows=5009 loops=1)
     1367                                      Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 338kB
     1368                                      Buffers: shared hit=63
     1369                                      ->  Bitmap Heap Scan on orders o  (cost=79.80..352.96 rows=3019 width=32) (actual time=0.578..2.498 rows=5009 loops=1)
     1370                                            Recheck Cond: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '2 years'::interval)))
     1371                                            Heap Blocks: exact=42
     1372                                            Buffers: shared hit=63
     1373                                            ->  Bitmap Index Scan on idx_orders_status_purchase_date  (cost=0.00..79.05 rows=3019 width=0) (actual time=0.539..0.539 rows=5009 loops=1)
     1374                                                  Index Cond: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '2 years'::interval)))
     1375                                                  Buffers: shared hit=21
     1376Planning:
     1377  Buffers: shared hit=43
     1378Planning Time: 1.687 ms
     1379Execution Time: 92.316 ms
     1380}}}
     1381
     1382The query was again executed 10 times with each execution time shown bellow:
     1383
     1384{{{
     1385Execution 1: 92.316 ms
     1386
     1387Execution 2: 90.893 ms
     1388
     1389Execution 3: 91.461 ms
     1390
     1391Execution 4: 89.991 ms
     1392
     1393Execution 5: 89.992 ms
     1394
     1395Execution 6: 89.952 ms
     1396
     1397Execution 7: 90.382 ms
     1398
     1399Execution 8: 89.997 ms
     1400
     1401Execution 9: 89.878 ms
     1402
     1403Execution 10: 90.610 ms
     1404}}}
     1405
     1406The average execution time with indexes was: **90.547 ms**
    11521407
    11531408==== Performance comparison and conclusion