explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J4a8

Settings
# exclusive inclusive rows x rows loops node
1. 12,213.612 216,777.822 ↑ 1.0 1 1

Aggregate (cost=8,676,797.12..8,676,797.13 rows=1 width=8) (actual time=216,777.822..216,777.822 rows=1 loops=1)

2. 18,677.043 204,564.210 ↓ 26.7 98,881,428 1

Hash Right Join (cost=8,565,295.53..8,602,609.16 rows=3,709,398 width=38) (actual time=181,217.780..204,564.210 rows=98,881,428 loops=1)

  • Hash Cond: (overdrafts.userid = stat_users.userid)
3. 4,690.544 5,849.738 ↑ 1.3 58,544 1

Bitmap Heap Scan on overdrafts (cost=4,017.61..40,520.07 rows=73,839 width=4) (actual time=1,164.504..5,849.738 rows=58,544 loops=1)

  • Recheck Cond: (status = 104)
  • Filter: (creation_date < '2020-02-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 60,667
  • Heap Blocks: exact=32,002
4. 1,159.194 1,159.194 ↓ 1.3 147,993 1

Bitmap Index Scan on i_overdrafts_status (cost=0.00..3,999.15 rows=117,697 width=0) (actual time=1,159.194..1,159.194 rows=147,993 loops=1)

  • Index Cond: (status = 104)
5. 22,867.366 180,037.429 ↓ 26.6 98,793,525 1

Hash (cost=8,514,910.44..8,514,910.44 rows=3,709,398 width=42) (actual time=180,037.429..180,037.429 rows=98,793,525 loops=1)

  • Buckets: 8,388,608 (originally 4194304) Batches: 2 (originally 1) Memory Usage: 4,083,875kB
6. 35,449.417 157,170.063 ↓ 26.6 98,793,525 1

Hash Join (cost=7,574,222.23..8,514,910.44 rows=3,709,398 width=42) (actual time=76,688.912..157,170.063 rows=98,793,525 loops=1)

  • Hash Cond: (user_creditcards.creditcardid = creditcards.creditcardid)
7. 35,666.543 115,341.493 ↓ 26.6 98,793,525 1

Hash Join (cost=7,025,237.62..7,829,561.66 rows=3,709,398 width=46) (actual time=70,252.525..115,341.493 rows=98,793,525 loops=1)

  • Hash Cond: (user_creditcards.userid = stat_users.userid)
8. 9,440.287 9,440.287 ↑ 1.0 17,979,427 1

Seq Scan on user_creditcards (cost=0.00..677,208.71 rows=18,004,271 width=24) (actual time=0.398..9,440.287 rows=17,979,427 loops=1)

9. 6,304.484 70,234.663 ↓ 8.0 17,585,181 1

Hash (cost=6,997,831.41..6,997,831.41 rows=2,192,497 width=34) (actual time=70,234.663..70,234.663 rows=17,585,181 loops=1)

  • Buckets: 8,388,608 (originally 4194304) Batches: 4 (originally 1) Memory Usage: 446,465kB
10. 0.000 63,930.179 ↓ 8.0 17,585,181 1

Gather (cost=2,496,475.28..6,997,831.41 rows=2,192,497 width=34) (actual time=30,683.860..63,930.179 rows=17,585,181 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
11. 910.520 67,073.884 ↓ 7.0 2,198,148 8 / 8

Hash Anti Join (cost=2,495,475.28..6,777,581.71 rows=313,214 width=34) (actual time=30,674.979..67,073.884 rows=2,198,148 loops=8)

  • Hash Cond: (stat_users.userid = chargebacks.userid)
12. 1,586.536 65,901.284 ↓ 7.1 2,249,624 8 / 8

Hash Join (cost=2,453,936.05..6,731,794.18 rows=317,340 width=34) (actual time=30,405.120..65,901.284 rows=2,249,624 loops=8)

  • Hash Cond: (orders.userid = stat_users.userid)
13. 240.431 33,923.699 ↑ 1.5 2,462,003 8 / 8

Append (cost=0.00..4,260,777.40 rows=3,579,152 width=18) (actual time=0.030..33,923.699 rows=2,462,003 loops=8)

14. 0.024 0.024 ↓ 0.0 0 8 / 8

Parallel Seq Scan on orders (cost=0.00..313.00 rows=1 width=32) (actual time=0.024..0.024 rows=0 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
15. 2,710.723 2,710.723 ↑ 1.1 385,220 8 / 8

Parallel Seq Scan on orders_2020_01 (cost=0.00..914,623.20 rows=407,971 width=17) (actual time=0.006..2,710.723 rows=385,220 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
  • Rows Removed by Filter: 784,997
16. 1,824.479 1,824.479 ↑ 1.5 380,135 8 / 8

Parallel Seq Scan on orders_2020_02 (cost=0.00..620,754.14 rows=553,946 width=18) (actual time=0.012..1,824.479 rows=380,135 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
  • Rows Removed by Filter: 585,781
17. 6,184.025 6,184.025 ↑ 1.6 372,641 8 / 8

Parallel Seq Scan on orders_2020_03 (cost=0.00..614,192.67 rows=597,774 width=18) (actual time=0.444..6,184.025 rows=372,641 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
  • Rows Removed by Filter: 543,324
18. 6,505.143 6,505.143 ↑ 1.5 318,539 8 / 8

Parallel Seq Scan on orders_2020_04 (cost=0.00..524,907.70 rows=485,342 width=18) (actual time=0.498..6,505.143 rows=318,539 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
  • Rows Removed by Filter: 506,104
19. 6,332.782 6,332.782 ↑ 1.5 320,168 8 / 8

Parallel Seq Scan on orders_2020_05 (cost=0.00..505,239.69 rows=483,805 width=17) (actual time=0.355..6,332.782 rows=320,168 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
  • Rows Removed by Filter: 493,822
20. 4,972.862 4,972.862 ↑ 1.5 315,738 8 / 8

Parallel Seq Scan on orders_2020_06 (cost=0.00..481,977.60 rows=468,379 width=17) (actual time=0.498..4,972.862 rows=315,738 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
  • Rows Removed by Filter: 420,727
21. 5,098.405 5,098.405 ↑ 1.5 334,941 8 / 8

Parallel Seq Scan on orders_2020_07 (cost=0.00..540,882.22 rows=505,281 width=18) (actual time=0.055..5,098.405 rows=334,941 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
  • Rows Removed by Filter: 481,627
22. 54.825 54.825 ↑ 2.2 34,621 8 / 8

Parallel Seq Scan on orders_2020_08 (cost=0.00..57,887.19 rows=76,653 width=17) (actual time=0.013..54.825 rows=34,621 loops=8)

  • Filter: ((creditcard_amount > '0'::numeric) AND (creation_date > '2020-01-01 00:00:00'::timestamp without time zone) AND (status = 101))
  • Rows Removed by Filter: 42,796
23. 819.733 30,391.049 ↓ 1.9 2,097,168 8 / 8

Hash (cost=2,440,337.47..2,440,337.47 rows=1,087,886 width=16) (actual time=30,391.049..30,391.049 rows=2,097,168 loops=8)

  • Buckets: 4,194,304 (originally 2097152) Batches: 1 (originally 1) Memory Usage: 131,073kB
24. 3,579.782 29,571.316 ↓ 1.9 2,097,168 8 / 8

Hash Join (cost=976,299.22..2,440,337.47 rows=1,087,886 width=16) (actual time=10,549.615..29,571.316 rows=2,097,168 loops=8)

  • Hash Cond: (users.userid = stat_users.userid)
25. 15,456.564 15,456.564 ↑ 1.0 10,634,792 8 / 8

Seq Scan on users (cost=0.00..1,413,244.02 rows=10,644,102 width=4) (actual time=0.043..15,456.564 rows=10,634,792 loops=8)

  • Filter: (status <> ALL ('{-1,-2,0}'::integer[]))
  • Rows Removed by Filter: 233,901
26. 693.977 10,534.970 ↓ 1.9 2,114,806 8 / 8

Hash (cost=962,413.20..962,413.20 rows=1,110,881 width=12) (actual time=10,534.970..10,534.970 rows=2,114,806 loops=8)

  • Buckets: 4,194,304 (originally 2097152) Batches: 1 (originally 1) Memory Usage: 123,639kB
27. 2,845.992 9,840.993 ↓ 1.9 2,114,806 8 / 8

Hash Join (cost=675,231.06..962,413.20 rows=1,110,881 width=12) (actual time=4,393.095..9,840.993 rows=2,114,806 loops=8)

  • Hash Cond: (user_profiles.userid = stat_users.userid)
28. 2,616.565 2,616.565 ↓ 1.0 8,208,932 8 / 8

Seq Scan on user_profiles (cost=0.00..245,442.09 rows=8,168,333 width=8) (actual time=0.068..2,616.565 rows=8,208,932 loops=8)

  • Filter: (birthdate < '2002-08-01'::date)
  • Rows Removed by Filter: 529,135
29. 804.766 4,378.436 ↓ 2.0 2,930,181 8 / 8

Hash (cost=657,140.36..657,140.36 rows=1,447,256 width=4) (actual time=4,378.436..4,378.436 rows=2,930,181 loops=8)

  • Buckets: 4,194,304 (originally 2097152) Batches: 1 (originally 1) Memory Usage: 135,783kB
30. 1,216.884 3,573.670 ↓ 2.0 2,930,181 8 / 8

Hash Left Join (cost=49,741.30..657,140.36 rows=1,447,256 width=4) (actual time=713.729..3,573.670 rows=2,930,181 loops=8)

  • Hash Cond: (stat_users.userid = user_categories.userid)
  • Filter: ((((user_categories.category)::text !~~ '%suspect%'::text) AND ((user_categories.category)::text !~~ '%watchlist%'::text)) OR (user_categories.category IS NULL))
  • Rows Removed by Filter: 89,416
31. 1,655.734 1,655.734 ↓ 1.0 3,019,597 8 / 8

Seq Scan on stat_users (cost=0.00..599,710.32 rows=2,929,042 width=4) (actual time=0.027..1,655.734 rows=3,019,597 loops=8)

  • Filter: (order_count > 4)
  • Rows Removed by Filter: 7,849,075
32. 342.354 701.052 ↓ 1.0 1,091,319 8 / 8

Hash (cost=36,103.91..36,103.91 rows=1,090,991 width=28) (actual time=701.052..701.052 rows=1,091,319 loops=8)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 80,910kB
33. 358.698 358.698 ↓ 1.0 1,091,319 8 / 8

Seq Scan on user_categories (cost=0.00..36,103.91 rows=1,090,991 width=28) (actual time=0.057..358.698 rows=1,091,319 loops=8)

34. 155.750 262.080 ↓ 1.0 845,311 8 / 8

Hash (cost=30,980.77..30,980.77 rows=844,677 width=4) (actual time=262.080..262.080 rows=845,311 loops=8)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 37,910kB
35. 106.330 106.330 ↓ 1.0 845,311 8 / 8

Seq Scan on chargebacks (cost=0.00..30,980.77 rows=844,677 width=4) (actual time=0.033..106.330 rows=845,311 loops=8)

36. 3,093.134 6,379.153 ↑ 1.0 15,595,774 1

Hash (cost=291,017.16..291,017.16 rows=15,723,716 width=4) (actual time=6,379.153..6,379.153 rows=15,595,774 loops=1)

  • Buckets: 16,777,216 Batches: 2 Memory Usage: 405,137kB
37. 3,286.019 3,286.019 ↑ 1.0 15,595,774 1

Seq Scan on creditcards (cost=0.00..291,017.16 rows=15,723,716 width=4) (actual time=0.464..3,286.019 rows=15,595,774 loops=1)

Planning time : 92.973 ms
Execution time : 216,808.534 ms