explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 94PG

Settings
# exclusive inclusive rows x rows loops node
1. 2.633 13,852.384 ↑ 21.0 53 1

GroupAggregate (cost=10,000,011,726.12..10,000,011,753.97 rows=1,114 width=62) (actual time=13,849.446..13,852.384 rows=53 loops=1)

  • Group Key: accounts.account_type_id, accounts.full_qualified_name, txdetails.account_id
  • Buffers: shared hit=3 read=19,166
  • I/O Timings: read=1,032.421
2. 9.164 13,849.751 ↓ 13.3 14,771 1

Sort (cost=10,000,011,726.12..10,000,011,728.90 rows=1,114 width=36) (actual time=13,848.769..13,849.751 rows=14,771 loops=1)

  • Sort Key: accounts.account_type_id, accounts.full_qualified_name, txdetails.account_id
  • Sort Method: quicksort Memory: 1,540kB
  • Buffers: shared hit=3 read=19,166
  • I/O Timings: read=1,032.421
3. 75.857 13,840.587 ↓ 13.3 14,771 1

Nested Loop Semi Join (cost=10,000,000,069.26..10,000,011,669.74 rows=1,114 width=36) (actual time=199.192..13,840.587 rows=14,771 loops=1)

  • Join Filter: ((txdetails.txn_type_id)::numeric = (((unnest('{1,3,4,7,8,9,11,12,13,14,15,20,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64}'::text[])))::numeric))
  • Rows Removed by Join Filter: 319,657
  • Buffers: shared hit=3 read=19,166
  • I/O Timings: read=1,032.421
4. 7.663 13,749.959 ↓ 6.6 14,771 1

Hash Join (cost=69.26..7,768.97 rows=2,227 width=38) (actual time=199.140..13,749.959 rows=14,771 loops=1)

  • Hash Cond: (txdetails.account_id = accounts.account_id)
  • Join Filter: ((accounts.account_type_id <> ALL ('{10,13,12,14,11}'::integer[])) OR (txdetails.txn_date >= '2020-01-01'::date))
  • Rows Removed by Join Filter: 6,034
  • Buffers: shared hit=3 read=19,166
  • I/O Timings: read=1,032.421
5. 2.441 13,596.526 ↓ 2.9 20,805 1

Append (cost=0.56..7,629.18 rows=7,223 width=26) (actual time=49.398..13,596.526 rows=20,805 loops=1)

  • Buffers: shared hit=2 read=19,084
  • I/O Timings: read=999.880
6. 13,594.085 13,594.085 ↓ 2.9 20,805 1

Index Scan using txnd_cid_tdate_aid_cid_i2_p10 on transaction_detail_p10 txdetails (cost=0.56..7,593.06 rows=7,223 width=26) (actual time=49.397..13,594.085 rows=20,805 loops=1)

  • Index Cond: ((company_id = '123145693504642'::numeric) AND (txn_date < '2020-12-31'::date))
  • Filter: (is_non_posting IS NULL)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=2 read=19,084
  • I/O Timings: read=999.880
7. 0.048 145.770 ↓ 1.3 82 1

Hash (cost=67.89..67.89 rows=65 width=41) (actual time=145.770..145.770 rows=82 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=1 read=82
  • I/O Timings: read=32.542
8. 0.015 145.722 ↓ 1.3 82 1

Append (cost=0.43..67.89 rows=65 width=41) (actual time=14.066..145.722 rows=82 loops=1)

  • Buffers: shared hit=1 read=82
  • I/O Timings: read=32.542
9. 145.707 145.707 ↓ 1.3 82 1

Index Scan using account_p10_pkey on account_p10 accounts (cost=0.43..67.56 rows=65 width=41) (actual time=14.065..145.707 rows=82 loops=1)

  • Index Cond: (company_id = '123145693504642'::numeric)
  • Buffers: shared hit=1 read=82
  • I/O Timings: read=32.542
10. 14.745 14.771 ↑ 4.3 23 14,771

Materialize (cost=0.00..3.77 rows=100 width=32) (actual time=0.000..0.001 rows=23 loops=14,771)

11. 0.016 0.026 ↑ 2.4 42 1

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.007..0.026 rows=42 loops=1)

12. 0.009 0.010 ↑ 2.4 42 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.004..0.010 rows=42 loops=1)

13. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)