explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 41.940 469.773 ↓ 5.2 54,510 1

Hash Join (cost=6,548.63..11,097.61 rows=10,397 width=1,552) (actual time=236.814..469.773 rows=54,510 loops=1)

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

Hash Right Join (cost=4,889.03..9,020.34 rows=115,052 width=1,264) (actual time=209.414..400.495 rows=151,171 loops=1)

  • Hash Cond: (invoice_view_1.invoice_id = invoice.id)
3. 11.489 11.489 ↑ 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.019..11.489 rows=71,185 loops=1)

4. 47.870 209.333 ↓ 1.0 31,375 1

Hash (cost=4,508.97..4,508.97 rows=30,405 width=1,084) (actual time=209.333..209.333 rows=31,375 loops=1)

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

Hash Join (cost=3,128.96..4,508.97 rows=30,405 width=1,084) (actual time=76.985..161.463 rows=31,375 loops=1)

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

Hash Right Join (cost=2,432.44..3,732.61 rows=30,405 width=821) (actual time=64.596..123.163 rows=31,375 loops=1)

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

Hash Left Join (cost=736.16..1,625.77 rows=28,403 width=507) (actual time=17.726..48.720 rows=28,403 loops=1)

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

Seq Scan on invoice_line invoice_line_1 (cost=0.00..815.03 rows=28,403 width=137) (actual time=0.018..4.222 rows=28,403 loops=1)

9. 9.574 17.648 ↑ 1.0 18,096 1

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

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

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

11. 10.423 46.809 ↓ 1.1 21,146 1

Hash (cost=1,444.55..1,444.55 rows=20,138 width=314) (actual time=46.809..46.809 rows=21,146 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 6,149kB
12. 12.240 36.386 ↓ 1.1 21,146 1

Hash Right Join (cost=1,040.28..1,444.55 rows=20,138 width=314) (actual time=22.086..36.386 rows=21,146 loops=1)

  • Hash Cond: (invoice_send_1.invoice_id = invoice.id)
13. 2.173 2.173 ↑ 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.017..2.173 rows=20,140 loops=1)

14. 7.405 21.973 ↓ 1.0 18,812 1

Hash (cost=805.15..805.15 rows=18,810 width=276) (actual time=21.973..21.973 rows=18,812 loops=1)

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

Seq Scan on invoice (cost=0.00..805.15 rows=18,810 width=276) (actual time=0.031..14.568 rows=18,812 loops=1)

  • Filter: ((invoice_key)::text ~~* '%%'::text)
16. 6.741 12.340 ↑ 1.0 18,201 1

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

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

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

18. 0.368 27.338 ↓ 1.7 2,846 1

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

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

HashAggregate (cost=1,621.35..1,638.35 rows=1,700 width=8) (actual time=26.477..26.970 rows=2,846 loops=1)

  • Group Key: invoice_1.id
20. 3.189 24.542 ↓ 5.8 9,791 1

Nested Loop (cost=190.60..1,617.10 rows=1,700 width=8) (actual time=0.679..24.542 rows=9,791 loops=1)

21. 7.578 11.562 ↓ 5.8 9,791 1

Hash Join (cost=190.32..1,079.93 rows=1,700 width=4) (actual time=0.667..11.562 rows=9,791 loops=1)

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

Seq Scan on invoice_line invoice_line_2 (cost=0.00..815.03 rows=28,403 width=8) (actual time=0.011..3.388 rows=28,403 loops=1)

23. 0.121 0.596 ↑ 1.0 1,083 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 55kB
24. 0.475 0.475 ↑ 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.017..0.475 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 : 2.156 ms
Execution time : 473.608 ms