explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5LX2 : Optimization for: Optimization for: Optimization for: Optimization for: plan #Xx4H; plan #bJql; plan #La18; plan #BPgB

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 44.822 485.916 ↓ 5.2 54,510 1

Hash Join (cost=6,387.66..10,936.81 rows=10,398 width=1,552) (actual time=244.096..485.916 rows=54,510 loops=1)

  • Hash Cond: (invoice.id = invoice_line_2.invoice_id)
2. 185.654 403.475 ↓ 1.3 151,171 1

Hash Right Join (cost=4,785.09..8,916.52 rows=115,065 width=1,264) (actual time=206.426..403.475 rows=151,171 loops=1)

  • Hash Cond: (invoice_view_1.invoice_id = invoice.id)
3. 11.470 11.470 ↑ 1.0 71,185 1

Seq Scan on invoice_view invoice_view_1 (cost=0.00..2,624.85 rows=71,185 width=180) (actual time=0.014..11.470 rows=71,185 loops=1)

4. 45.367 206.351 ↓ 1.0 31,375 1

Hash (cost=4,404.99..4,404.99 rows=30,408 width=1,084) (actual time=206.351..206.351 rows=31,375 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 17,660kB
5. 22.388 160.984 ↓ 1.0 31,375 1

Hash Join (cost=3,082.27..4,404.99 rows=30,408 width=1,084) (actual time=67.533..160.984 rows=31,375 loops=1)

  • Hash Cond: (invoice.bill_to_contact_id = contact_1.id)
6. 30.129 126.457 ↓ 1.0 31,375 1

Hash Right Join (cost=2,385.75..3,628.63 rows=30,408 width=821) (actual time=55.346..126.457 rows=31,375 loops=1)

  • Hash Cond: (invoice_line_1.invoice_id = invoice.id)
7. 24.616 58.697 ↑ 1.0 28,403 1

Hash Left Join (cost=736.45..1,568.74 rows=28,403 width=507) (actual time=17.650..58.697 rows=28,403 loops=1)

  • Hash Cond: (invoice_line_1.employer_id = employer_1.employer_id)
8. 16.503 16.503 ↑ 1.0 28,403 1

Index Scan using invoice_line_employer_id on invoice_line invoice_line_1 (cost=0.29..758.00 rows=28,403 width=137) (actual time=0.018..16.503 rows=28,403 loops=1)

9. 9.644 17.578 ↑ 1.0 18,096 1

Hash (cost=509.96..509.96 rows=18,096 width=370) (actual time=17.578..17.578 rows=18,096 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,848kB
10. 7.934 7.934 ↑ 1.0 18,096 1

Seq Scan on employer employer_1 (cost=0.00..509.96 rows=18,096 width=370) (actual time=0.018..7.934 rows=18,096 loops=1)

11. 10.268 37.631 ↓ 1.0 21,146 1

Hash (cost=1,397.55..1,397.55 rows=20,140 width=314) (actual time=37.631..37.631 rows=21,146 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 6,149kB
12. 12.781 27.363 ↓ 1.0 21,146 1

Hash Right Join (cost=993.27..1,397.55 rows=20,140 width=314) (actual time=12.586..27.363 rows=21,146 loops=1)

  • Hash Cond: (invoice_send_1.invoice_id = invoice.id)
13. 2.140 2.140 ↑ 1.0 20,140 1

Seq Scan on invoice_send invoice_send_1 (cost=0.00..351.40 rows=20,140 width=38) (actual time=0.013..2.140 rows=20,140 loops=1)

14. 6.517 12.442 ↑ 1.0 18,812 1

Hash (cost=758.12..758.12 rows=18,812 width=276) (actual time=12.442..12.442 rows=18,812 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 4,823kB
15. 5.925 5.925 ↑ 1.0 18,812 1

Seq Scan on invoice (cost=0.00..758.12 rows=18,812 width=276) (actual time=0.013..5.925 rows=18,812 loops=1)

16. 6.822 12.139 ↑ 1.0 18,201 1

Hash (cost=469.01..469.01 rows=18,201 width=263) (actual time=12.138..12.139 rows=18,201 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,534kB
17. 5.317 5.317 ↑ 1.0 18,201 1

Seq Scan on contact contact_1 (cost=0.00..469.01 rows=18,201 width=263) (actual time=0.015..5.317 rows=18,201 loops=1)

18. 0.369 37.619 ↓ 1.7 2,846 1

Hash (cost=1,581.32..1,581.32 rows=1,700 width=8) (actual time=37.619..37.619 rows=2,846 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 144kB
19. 2.628 37.250 ↓ 1.7 2,846 1

HashAggregate (cost=1,564.32..1,581.32 rows=1,700 width=8) (actual time=36.747..37.250 rows=2,846 loops=1)

  • Group Key: invoice_1.id
20. 3.205 34.622 ↓ 5.8 9,791 1

Nested Loop (cost=190.89..1,560.07 rows=1,700 width=8) (actual time=8.814..34.622 rows=9,791 loops=1)

21. 7.944 21.626 ↓ 5.8 9,791 1

Hash Join (cost=190.60..1,022.90 rows=1,700 width=4) (actual time=8.792..21.626 rows=9,791 loops=1)

  • Hash Cond: (invoice_line_2.employer_id = employer.employer_id)
22. 13.114 13.114 ↑ 1.0 28,403 1

Index Scan using invoice_line_employer_id on invoice_line invoice_line_2 (cost=0.29..758.00 rows=28,403 width=8) (actual time=0.008..13.114 rows=28,403 loops=1)

23. 0.122 0.568 ↑ 1.0 1,083 1

Hash (cost=176.78..176.78 rows=1,083 width=4) (actual time=0.568..0.568 rows=1,083 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 55kB
24. 0.446 0.446 ↑ 1.0 1,083 1

Index Scan using uq_employer_tenant_id_wex_key on employer (cost=0.29..176.78 rows=1,083 width=4) (actual time=0.014..0.446 rows=1,083 loops=1)

  • Index Cond: (tenant_id = 651)
25. 9.791 9.791 ↑ 1.0 1 9,791

Index Only Scan using invoice_pkey on invoice invoice_1 (cost=0.29..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=9,791)

  • Index Cond: (id = invoice_line_2.invoice_id)
  • Heap Fetches: 9,791
Planning time : 1.933 ms
Execution time : 489.477 ms