explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dJAf

Settings
# exclusive inclusive rows x rows loops node
1. 472.301 32,495.905 ↑ 8.2 4 1

GroupAggregate (cost=2,855.94..2,856.60 rows=33 width=42) (actual time=31,840.110..32,495.905 rows=4 loops=1)

  • Group Key: u.id
  • Filter: (count(DISTINCT t2.id) = 0)
  • Rows Removed by Filter: 2,656
2. 983.316 32,023.604 ↓ 2,106.1 69,502 1

Sort (cost=2,855.94..2,856.02 rows=33 width=42) (actual time=31,568.817..32,023.604 rows=69,502 loops=1)

  • Sort Key: u.id
  • Sort Method: external merge Disk: 3,936kB
3. 942.246 31,040.288 ↓ 2,106.1 69,502 1

Nested Loop Left Join (cost=27.48..2,855.11 rows=33 width=42) (actual time=15.412..31,040.288 rows=69,502 loops=1)

4. 11,718.187 29,479.994 ↓ 2,664.0 2,664 1

Nested Loop Anti Join (cost=27.05..2,824.82 rows=1 width=34) (actual time=15.356..29,479.994 rows=2,664 loops=1)

  • Join Filter: (n."accountId" = u."accountId")
  • Rows Removed by Join Filter: 1,745,018
5. 94.519 310.171 ↓ 2,666.0 2,666 1

Nested Loop (cost=4.21..54.80 rows=1 width=34) (actual time=0.347..310.171 rows=2,666 loops=1)

6. 60.560 104.652 ↓ 4,440.0 4,440 1

Nested Loop (cost=3.79..45.59 rows=1 width=34) (actual time=0.296..104.652 rows=4,440 loops=1)

7. 2.027 3.373 ↓ 147.0 147 1

Hash Left Join (cost=3.50..16.90 rows=1 width=8) (actual time=0.175..3.373 rows=147 loops=1)

  • Hash Cond: (o.id = oeb."organizationId")
  • Filter: (oeb.id IS NULL)
  • Rows Removed by Filter: 2
8. 1.224 1.224 ↓ 1.0 149 1

Seq Scan on organization o (cost=0.00..12.45 rows=148 width=8) (actual time=0.021..1.224 rows=149 loops=1)

  • Filter: ((subscription <> 'suspended'::text) AND ("amountPerMonth" > 0))
  • Rows Removed by Filter: 14
9. 0.054 0.122 ↓ 1.3 4 1

Hash (cost=3.46..3.46 rows=3 width=16) (actual time=0.116..0.122 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.068 0.068 ↓ 1.3 4 1

Seq Scan on "organizationEmailBlacklist" oeb (cost=0.00..3.46 rows=3 width=16) (actual time=0.018..0.068 rows=4 loops=1)

  • Filter: (("deletedAt" IS NULL) AND (email = 'credit-threshold-way-reached'::text))
  • Rows Removed by Filter: 113
11. 40.719 40.719 ↓ 1.7 30 147

Index Scan using idx_16481_user_status_organization_id on "user" u (cost=0.29..28.51 rows=18 width=34) (actual time=0.015..0.277 rows=30 loops=147)

  • Index Cond: ((status = ANY ('{person,personAndAdmin}'::text[])) AND ("organizationId" = o.id))
  • Filter: (("deletedAt" IS NULL) AND (date("createdAt") < '2020-08-03'::date))
  • Rows Removed by Filter: 2
12. 111.000 111.000 ↑ 1.0 1 4,440

Index Scan using transaction_6 on transaction t1 (cost=0.43..9.20 rows=1 width=8) (actual time=0.020..0.025 rows=1 loops=4,440)

  • Index Cond: (("userId" = u.id) AND (my_date_cast("createdAt") = '2020-08-03'::date))
  • Filter: (("deletedAt" IS NULL) AND (id IS NOT NULL))
13. 16,569.190 17,451.636 ↓ 2.3 655 2,666

Bitmap Heap Scan on notification n (cost=22.84..2,766.49 rows=282 width=8) (actual time=1.370..6.546 rows=655 loops=2,666)

  • Recheck Cond: (type = 'credit-threshold-way-reached'::text)
  • Filter: (("deletedAt" IS NULL) AND (date("createdAt") > '2020-08-03'::date))
  • Rows Removed by Filter: 2,910
  • Heap Blocks: exact=3,220,154
14. 882.446 882.446 ↓ 4.2 3,565 2,666

Bitmap Index Scan on idx_16551_notification_type (cost=0.00..22.77 rows=846 width=0) (actual time=0.331..0.331 rows=3,565 loops=2,666)

  • Index Cond: (type = 'credit-threshold-way-reached'::text)
15. 618.048 618.048 ↑ 2.3 26 2,664

Index Scan using transaction_6 on transaction t2 (cost=0.43..29.70 rows=59 width=16) (actual time=0.024..0.232 rows=26 loops=2,664)

  • Index Cond: (("userId" = u.id) AND (my_date_cast("createdAt") > '2020-08-03'::date))
  • Filter: ("deletedAt" IS NULL)
Planning time : 2.201 ms
Execution time : 32,497.348 ms