explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NDiA

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 19,840.944 ↓ 0.0 0 1

Unique (cost=9,550,098.87..9,668,481.17 rows=1,392,733 width=4,426) (actual time=19,840.944..19,840.944 rows=0 loops=1)

  • ((attype.id)::text = '2'::text) AND (at.issubscribed IS TRUE)) THEN true WHEN ((s.id IS NOT NULL) AND (s.zaakid IS NOT NULL)) THEN true ELSE NULL::boolean END), (CASE WHEN ((at.id IS NOT NULL) AND ((attype.id)::text = '2':
  • Planning time: 11.931 ms
  • Execution time: 19856.470 ms
2. 0.065 19,840.943 ↓ 0.0 0 1

Sort (cost=9,550,098.87..9,553,580.70 rows=1,392,733 width=4,426) (actual time=19,840.943..19,840.943 rows=0 loops=1)

  • Sort Key: a.createddate DESC NULLS LAST, a.id, a.entityid, a.entityzaakid, a.filename, a.title, a.status, a.contenttype, a.changeddate, a.datereceived, a.datesent, a.lastmodifieddate, a.mksdocumentid, a.issubscribe
  • Sort Method: quicksort Memory: 25kB
3. 1,697.197 19,840.878 ↓ 0.0 0 1

Hash Left Join (cost=177,404.77..1,453,478.54 rows=1,392,733 width=4,426) (actual time=19,840.878..19,840.878 rows=0 loops=1)

  • Hash Cond: ((a.entityid)::text = (questionnaire.id)::text)
  • Filter: ((((a.entityid)::text = '15aaaee6-2a41-4f0e-b9f3-71d6ee93a9a6'::text) AND ((a.documentsource)::text <> 'Toestemmingsverklaring'::text)) OR ((pt.deletedprogress_id IS NULL) AND (((pt.case_id)::text = '
  • Rows Removed by Filter: 1716775
4. 769.434 18,017.448 ↑ 1.1 1,716,775 1

Hash Left Join (cost=159,328.69..1,403,983.74 rows=1,877,307 width=4,391) (actual time=1,435.791..18,017.448 rows=1,716,775 loops=1)

  • Hash Cond: ((a.entityid)::text = (diagnose.id)::text)
5. 733.574 17,234.674 ↑ 1.1 1,716,775 1

Hash Left Join (cost=158,184.68..1,394,663.72 rows=1,877,307 width=4,280) (actual time=1,422.405..17,234.674 rows=1,716,775 loops=1)

  • Hash Cond: ((a.entityid)::text = (idc.id)::text)
6. 470.775 16,482.702 ↑ 1.0 1,716,775 1

Nested Loop Left Join (cost=156,755.65..1,386,767.87 rows=1,720,721 width=4,206) (actual time=1,403.995..16,482.702 rows=1,716,775 loops=1)

7. 928.449 10,861.602 ↑ 1.0 1,716,775 1

Hash Left Join (cost=156,755.23..587,740.43 rows=1,720,721 width=4,183) (actual time=1,403.958..10,861.602 rows=1,716,775 loops=1)

  • Hash Cond: ((a.documenttemplatetype_id)::text = (dtt.id)::text)
8. 559.760 9,933.140 ↑ 1.0 1,716,775 1

Hash Left Join (cost=156,753.92..564,415.32 rows=1,720,721 width=4,171) (actual time=1,403.922..9,933.140 rows=1,716,775 loops=1)

  • Hash Cond: ((at.id)::text = (rp.action_id)::text)
9. 697.483 9,346.369 ↑ 1.0 1,716,775 1

Merge Left Join (cost=153,808.98..548,394.89 rows=1,720,721 width=4,162) (actual time=1,376.764..9,346.369 rows=1,716,775 loops=1)

  • Merge Cond: ((a.entityid)::text = (c.id)::text)
10. 2,716.332 8,490.776 ↑ 1.0 1,716,775 1

Nested Loop (cost=153,808.56..534,928.68 rows=1,720,721 width=3,609) (actual time=1,376.738..8,490.776 rows=1,716,775 loops=1)

  • Join Filter: ((dtr.documenttype_id)::text = (a.documenttemplatetype_id)::text)
  • Rows Removed by Join Filter: 14478309
11. 103.188 4,583.629 ↑ 1.0 238,163 1

Merge Left Join (cost=153,807.29..291,685.26 rows=238,468 width=3,609) (actual time=1,376.650..4,583.629 rows=238,163 loops=1)

  • Merge Cond: ((a.entityid)::text = (ad.id)::text)
12. 113.327 4,464.950 ↑ 1.0 238,163 1

Merge Left Join (cost=152,838.79..289,935.42 rows=238,468 width=3,498) (actual time=1,364.185..4,464.950 rows=238,163 loops=1)

  • Merge Cond: ((a.entityid)::text = (s.id)::text)
13. 235.669 4,196.555 ↑ 1.0 238,163 1

Merge Left Join (cost=152,838.38..281,738.31 rows=238,468 width=2,871) (actual time=1,364.143..4,196.555 rows=238,163 loops=1)

  • Merge Cond: ((a.entityid)::text = (pt.id)::text)
14. 1,884.148 2,605.609 ↑ 1.0 238,163 1

Merge Left Join (cost=47,454.92..170,417.13 rows=238,468 width=2,686) (actual time=506.593..2,605.609 rows=238,163 loops=1)

  • Merge Cond: ((a.entityid)::text = (at.id)::text)
  • -> Index Scan using idx_document_entityid on document a (cost=0.42..118382.91 rows=238468 width=2570) (actual time=0.004..1730.976 rows=2381
  • Filter: (isdeleted IS FALSE)
  • Rows Removed by Filter: 6714
15. 31.966 721.461 ↓ 1.0 191,032 1

Materialize (cost=47,454.49..48,408.50 rows=190,801 width=116) (actual time=506.584..721.461 rows=191,032 loops=1)

16. 482.611 689.495 ↓ 1.0 190,895 1

Sort (cost=47,454.49..47,931.49 rows=190,801 width=116) (actual time=506.573..689.495 rows=190,895 loops=1)

  • Sort Key: at.id
  • Sort Method: external merge Disk: 16704kB
17. 58.491 206.884 ↓ 1.0 190,895 1

Hash Left Join (cost=1.09..18,980.61 rows=190,801 width=116) (actual time=0.025..206.884 rows=190,895 loops=1)

  • Hash Cond: ((at.actiontype_id)::text = (attype.id)::text)
18. 148.387 148.387 ↓ 1.0 190,895 1

Seq Scan on actiontable at (cost=0.00..16,356.01 rows=190,801 width=114) (actual time=0.007..148.387 rows=190,895 loops=1)

19. 0.005 0.006 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=2) (actual time=0.006..0.006 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.001 0.001 ↑ 1.0 4 1

Seq Scan on actiontype attype (cost=0.00..1.04 rows=4 width=2) (actual time=0.001..0.001 rows=4 loops=1)

21. 69.638 1,355.277 ↓ 1.1 436,121 1

Materialize (cost=105,383.46..107,386.03 rows=400,514 width=185) (actual time=857.541..1,355.277 rows=436,121 loops=1)

22. 1,038.169 1,285.639 ↓ 1.0 400,721 1

Sort (cost=105,383.46..106,384.74 rows=400,514 width=185) (actual time=857.537..1,285.639 rows=400,721 loops=1)

  • Sort Key: pt.id
  • Sort Method: external merge Disk: 32992kB
23. 247.470 247.470 ↓ 1.0 400,721 1

Seq Scan on progresstable pt (cost=0.00..31,149.14 rows=400,514 width=185) (actual time=0.022..247.470 rows=400,721 loops=1)

24. 7.748 155.068 ↓ 1.0 22,204 1

Materialize (cost=0.41..7,081.03 rows=21,301 width=627) (actual time=0.040..155.068 rows=22,204 loops=1)

25. 147.320 147.320 ↓ 1.0 21,309 1

Index Scan using signaltable_pkey on signaltable s (cost=0.41..7,027.77 rows=21,301 width=627) (actual time=0.036..147.320 rows=21,309 loops=1)

26. 9.748 15.491 ↓ 1.0 6,883 1

Sort (cost=968.50..985.70 rows=6,882 width=111) (actual time=12.459..15.491 rows=6,883 loops=1)

  • Sort Key: ad.id
  • Sort Method: quicksort Memory: 1160kB
27. 5.743 5.743 ↓ 1.0 6,883 1

Seq Scan on actiondiagnose ad (cost=0.00..529.82 rows=6,882 width=111) (actual time=0.014..5.743 rows=6,883 loops=1)

28. 1,190.707 1,190.815 ↑ 1.0 68 238,163

Materialize (cost=1.27..6.22 rows=68 width=37) (actual time=0.000..0.005 rows=68 loops=238,163)

29. 0.055 0.108 ↑ 1.0 68 1

Hash Join (cost=1.27..5.88 rows=68 width=37) (actual time=0.063..0.108 rows=68 loops=1)

  • Hash Cond: ((dtr.role_id)::text = (role.id)::text)
30. 0.014 0.014 ↑ 1.0 68 1

Seq Scan on documenttyperoles dtr (cost=0.00..3.68 rows=68 width=74) (actual time=0.003..0.014 rows=68 loops=1)

31. 0.006 0.039 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=37) (actual time=0.039..0.039 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.033 0.033 ↑ 1.0 12 1

Seq Scan on roletable role (cost=0.00..1.12 rows=12 width=37) (actual time=0.012..0.033 rows=12 loops=1)

33. 16.606 158.110 ↓ 7.2 129,932 1

Materialize (cost=0.41..5,729.10 rows=17,924 width=553) (actual time=0.022..158.110 rows=129,932 loops=1)

34. 141.504 141.504 ↓ 1.0 17,931 1

Index Scan using casetable_pkey on casetable c (cost=0.41..5,684.29 rows=17,924 width=553) (actual time=0.020..141.504 rows=17,931 loops=1)

35. 8.485 27.011 ↓ 1.0 25,744 1

Hash (cost=2,623.24..2,623.24 rows=25,736 width=46) (actual time=27.011..27.011 rows=25,744 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2233kB
36. 18.526 18.526 ↓ 1.0 25,744 1

Seq Scan on requestprovision rp (cost=0.00..2,623.24 rows=25,736 width=46) (actual time=0.008..18.526 rows=25,744 loops=1)

  • Filter: (zaakid IS NOT NULL)
  • Rows Removed by Filter: 290
37. 0.008 0.013 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=49) (actual time=0.013..0.013 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
38. 0.005 0.005 ↑ 1.0 14 1

Seq Scan on documenttemplatetype dtt (cost=0.00..1.14 rows=14 width=49) (actual time=0.003..0.005 rows=14 loops=1)

39. 5,150.325 5,150.325 ↑ 1.0 1 1,716,775

Index Scan using baseusertable_pkey on baseusertable bua (cost=0.41..0.45 rows=1 width=97) (actual time=0.003..0.003 rows=1 loops=1,716,775)

  • Index Cond: ((a.createdby_id)::text = (id)::text)
40. 0.079 18.398 ↓ 1.4 149 1

Hash (cost=1,427.67..1,427.67 rows=109 width=74) (actual time=18.398..18.398 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
41. 0.177 18.319 ↓ 1.4 149 1

Nested Loop Left Join (cost=5.66..1,427.67 rows=109 width=74) (actual time=0.147..18.319 rows=149 loops=1)

42. 7.841 16.801 ↓ 1.4 149 1

Hash Right Join (cost=5.25..1,377.20 rows=109 width=74) (actual time=0.101..16.801 rows=149 loops=1)

  • Hash Cond: ((cct.citizen_id)::text = (idc.citizen_id)::text)
43. 8.882 8.882 ↓ 1.0 35,943 1

Seq Scan on caseclienttable cct (cost=0.00..1,236.12 rows=35,931 width=74) (actual time=0.009..8.882 rows=35,943 loops=1)

  • Filter: (deletedclient_id IS NULL)
  • Rows Removed by Filter: 1077
44. 0.031 0.078 ↑ 1.0 100 1

Hash (cost=4.00..4.00 rows=100 width=74) (actual time=0.078..0.078 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
45. 0.047 0.047 ↑ 1.0 100 1

Seq Scan on identificationdocument idc (cost=0.00..4.00 rows=100 width=74) (actual time=0.014..0.047 rows=100 loops=1)

46. 1.341 1.341 ↑ 1.0 1 149

Index Only Scan using casetable_pkey on casetable ct (cost=0.41..0.45 rows=1 width=37) (actual time=0.009..0.009 rows=1 loops=149)

  • Index Cond: (id = (cct.case_id)::text)
  • Heap Fetches: 1
47. 2.911 13.340 ↓ 1.0 8,050 1

Hash (cost=1,043.45..1,043.45 rows=8,045 width=111) (actual time=13.340..13.340 rows=8,050 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 930kB
48. 10.429 10.429 ↓ 1.0 8,050 1

Seq Scan on diagnosetable diagnose (cost=0.00..1,043.45 rows=8,045 width=111) (actual time=0.012..10.429 rows=8,050 loops=1)

49. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=10.10..10.10 rows=10 width=1,548) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
50. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on questionnaireintake questionnaire (cost=0.00..10.10 rows=10 width=1,548) (actual time=0.000..0.000 rows=0 loops=1)

51.          

SubPlan (forHash Left Join)

52. 115.288 115.288 ↓ 0.0 0 1

Seq Scan on actiontable ac (cost=0.00..16,833.01 rows=17 width=37) (actual time=115.288..115.288 rows=0 loops=1)

  • Filter: ((case_id)::text = '15aaaee6-2a41-4f0e-b9f3-71d6ee93a9a6'::text)
  • Rows Removed by Filter: 190895
53. 10.944 10.944 ↓ 0.0 0 1

Seq Scan on trajecttable t (cost=0.00..1,232.79 rows=3 width=37) (actual time=10.944..10.944 rows=0 loops=1)

  • Filter: ((case_id)::text = '15aaaee6-2a41-4f0e-b9f3-71d6ee93a9a6'::text)
  • Rows Removed by Filter: 35446