explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bJql : Optimization for: plan #Xx4H

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 459.117 988.877 ↓ 5.2 54,510 1

Sort (cost=11,843.28..11,869.27 rows=10,397 width=1,588) (actual time=980.143..988.877 rows=54,510 loops=1)

  • Sort Key: (upper(invoice.effective_daterange)) DESC NULLS LAST, invoice.id DESC, invoice_line_1.employer_id, (lower((invoice_line_1.name)::text))
  • Sort Method: quicksort Memory: 53,944kB
2. 68.559 529.760 ↓ 5.2 54,510 1

Hash Join (cost=6,548.63..11,149.60 rows=10,397 width=1,588) (actual time=256.984..529.760 rows=54,510 loops=1)

  • Hash Cond: (invoice.id = invoice_line_2.invoice_id)
3. 189.479 431.578 ↓ 1.3 151,171 1

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

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

5. 57.598 227.196 ↓ 1.0 31,375 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 17,660kB
6. 25.778 169.598 ↓ 1.0 31,375 1

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

  • Hash Cond: (invoice.bill_to_contact_id = contact_1.id)
7. 28.820 130.342 ↓ 1.0 31,375 1

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

  • Hash Cond: (invoice_line_1.invoice_id = invoice.id)
8. 27.717 50.761 ↑ 1.0 28,403 1

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

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

10. 10.086 18.752 ↑ 1.0 18,096 1

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

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

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

12. 13.080 50.761 ↓ 1.1 21,146 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 6,149kB
13. 12.520 37.681 ↓ 1.1 21,146 1

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

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

15. 7.746 22.610 ↓ 1.0 18,812 1

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

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

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

  • Filter: ((invoice_key)::text ~~* '%%'::text)
17. 7.207 13.478 ↑ 1.0 18,201 1

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

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

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

19. 0.400 29.623 ↓ 1.7 2,846 1

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

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 144kB
20. 2.498 29.223 ↓ 1.7 2,846 1

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

  • Group Key: invoice_1.id
21. 3.043 26.725 ↓ 5.8 9,791 1

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

22. 7.890 13.891 ↓ 5.8 9,791 1

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

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

24. 0.175 1.153 ↑ 1.0 1,083 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 55kB
25. 0.978 0.978 ↑ 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.063..0.978 rows=1,083 loops=1)

  • Index Cond: (tenant_id = 651)
26. 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.044 ms
Execution time : 1,044.014 ms