Optimize transactions by accounts (gsfa) query

[grooviegermanikus]

Query takes 6 Minutes for 576 matches

set work_mem TO '2000MB'
SELECT acc_id, count(*) FROM banking_stage_results_2.accounts_map_transaction amt
group by acc_id
having count(*) > 1000000
order by count(*) DESC

1533 372293232
1301 118448733
1569 95178560
1541 88712766
1519 79776843
1235 66610780
1437 53249110
1430 50201866
1250 48955244
1564 43563918
1376 41126735
1473 33466475
610 28398069
1440 21596952
1256 20354158
851 18442399
1441 18272739
1386 17495770
359 17226308
1429 16459921
1350 15427074
1255 14196734
1518 14100001
1359 13513218
1349 13204731
1537 13192097
1320 12670048
27387827 11663724
27486200 11661708
1415 11429613
1253 11421412
1318 11087305
1513 10469404
364 10356425
1449 10178756
1474 10069702
1390 10016931
1261 9685462
973 9585568
760 9584416
720 9531266
1497 9459862
434 9443298
1377 9399446
80 9374782
767 9244591
1366 9244591
399 9244591
53881 8809296
54145 8793796
676 8735432
845 8684860
634 8658417
229 8638330
1157 8114592
10435 8084228
188 8079712
931 7815970
415 7766531
245 7687711
1472 7614233
319 7515593
288 7481855
16900 7242286
16932 7010390
1512 6975971
866 6931972
371 6931689
333 6829968
28 6819488
1176 6681831
1572 6637659
447 6575369
647 6569540
105 6515669
197 6499773
1209 6451038
1304 6400690
22419 6386401
1190 6353893
1698 6036577
2729549 5937213
499 5896418
143 5775537
21233 5757663
217 5755438
1163 5741400
485 5682768
1108 5547583
3750 5541768
9282 5478259
1550 5468185
283 5458047
144 5456961
17 5388674
807 5355036
1177 5348771
1117 5285651
298 5284630
328503435 5275777
843 5258703
837 5237212
1066 5153144
382 5153144
47 5151728
1833781 5135683
328684041 5133522
10 5091835
88 5054436
1523 5026917
1259 5019718
32 5002233
1180 4962501
1059 4960909
1032 4958784
726 4926412
1981 4865012
200 4858706
852 4798945
79783521 4783654
79789875 4777421
1012 4723311
801 4697871
328572725 4685119
1438 4683593
1613 4669107
597 4651626
357 4595824
10244 4527592
336 4521670
1339 4521635
377 4518791
1448 4502323
2729 4485624
954 4477077
1126 4477076
1544 4477076
1054 4459921
987 4438209
682 4418329
221 4411515
1287 4411514
940 4401685
1240 4401684
9939 4393721
1220 4328745
228 4322450
1365 4322449
338 4322449
386 4301336
1485 4301334
2250 4299280
554 4267335
938 4267334
1263 4267334
64 4265448
356 4221648
125 4221648
39 4185487
1102 4178364
913 4165397
1325 4165397
8678 4161843
1277 4158202
470 4158202
636 4156609
4986 4125076
992 4094298
4306 4088842
4605 4088735
323 4088451
1288 4088450
1079 4088450
1905 4065208
326 4063382
1539 4063381
1101 4036267
92 4036267
22388 4025705
8353 4006373
2402 3925276
820 3901232
1530 3901232
311 3901232
327 3812713
231 3803386
5061 3800210
1394 3758566
1432 3734194
369 3727375
88055 3701992
314 3683277
544 3682310
250 3682310
641 3663677
627 3643598
1302 3643597
269 3631735
316 3622783
2578890 3604292
1465 3537306
10415 3533051
1910 3524257
1187 3522480
4061 3518759
834 3504916
26848 3444732
27111 3444732
833 3440008
4117 3434025
27352 3404250
7769 3375546
8046 3375415
8190 3375415
9574 3358263
3238483 3341376
3238970 3341171
783906 3267255
4545729 3266746
1343 3225200
648 3206340
6493 3197711
4014 3169436
897 3140971
2210 3010838
89555 2987936
37300 2954646
1262 2953133
2082 2947217
2371 2947216
737 2938439
526 2938159
1028 2931483
589 2927790
20866 2920120
1127 2902469
2320 2901803
1416 2901125
1463 2894153
12800 2885354
9323 2880154
12893 2879176
969 2878855
9560 2875772
1444 2867710
272 2860775
21047 2846823
446 2839443
583977233 2833929
1445 2816203
1052 2814204
854 2807756
958 2797925
1509 2789809
31391699 2785721
31391144 2785445
1466 2767499
20518 2757272
764 2756992
631 2756992
956 2711239
7569 2683509
1724 2662944
7095485 2637726
85 2627075
756 2626849
1179 2626529
292 2624825
961 2624404
519 2624158
1036 2623543
1008 2622602
842 2607747
4852 2604181
1739692 2603731
1072 2598011
1048 2592217
1201 2591988
230 2591938
460 2590379
72 2589878
36 2589690
20 2588531
274 2587318
20744 2577101
6432 2569792
1434 2563727
706 2560256
797 2547534
550 2537046
1542 2536123
35198 2518601
89493 2509093
2288 2460337
2254 2460209
20464 2432096
87247 2423583
2354 2413498
1063 2409018
3672 2377537
1203 2355384
12623 2324674
1824 2317495
20319 2261322
81821 2245707
2709 2241947
2255 2229189
4936 2228911
456 2224289
235 2221714
644 2217470
711759866 2212162
713005001 2211671
3591 2208048
52 2194818
2128 2177326
187 2170929
50 2143680
185685077 2141946
185684822 2141867
2905 2038697
7866800 2026291
7867920 2026209
552 2013855
4272 2012081
2511 2003150
2084 1983560
1882 1960327
794 1959360
516 1937250
467160 1930784
467180 1930784
612278 1929588
548 1921608
1526 1868643
95689 1866744
2199707 1863983
2555 1848954
1 1846153
380 1828689
9052 1822786
81714 1815852
101709 1810288
101526 1809251
6446 1808736
867 1806294
10699023 1802603
10697975 1802543
4098 1750235
4688 1750217
942 1736588
1276 1736230
6469 1708286
2013 1698594
1178 1685071
3392 1676701
725 1676189
182 1666773
514 1666720
59673 1659158
1568 1647789
3454 1644419
3095 1644418
2025 1643974
1788 1640040
848 1639523
624 1637721
82051 1632084
43 1593069
3353 1585302
4044 1577986
1151 1555321
2659570 1552916
35 1552402
1076 1543129
66097366 1539605
66096903 1539344
1200 1537262
690 1531337
94 1525008
808 1519864
735 1512553
1197 1508088
1051 1507720
325 1507327
450 1498830
235744763 1490101
788 1489546
235814117 1489065
1567 1487429
1500 1486051
307 1485910
1842 1484903
1340 1484887
1279 1484213
595 1484148
1571 1483388
151 1472185
81688 1463931
821 1463430
2754 1459336
2726 1459336
611 1457066
468 1455089
2097 1453498
1294 1452432
287 1451359
658 1450995
812 1450466
111 1449952
346 1448969
194 1448877
402 1448316
2194 1441769
2080 1441769
1119 1441088
14538 1438073
14557 1438028
240 1429274
150 1425467
467 1422004
975 1420907
153 1419624
362 1415667
1442 1413964
1553 1413308
27104 1409222
719 1405470
612 1403147
84 1402701
1027 1402400
667 1402311
620 1401605
476 1401538
656 1401025
27971 1387694
1084 1380251
40073316 1371411
668776900 1371320
1405 1369892
986 1369780
989 1369214
90142 1363244
273240 1362782
385 1359869
92671 1354766
1387 1354581
944 1346739
329 1331660
45079 1330753
1596 1327626
112958 1321748
970 1311203
1137 1286311
923 1283305
779 1277401
97377630 1275368
36292 1274934
3836 1269513
1206 1263960
32686278 1262045
948 1252307
3619 1251645
22711 1247047
2271 1245199
92943 1242714
1625 1241602
399754 1236225
605 1234697
2372 1233987
5734 1232092
6537 1232084
6554 1228365
782 1227147
5997 1223645
5602 1223639
1900 1223298
59092 1219194
367 1218986
40 1218368
360 1217399
320 1217267
1562 1216176
7194 1213720
26996 1207418
133 1206337
504 1205480
4120193 1195881
1241346 1195005
560 1191972
2204 1190259
1323 1189701
87 1189632
749 1182394
2269 1176896
1653 1176869
67 1175639
27141 1175141
618 1174505
32685855 1174036
81746 1170828
111804 1167602
835 1157019
205308 1155526
4794 1154115
4188 1154112
4137 1154101
199236 1152768
22532 1147828
22586 1147828
799 1146906
14831 1122411
15222 1121866
824 1114679
1106 1112416
290 1112416
715 1112416
1083 1112416
689 1109018
478 1106205
3413 1104875
480 1104353
1305 1104026
1527 1104026
1468 1104026
1413 1104026
6861 1093205
825 1092133
22058 1091836
4155750 1088976
81576 1087748
281391585 1087447
4155621 1085757
8916 1079604
9761 1076165
17223 1071771
365 1070041
253 1070041
921 1070036
119544 1066231
13328 1056325
3295 1053282
3336 1053274
2993 1053047
2879 1053047
3634 1053039
1191 1052510
2197 1042205
2424 1042205
2407 1040089
2440 1036155
25402 1030804
25207 1030804
25260 1030804
25432 1030804
45166 1029849
91699 1028911
545 1026290
1183 1026290
208 1026283
3257 1024809
3187 1024809
2775 1024803
22927 1021846
492 1019620
26 1012843
21207 1011495
441494 1009556
441758 1009552
441771 1009552
441792 1009552
442090 1009552
441406 1009552
1833409 1008255
92134 1007643
4194 1006916

[grooviegermanikus]

CREATE INDEX idx_amt_large_accounts ON banking_stage_results_2.accounts_map_transaction(acc_id,transaction_id)
WHERE acc_id IN (
1533,
1301,
1569,
1541,
1519,
1235,
1437,
1430,
1250,
1564,
1376,
1473,
610,
1440,
1256,
851,
1441,
1386,
359,
1429,
1350,
1255,
1518,
1359,
1349,
1537,
1320,
27387827,
27486200,
1415)

[grooviegermanikus]

Use this to test:

• 1415, Cargo8a1e6NkGyrjy4BQEW4ASGKs9KSyDyUrXMfpJoiH -> part of index
• 1253, cstath6RrYbzZcW5HUVgkE2ibC3JS8g56YsfXVeNNR6 -> not indexed

[grooviegermanikus]

"banking_stage_results_2.idx_amt_large_accounts" -> "41 GB"

[grooviegermanikus]

SELECT * FROM ( SELECT amt.transaction_id, signature, exists ( SELECT 1 FROM banking_stage_results_2.transaction_infos txi WHERE txi.transaction_id=amt.transaction_id ) AS was_included_in_block, txi.cu_requested, txi.prioritization_fees FROM banking_stage_results_2.accounts_map_transaction amt INNER JOIN banking_stage_results_2.accounts acc ON acc.acc_id=amt.acc_id INNER JOIN banking_stage_results_2.transactions txs ON txs.transaction_id=amt.transaction_id WHERE account_key = '9CQdhWRxLwVpQuMzxQ7vLoZQUzyDd6jLEQgYUkbJhDry' -- '7Zi96LCCjSEEd5yyFik8XvAhfJsdUGzLPMprjKKrdaCA' --'43dEtfUoL1dN9v4JPFB5KSkHHFF4bswGn6mUJ9dpEAix' ) AS data ORDER BY transaction_id DESC LIMIT 100

[grooviegermanikus]

closed - implement this approach: blockworks-foundation/BankingStageErrorsTrackingSidecar#37