explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 42.945 481.055 ↓ 5.2 54,510 1

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

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

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

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

4. 48.224 215.039 ↓ 1.0 31,375 1

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

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

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

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

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

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

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

  • Hash Cond: (invoice_line_1.employer_id = employer_1.employer_id)
8. 4.309 4.309 ↑ 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.016..4.309 rows=28,403 loops=1)

9. 10.723 18.902 ↑ 1.0 18,096 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,848kB
10. 8.179 8.179 ↑ 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.179 rows=18,096 loops=1)

11. 11.221 48.757 ↓ 1.1 21,146 1

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

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

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

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

14. 7.110 21.825 ↓ 1.0 18,812 1

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

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

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

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

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

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

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

18. 0.398 28.182 ↓ 1.7 2,846 1

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

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

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

  • Group Key: invoice_1.id
20. 3.272 25.276 ↓ 5.8 9,791 1

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

21. 7.765 12.213 ↓ 5.8 9,791 1

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

  • Hash Cond: (invoice_line_2.employer_id = employer.employer_id)
22. 3.833 3.833 ↑ 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.013..3.833 rows=28,403 loops=1)

23. 0.133 0.615 ↑ 1.0 1,083 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 55kB
24. 0.482 0.482 ↑ 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.482 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.452 ms
Execution time : 484.363 ms