explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n2PR

Settings
# exclusive inclusive rows x rows loops node
1. 0.693 291.622 ↑ 1.0 1 1

Hash Join (cost=329,442.99..329,549.59 rows=1 width=371) (actual time=290.723..291.622 rows=1 loops=1)

  • Hash Cond: (si_1.supplier_invoice_id = si.supplier_invoice_id)
2. 3.722 290.917 ↑ 1.0 7,092 1

Hash Right Join (cost=329,439.88..329,448.95 rows=7,092 width=11) (actual time=268.934..290.917 rows=7,092 loops=1)

  • Hash Cond: (iwnri.supplier_invoice_id = si_1.supplier_invoice_id)
3.          

CTE invoice_with_not_resolved_issues

4. 1.230 277.622 ↓ 8.9 1,950 1

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

  • Group Key: si_2.supplier_invoice_id
  • Filter: (count(1) > 0)
5. 1.877 276.392 ↓ 15.7 3,454 1

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

  • Merge Cond: (sii.supplier_invoice_id = si_2.supplier_invoice_id)
6. 1.601 260.464 ↓ 15.7 3,454 1

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

  • Sort Key: sii.supplier_invoice_id
  • Sort Method: quicksort Memory: 258kB
7. 11.624 258.863 ↓ 15.7 3,454 1

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

  • Hash Cond: (sii.supplier_invoice_issue_id = sii_1.supplier_invoice_issue_id)
8. 21.055 21.055 ↓ 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.012..21.055 rows=23,194 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 698kB
10. 11.798 222.070 ↑ 1.4 19,845 1

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

  • Group Key: sii_1.supplier_invoice_issue_id
11.          

CTE case_ticket_issue_types

12. 0.004 0.004 ↑ 1.0 3 1

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

13.          

CTE case_ticket_resolved_issues

14. 17.647 107.960 ↑ 23,932.7 94 1

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

15. 44.859 44.859 ↓ 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.013..44.859 rows=22,727 loops=1)

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

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

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

Hash Left Join (cost=4,544.43..125,600.50 rows=27,415 width=4) (actual time=114.200..210.272 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
18. 9.298 202.456 ↓ 1.0 27,923 1

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

  • Hash Cond: ((ctrs.reference = sii_1.reference) AND (ctrs.invoice_id = si_3.supplier_invoice_id) AND (ctrs.issue_type = (sii_1.issue_type)::text))
19. 108.044 108.044 ↑ 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=15.253..108.044 rows=94 loops=1)

20. 12.734 85.114 ↓ 1.0 27,923 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1858kB
21. 14.639 72.380 ↓ 1.0 27,923 1

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

  • Hash Cond: (sii_1.supplier_invoice_id = si_3.supplier_invoice_id)
22. 22.584 52.302 ↓ 1.0 27,923 1

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

  • Hash Cond: (siic.supplier_invoice_issue_id = sii_1.supplier_invoice_issue_id)
23. 12.209 12.209 ↓ 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.011..12.209 rows=19,024 loops=1)

  • Filter: ((change_type)::text <> 'ignore'::text)
  • Rows Removed by Filter: 1110
24. 5.040 17.509 ↑ 1.0 27,611 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1751kB
25. 12.469 12.469 ↑ 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.003..12.469 rows=27,611 loops=1)

26. 1.042 5.439 ↑ 1.0 7,092 1

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

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

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

  • Heap Fetches: 4622
28. 0.002 0.008 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
29. 0.006 0.006 ↑ 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.006 rows=3 loops=1)

30. 14.051 14.051 ↓ 1.2 8,596 1

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

  • Heap Fetches: 5733
31. 278.328 278.328 ↓ 8.9 1,950 1

CTE Scan on invoice_with_not_resolved_issues iwnri (cost=0.00..4.40 rows=220 width=4) (actual time=260.055..278.328 rows=1,950 loops=1)

32. 0.970 8.867 ↑ 1.0 7,092 1

Hash (cost=820.92..820.92 rows=7,092 width=7) (actual time=8.867..8.867 rows=7,092 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 271kB
33. 7.897 7.897 ↑ 1.0 7,092 1

Seq Scan on supplier_invoice si_1 (cost=0.00..820.92 rows=7,092 width=7) (actual time=0.004..7.897 rows=7,092 loops=1)

34. 0.002 0.012 ↑ 1.0 1 1

Hash (cost=3.10..3.10 rows=1 width=339) (actual time=0.012..0.012 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
35. 0.010 0.010 ↑ 1.0 1 1

Index Scan using supplier_invoice_pkey on supplier_invoice si (cost=0.28..3.10 rows=1 width=339) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (supplier_invoice_id = ANY ('{6955}'::integer[]))
Planning time : 4.146 ms
Execution time : 293.126 ms