explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q86w : Optimization for: plan #9jLR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=3,780,538.10..4,018,122.02 rows=2,795,105 width=865) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=3,780,538.10..3,787,525.86 rows=2,795,105 width=865) (actual rows= loops=)

  • 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.issubscribed, a.confidentialindication, a.version, a.language, a.hyperlink, (CASE WHEN (dtt.name IS NULL) THEN 'Bijlage'::character varying ELSE dtt.name END), (CASE WHEN (dtt.id IS NULL) THEN '41207e5d-eca5-4687-91cf-9fae960f5419'::character varying ELSE dtt.id END), bua.username, bua.name, bua.surname, bua.initials, bua.formatedname, bua.prefixv, (CASE WHEN (pt.id IS NOT NULL) THEN 'Verslag'::text WHEN (s.id IS NOT NULL) THEN 'Signaal'::text WHEN (at.id IS NOT NULL) THEN 'Actie'::text WHEN (ad.id IS NOT NULL) THEN 'ZRM actie'::text WHEN (idc.id IS NOT NULL) THEN 'Persoon'::text WHEN (diagnose.id IS NOT NULL) THEN 'Zelfredzaamheid'::text WHEN ((c.id IS NOT NULL) AND ((a.documentsource)::text = 'Toestemmingsverklaring'::text)) THEN 'Toestemmingsverklaring'::text WHEN (c.id IS NOT NULL) THEN 'Casus'::text WHEN (questionnaire.id IS NOT NULL) THEN 'Vragenlijst'::text ELSE NULL::text END), (CASE WHEN (pt.id IS NOT NULL) THEN true WHEN ((at.id IS NOT NULL) AND (at.issubscribed OR ((at.actiontype_id)::text <> '2'::text))) THEN true WHEN (diagnose.id IS NOT NULL) THEN true WHEN ((c.id IS NOT NULL) AND ((a.documentsource)::text = 'Toestemmingsverklaring'::text)) THEN true WHEN (c.id IS NOT NULL) THEN true WHEN (s.id IS NOT NULL) THEN true WHEN (questionnaire.id IS NOT NULL) THEN true ELSE false END), (CASE WHEN ((at.id IS NOT NULL) AND ((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'::text) AND (at.issubscribed IS TRUE)) THEN true WHEN ((c.id IS NOT NULL) AND (c.zaakid IS NOT NULL)) THEN true WHEN ((s.id IS NOT NULL) AND (s.zaakid IS NOT NULL)) THEN true ELSE false END), (CASE WHEN ((at.id IS NOT NULL) AND ((attype.id)::text = '1'::text)) THEN 'VTO'::character varying WHEN ((at.id IS NOT NULL) AND ((attype.id)::text = '2'::text) AND (rp.zaakid IS NOT NULL)) THEN rp.zaakid WHEN ((s.id IS NOT NULL) AND (s.zaakid IS NOT NULL)) THEN s.zaakid ELSE NULL::character varying END), a.origin, a.createdbyname
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,773.18..271,252.12 rows=2,795,105 width=865) (actual rows= loops=)

  • Hash Cond: ((a.entityid)::text = (questionnaire.id)::text)
  • Filter: ((((a.entityid)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) AND ((a.documentsource)::text <> 'Toestemmingsverklaring'::text)) OR ((pt.deletedprogress_id IS NULL) AND (((pt.case_id)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) OR (hashed SubPlan 1) OR (hashed SubPlan 2))) OR (((s.case_id)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) AND (s.deletedsignal_id IS NULL)) OR (((at.case_id)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) AND (at.deletedaction_id IS NULL)) OR ((ct.id)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) OR (((a.entityid)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) AND ((a.documentsource)::text = 'Toestemmingsverklaring'::text)) OR (((diagnose.case_id)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) AND (diagnose.deleteddiagnose_id IS NULL)) OR (((questionnaire.case_id)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) AND (questionnaire.deleteddiagnose_id IS NULL)) OR (((ad.case_id)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text) AND (ad.deletedaction_id IS NULL)))
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,665.63..198,701.94 rows=3,727,612 width=2,556) (actual rows= loops=)

  • Hash Cond: ((a.entityid)::text = (diagnose.id)::text)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,617.50..166,156.88 rows=3,727,612 width=2,445) (actual rows= loops=)

  • Hash Cond: ((a.entityid)::text = (idc.id)::text)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,483.91..156,711.39 rows=1,790,428 width=1,892) (actual rows= loops=)

  • Hash Cond: ((a.createdby_id)::text = (bua.id)::text)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,441.02..144,672.03 rows=1,790,428 width=1,881) (actual rows= loops=)

  • Hash Cond: ((a.documenttemplatetype_id)::text = (dtt.id)::text)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,438.57..120,345.43 rows=1,790,428 width=1,869) (actual rows= loops=)

  • Hash Cond: ((a.entityid)::text = (c.id)::text)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=17,189.06..95,775.85 rows=1,790,428 width=1,823) (actual rows= loops=)

  • Hash Cond: ((at.id)::text = (rp.action_id)::text)
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=17,174.04..89,034.82 rows=1,790,428 width=1,814) (actual rows= loops=)

  • Hash Cond: ((a.documenttemplatetype_id)::text = (dtr.documenttype_id)::text)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=17,152.68..68,477.84 rows=84,203 width=1,814) (actual rows= loops=)

  • Hash Cond: ((a.entityid)::text = (ad.id)::text)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=17,134.93..67,969.30 rows=84,203 width=1,703) (actual rows= loops=)

  • Hash Cond: ((a.entityid)::text = (at.id)::text)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=17,021.33..66,805.99 rows=84,203 width=1,073) (actual rows= loops=)

  • Hash Cond: ((a.entityid)::text = (pt.id)::text)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,615.59..33,477.55 rows=84,203 width=888) (actual rows= loops=)

  • Hash Cond: ((a.entityid)::text = (s.id)::text)
15. 0.000 0.000 ↓ 0.0

Seq Scan on document a (cost=0.00..10,371.26 rows=84,203 width=768) (actual rows= loops=)

  • Filter: (isdeleted IS FALSE)
16. 0.000 0.000 ↓ 0.0

Hash (cost=2,632.93..2,632.93 rows=65,893 width=120) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on signaltable s (cost=0.00..2,632.93 rows=65,893 width=120) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=10,750.66..10,750.66 rows=42,566 width=185) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on progresstable pt (cost=0.00..10,750.66 rows=42,566 width=185) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=94.79..94.79 rows=1,505 width=630) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.07..94.79 rows=1,505 width=630) (actual rows= loops=)

  • Hash Cond: ((at.actiontype_id)::text = (attype.id)::text)
22. 0.000 0.000 ↓ 0.0

Seq Scan on actiontable at (cost=0.00..79.05 rows=1,505 width=114) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=1.03..1.03 rows=3 width=516) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on actiontype attype (cost=0.00..1.03 rows=3 width=516) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=14.00..14.00 rows=300 width=111) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on actiondiagnose ad (cost=0.00..14.00 rows=300 width=111) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=18.50..18.50 rows=229 width=37) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.06..18.50 rows=229 width=37) (actual rows= loops=)

  • Hash Cond: ((dtr.role_id)::text = (role.id)::text)
29. 0.000 0.000 ↓ 0.0

Seq Scan on documenttyperoles dtr (cost=0.00..12.29 rows=229 width=74) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=2.47..2.47 rows=47 width=37) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on roletable role (cost=0.00..2.47 rows=47 width=37) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash (cost=15.01..15.01 rows=1 width=46) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on requestprovision rp (cost=0.00..15.01 rows=1 width=46) (actual rows= loops=)

  • Filter: (zaakid IS NOT NULL)
34. 0.000 0.000 ↓ 0.0

Hash (cost=2,214.78..2,214.78 rows=2,778 width=46) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on casetable c (cost=0.00..2,214.78 rows=2,778 width=46) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=2.20..2.20 rows=20 width=49) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on documenttemplatetype dtt (cost=0.00..2.20 rows=20 width=49) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=30.73..30.73 rows=973 width=85) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on baseusertable bua (cost=0.00..30.73 rows=973 width=85) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash (cost=133.23..133.23 rows=29 width=553) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.72..133.23 rows=29 width=553) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1.32..47.38 rows=29 width=553) (actual rows= loops=)

  • Hash Cond: ((cct.citizen_id)::text = (idc.citizen_id)::text)
43. 0.000 0.000 ↓ 0.0

Seq Scan on caseclienttable cct (cost=0.00..41.97 rows=1,016 width=74) (actual rows= loops=)

  • Filter: (deletedclient_id IS NULL)
44. 0.000 0.000 ↓ 0.0

Hash (cost=1.14..1.14 rows=14 width=1,032) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on identificationdocument idc (cost=0.00..1.14 rows=14 width=1,032) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = (cct.case_id)::text)
47. 0.000 0.000 ↓ 0.0

Hash (cost=39.17..39.17 rows=717 width=111) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on diagnosetable diagnose (cost=0.00..39.17 rows=717 width=111) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash (cost=4.87..4.87 rows=87 width=111) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on questionnaireintake questionnaire (cost=0.00..4.87 rows=87 width=111) (actual rows= loops=)

51.          

SubPlan (forHash Left Join)

52. 0.000 0.000 ↓ 0.0

Seq Scan on actiontable ac (cost=0.00..82.81 rows=2 width=37) (actual rows= loops=)

  • Filter: ((case_id)::text = '3ce2460a-c2e4-45a6-b6b8-f61fdfb2c356'::text)
53. 0.000 0.000 ↓ 0.0

Seq Scan on trajecttable t (cost=0.00..18.77 rows=1 width=37) (actual rows= loops=)