explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RdyK

Settings
# exclusive inclusive rows x rows loops node
1. 0.770 182.223 ↓ 8.9 1,950 1

GroupAggregate (cost=328,018.01..328,530.31 rows=220 width=4) (actual time=174.718..182.223 rows=1,950 loops=1)

  • Group Key: si.supplier_invoice_id
  • Filter: (count(1) > 0)
2. 1.284 181.453 ↓ 15.7 3,454 1

Merge Join (cost=328,018.01..328,526.46 rows=220 width=4) (actual time=174.707..181.453 rows=3,454 loops=1)

  • Merge Cond: (sii.supplier_invoice_id = si.supplier_invoice_id)
3. 1.057 174.990 ↓ 15.7 3,454 1

Sort (cost=328,017.73..328,018.28 rows=220 width=4) (actual time=174.686..174.990 rows=3,454 loops=1)

  • Sort Key: sii.supplier_invoice_id
  • Sort Method: quicksort Memory: 258kB
4. 8.126 173.933 ↓ 15.7 3,454 1

Hash Anti Join (cost=326,272.84..328,009.17 rows=220 width=4) (actual time=155.683..173.933 rows=3,454 loops=1)

  • Hash Cond: (sii.supplier_invoice_issue_id = sii_1.supplier_invoice_issue_id)
5. 11.508 11.508 ↓ 1.0 23,194 1

Seq Scan on supplier_invoice_issue sii (cost=0.00..1,474.97 rows=23,190 width=8) (actual time=0.008..11.508 rows=23,194 loops=1)

  • Filter: ((NOT orphan) AND (severity <> 'notice'::text))
  • Rows Removed by Filter: 4417
6. 3.029 154.299 ↑ 1.4 19,845 1

Hash (cost=325,930.15..325,930.15 rows=27,415 width=4) (actual time=154.299..154.299 rows=19,845 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 698kB
7. 8.862 151.270 ↑ 1.4 19,845 1

HashAggregate (cost=325,381.85..325,656.00 rows=27,415 width=4) (actual time=147.192..151.270 rows=19,845 loops=1)

  • Group Key: sii_1.supplier_invoice_issue_id
8.          

CTE case_ticket_issue_types

9. 0.001 0.001 ↑ 1.0 3 1

Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=32) (actual time=0.001..0.001 rows=3 loops=1)

10.          

CTE case_ticket_resolved_issues

11. 13.927 57.096 ↑ 23,932.7 94 1

Nested Loop (cost=0.01..199,712.78 rows=2,249,676 width=374) (actual time=8.456..57.096 rows=94 loops=1)

12. 20.442 20.442 ↓ 1.0 22,727 1

Seq Scan on case_ticket ct (cost=0.00..8,149.45 rows=22,724 width=342) (actual time=0.012..20.442 rows=22,727 loops=1)

  • Filter: (case_ticket_status = 'closed'::text)
  • Rows Removed by Filter: 2589
13. 22.727 22.727 ↓ 0.0 0 22,727

Function Scan on jsonb_array_elements c (cost=0.01..1.50 rows=99 width=32) (actual time=0.001..0.001 rows=0 loops=22,727)

  • Filter: (((item ->> 'reference'::text) IS NOT NULL) AND ((item ->> 'issueType'::text) IS NOT NULL))
  • Rows Removed by Filter: 2
14. 5.644 142.408 ↑ 1.4 20,157 1

Hash Left Join (cost=4,544.43..125,600.50 rows=27,415 width=4) (actual time=87.661..142.408 rows=20,157 loops=1)

  • Hash Cond: ((sii_1.issue_type)::text = ctit.issue_type)
  • Filter: (sii_1.ignored OR ((ctit.issue_type IS NOT NULL) AND (ctrs.reference IS NOT NULL)) OR ((ctit.issue_type IS NULL) AND (siic.supplier_invoice_issue_id IS NOT NULL)))
  • Rows Removed by Filter: 7766
15. 7.461 136.758 ↓ 1.0 27,923 1

Hash Right Join (cost=4,544.33..125,464.67 rows=27,678 width=61) (actual time=87.645..136.758 rows=27,923 loops=1)

  • Hash Cond: ((ctrs.reference = sii_1.reference) AND (ctrs.invoice_id = si_1.supplier_invoice_id) AND (ctrs.issue_type = (sii_1.issue_type)::text))
16. 57.156 57.156 ↑ 23,932.7 94 1

CTE Scan on case_ticket_resolved_issues ctrs (cost=0.00..44,993.52 rows=2,249,676 width=68) (actual time=8.459..57.156 rows=94 loops=1)

17. 7.892 72.141 ↓ 1.0 27,923 1

Hash (cost=4,059.96..4,059.96 rows=27,678 width=36) (actual time=72.141..72.141 rows=27,923 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1858kB
18. 13.176 64.249 ↓ 1.0 27,923 1

Hash Join (cost=2,328.17..4,059.96 rows=27,678 width=36) (actual time=23.999..64.249 rows=27,923 loops=1)

  • Hash Cond: (sii_1.supplier_invoice_id = si_1.supplier_invoice_id)
19. 20.062 44.854 ↓ 1.0 27,923 1

Hash Right Join (cost=1,751.76..2,895.39 rows=27,678 width=36) (actual time=17.771..44.854 rows=27,923 loops=1)

  • Hash Cond: (siic.supplier_invoice_issue_id = sii_1.supplier_invoice_issue_id)
20. 7.054 7.054 ↓ 1.0 19,024 1

Seq Scan on supplier_invoice_issue_change siic (cost=0.00..739.56 rows=19,015 width=4) (actual time=0.014..7.054 rows=19,024 loops=1)

  • Filter: ((change_type)::text <> 'ignore'::text)
  • Rows Removed by Filter: 1110
21. 4.532 17.738 ↑ 1.0 27,611 1

Hash (cost=1,405.78..1,405.78 rows=27,678 width=32) (actual time=17.738..17.738 rows=27,611 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1751kB
22. 13.206 13.206 ↑ 1.0 27,611 1

Seq Scan on supplier_invoice_issue sii_1 (cost=0.00..1,405.78 rows=27,678 width=32) (actual time=0.004..13.206 rows=27,611 loops=1)

23. 0.846 6.219 ↑ 1.0 7,092 1

Hash (cost=487.77..487.77 rows=7,092 width=4) (actual time=6.219..6.219 rows=7,092 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 250kB
24. 5.373 5.373 ↑ 1.0 7,092 1

Index Only Scan using supplier_invoice_pkey on supplier_invoice si_1 (cost=0.28..487.77 rows=7,092 width=4) (actual time=0.009..5.373 rows=7,092 loops=1)

  • Heap Fetches: 4740
25. 0.002 0.006 ↑ 1.0 3 1

Hash (cost=0.06..0.06 rows=3 width=32) (actual time=0.006..0.006 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
26. 0.004 0.004 ↑ 1.0 3 1

CTE Scan on case_ticket_issue_types ctit (cost=0.00..0.06 rows=3 width=32) (actual time=0.003..0.004 rows=3 loops=1)

27. 5.179 5.179 ↓ 1.2 8,596 1

Index Only Scan using supplier_invoice_pkey on supplier_invoice si (cost=0.28..487.77 rows=7,092 width=4) (actual time=0.018..5.179 rows=8,596 loops=1)

  • Heap Fetches: 5868
Planning time : 0.943 ms
Execution time : 182.534 ms