explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xx4H

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 455.312 1,073.722 ↓ 5.2 54,510 1

Sort (cost=11,843.28..11,869.27 rows=10,397 width=1,588) (actual time=1,064.628..1,073.722 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. 69.632 618.410 ↓ 5.2 54,510 1

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

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

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

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

5. 58.968 287.450 ↓ 1.0 31,375 1

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

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

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

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

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

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

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

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

10. 12.007 31.002 ↑ 1.0 18,096 1

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

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

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

12. 11.706 88.777 ↓ 1.1 21,146 1

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

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

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

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

15. 8.197 53.204 ↓ 1.0 18,812 1

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

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

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

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

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,534kB
18. 15.530 15.530 ↑ 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..15.530 rows=18,201 loops=1)

19. 0.362 28.824 ↓ 1.7 2,846 1

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

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

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

  • Group Key: invoice_1.id
21. 3.551 25.773 ↓ 5.8 9,791 1

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

22. 7.861 12.431 ↓ 5.8 9,791 1

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

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

24. 0.123 0.632 ↑ 1.0 1,083 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 55kB
25. 0.509 0.509 ↑ 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.015..0.509 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 : 38.343 ms
Execution time : 1,128.528 ms