explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Y11

Settings
# exclusive inclusive rows x rows loops node
1. 505.887 3,718.056 ↑ 83.0 4 1

GroupAggregate (cost=3,212.90..3,219.54 rows=332 width=42) (actual time=3,000.237..3,718.056 rows=4 loops=1)

  • Group Key: u.id
  • Filter: (count(DISTINCT t2.id) = 0)
  • Rows Removed by Filter: 2,656
2. 1,078.262 3,212.169 ↓ 209.3 69,502 1

Sort (cost=3,212.90..3,213.73 rows=332 width=42) (actual time=2,728.940..3,212.169 rows=69,502 loops=1)

  • Sort Key: u.id
  • Sort Method: external merge Disk: 3,944kB
3. 1,090.818 2,133.907 ↓ 209.3 69,502 1

Nested Loop Left Join (cost=2,788.06..3,198.99 rows=332 width=42) (actual time=20.974..2,133.907 rows=69,502 loops=1)

4. 101.107 374.425 ↓ 296.0 2,664 1

Nested Loop (cost=2,787.63..2,935.62 rows=9 width=34) (actual time=20.932..374.425 rows=2,664 loops=1)

5. 64.923 201.156 ↓ 237.4 3,798 1

Hash Anti Join (cost=2,787.20..2,816.23 rows=16 width=34) (actual time=20.871..201.156 rows=3,798 loops=1)

  • Hash Cond: (u."accountId" = n."accountId")
6. 69.553 121.128 ↓ 246.7 4,440 1

Nested Loop (cost=17.19..45.60 rows=18 width=34) (actual time=5.730..121.128 rows=4,440 loops=1)

7. 2.717 6.887 ↓ 147.0 147 1

HashAggregate (cost=16.90..16.91 rows=1 width=8) (actual time=5.608..6.887 rows=147 loops=1)

  • Group Key: o.id
8. 2.635 4.170 ↓ 147.0 147 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.070 0.070 ↓ 1.3 4 1

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

  • Filter: (("deletedAt" IS NULL) AND (email = 'credit-threshold-way-reached'::text))
  • Rows Removed by Filter: 113
12. 44.688 44.688 ↓ 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.018..0.304 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
13. 5.955 15.105 ↓ 2.3 655 1

Hash (cost=2,766.49..2,766.49 rows=282 width=8) (actual time=15.099..15.105 rows=655 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 34kB
14. 8.718 9.150 ↓ 2.3 655 1

Bitmap Heap Scan on notification n (cost=22.84..2,766.49 rows=282 width=8) (actual time=1.929..9.150 rows=655 loops=1)

  • 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=1,208
15. 0.432 0.432 ↓ 4.2 3,565 1

Bitmap Index Scan on idx_16551_notification_type (cost=0.00..22.77 rows=846 width=0) (actual time=0.426..0.432 rows=3,565 loops=1)

  • Index Cond: (type = 'credit-threshold-way-reached'::text)
16. 72.162 72.162 ↑ 1.0 1 3,798

Index Scan using transaction_7 on transaction t1 (cost=0.43..7.45 rows=1 width=8) (actual time=0.014..0.019 rows=1 loops=3,798)

  • Index Cond: (("userId" = u.id) AND ("projectId" IS NULL) AND (my_date_cast("createdAt") = '2020-08-03'::date))
  • Filter: (("deletedAt" IS NULL) AND (id IS NOT NULL))
17. 668.664 668.664 ↑ 2.3 26 2,664

Index Scan using transaction_7 on transaction t2 (cost=0.43..28.67 rows=59 width=16) (actual time=0.014..0.251 rows=26 loops=2,664)

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