explain.depesz.com

A tool for finding a real cause for slow queries.

Result: hOpx

options
# exclusive inclusive rows x rows loops node
1. 10.788 14506.508 ↑ 38.8 592 1
→

HashAggregate (cost=96491186.13..96491760.23 rows=22964 width=260) (actual time=14506.166..14506.508 rows=592 loops=1)

2.          

CTE kwclpermission
3. 0.019 0.851 ↑ 1.9 53 1
→

Nested Loop (cost=63.55..216.35 rows=103 width=8) (actual time=0.698..0.851 rows=53 loops=1)

4. 0.288 0.772 ↑ 2.7 12 1
→

Hash Join (cost=63.55..202.63 rows=32 width=8) (actual time=0.680..0.772 rows=12 loops=1)

  • Hash Cond: (param.pacs_session_permission_fk = perm.id)
5. 0.309 0.309 ↑ 1.0 2528 1
→

Seq Scan on pacs_session_parameter param (cost=0.00..129.28 rows=2528 width=16) (actual time=0.005..0.309 rows=2528 loops=1)

6. 0.003 0.175 ↑ 1.0 12 1
→

Hash (cost=63.40..63.40 rows=12 width=8) (actual time=0.175..0.175 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
7. 0.172 0.172 ↑ 1.0 12 1
→

Seq Scan on pacs_session_permission perm (cost=0.00..63.40 rows=12 width=8) (actual time=0.144..0.172 rows=12 loops=1)

  • Filter: (((permission_fk)::text = 'SearchKeywords'::text) AND (pacs_session_fk = 1925347919::bigint))
8. 0.060 0.060 ↑ 1.2 4 12
→

Index Scan using kw_cl_kw_cl_grp_fk on keyword_class kwclass (cost=0.00..0.37 rows=5 width=26) (actual time=0.004..0.005 rows=4 loops=12)

  • Index Cond: ((keyword_class_group_fk)::text = (param.value)::text)
9. 20.822 14495.720 ↑ 6.6 3485 1
→

Hash Join (cost=664655.63..96489534.53 rows=22964 width=260) (actual time=12294.440..14495.720 rows=3485 loops=1)

  • Hash Cond: (document.producer = o.id)
10. 163.051 14472.974 ↑ 6.6 3485 1
→

Nested Loop (cost=664485.42..96488589.28 rows=22964 width=239) (actual time=12292.477..14472.974 rows=3485 loops=1)

  • Join Filter: ((((((sqh.key)::text = 'ProducerWithoutMark'::text) AND (sqh.num_value1 = d.producer)) OR (((sqh.key)::text = 'OrdererWithoutMark'::text) AND (sqh.num_value1 = d.orderer)) OR (((((sqh.key)::text = 'ProducerWithMark'::text) AND (sqh.num_value1 = d.producer)) OR (((sqh.key)::text = 'OrdererWithMark'::text) AND (sqh.num_value1 = d.orderer))) AND ((SubPlan 2) > 0))) AND (SubPlan 3)) OR (((sqh.key)::text = 'ShareOrgunit'::text) AND (sqh.num_value1 = ds.orgunit_fk)) OR (((sqh.key)::text = 'ViewByPID'::text) AND ((d.index05)::text = (sqh.str_value1)::text) AND (NOT ou.view_by_pid_protection)))
11. 86.668 13168.342 ↑ 2.0 159158 1
→

Hash Join (cost=664302.70..720617.65 rows=312427 width=283) (actual time=12180.445..13168.342 rows=159158 loops=1)

  • Hash Cond: (d.producer = ou.id)
12. 615.151 13079.736 ↑ 2.0 159158 1
→

Hash Right Join (cost=664132.48..714589.43 rows=312427 width=282) (actual time=12178.499..13079.736 rows=159158 loops=1)

  • Hash Cond: (gc.document_fk = document.id)
13. 287.521 287.521 ↓ 1.0 1190892 1
→

Seq Scan on generic_container gc (cost=0.00..43439.67 rows=1190767 width=21) (actual time=0.005..287.521 rows=1190892 loops=1)

14. 174.511 12177.064 ↑ 2.0 159158 1
→

Hash (cost=660227.15..660227.15 rows=312427 width=269) (actual time=12177.064..12177.064 rows=159158 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 37683kB
15. 67.670 12002.553 ↑ 2.0 159158 1
→

Hash Left Join (cost=578859.25..660227.15 rows=312427 width=269) (actual time=10667.620..12002.553 rows=159158 loops=1)

  • Hash Cond: (document.id = ds.document_fk)
16. 53.084 11740.603 ↑ 2.0 159158 1
→

Hash Left Join (cost=566929.38..643819.99 rows=312427 width=250) (actual time=10473.275..11740.603 rows=159158 loops=1)

  • Hash Cond: (document.orderer = ord.id)
17. 554.109 11685.555 ↑ 2.0 159158 1
→

Hash Join (cost=566759.17..640499.37 rows=312427 width=229) (actual time=10471.292..11685.555 rows=159158 loops=1)

  • Hash Cond: (document.id = d.id)
18. 661.548 661.548 ↓ 1.0 1426601 1
→

Seq Scan on document (cost=0.00..65271.66 rows=1425140 width=186) (actual time=0.008..661.548 rows=1426601 loops=1)

  • Filter: ((status_deleted = 0) AND (record_type_fk = 1))
19. 155.035 10469.898 ↑ 2.0 162618 1
→

Hash (cost=562764.21..562764.21 rows=319597 width=51) (actual time=10469.898..10469.898 rows=162618 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 11193kB
20. 40.277 10314.863 ↑ 2.0 162618 1
→

Hash Left Join (cost=181397.31..562764.21 rows=319597 width=51) (actual time=1823.432..10314.863 rows=162618 loops=1)

  • Hash Cond: (d.id = ds.document_fk)
21. 248.943 10274.579 ↑ 2.0 162618 1
→

Hash Join (cost=181396.18..561564.57 rows=319597 width=43) (actual time=1823.410..10274.579 rows=162618 loops=1)

  • Hash Cond: (linker.document_fk = d.id)
22. 6460.102 8748.975 ↑ 2.0 162618 1
→

Hash Join (cost=93800.69..454887.63 rows=319597 width=8) (actual time=546.258..8748.975 rows=162618 loops=1)

  • Hash Cond: (linker.keyword_fk = kword.id)
23. 1742.806 1742.806 ↑ 1.0 14247522 1
→

Seq Scan on link_document_keyword_all linker (cost=0.00..233224.72 rows=14247572 width=16) (actual time=0.014..1742.806 rows=14247522 loops=1)

24. 28.802 546.067 ↓ 1.0 155974 1
→

Hash (cost=91920.44..91920.44 rows=150420 width=8) (actual time=546.067..546.067 rows=155974 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 6093kB
25. 60.289 517.265 ↓ 1.0 155974 1
→

Hash Join (cost=3365.41..91920.44 rows=150420 width=8) (actual time=143.206..517.265 rows=155974 loops=1)

  • Hash Cond: (kword.keyword_class_fk = kwclpermission.id)
26. 317.725 456.091 ↑ 1.2 263513 1
→

Bitmap Heap Scan on keyword kword (cost=3362.06..89240.92 rows=312524 width=16) (actual time=142.314..456.091 rows=263513 loops=1)

  • Recheck Cond: (upper((value)::text) ~~ '%2010%'::text)
27. 138.366 138.366 ↑ 1.0 309497 1
→

Bitmap Index Scan on keyword_value_upper_tri (cost=0.00..3283.93 rows=312524 width=0) (actual time=138.366..138.366 rows=309497 loops=1)

  • Index Cond: (upper((value)::text) ~~ '%2010%'::text)
28. 0.012 0.885 ↑ 1.9 53 1
→

Hash (cost=2.06..2.06 rows=103 width=8) (actual time=0.885..0.885 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
29. 0.873 0.873 ↑ 1.9 53 1
→

CTE Scan on kwclpermission (cost=0.00..2.06 rows=103 width=8) (actual time=0.701..0.873 rows=53 loops=1)

30. 445.053 1276.661 ↑ 1.0 1457777 1
→

Hash (cost=57982.44..57982.44 rows=1457844 width=35) (actual time=1276.661..1276.661 rows=1457777 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 43231kB
31. 831.608 831.608 ↑ 1.0 1457777 1
→

Seq Scan on document d (cost=0.00..57982.44 rows=1457844 width=35) (actual time=0.004..831.608 rows=1457777 loops=1)

32. 0.004 0.007 ↑ 1.0 6 1
→

Hash (cost=1.06..1.06 rows=6 width=16) (actual time=0.007..0.007 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
33. 0.003 0.003 ↑ 1.0 6 1
→

Seq Scan on document_share ds (cost=0.00..1.06 rows=6 width=16) (actual time=0.003..0.003 rows=6 loops=1)

34. 1.097 1.964 ↑ 1.0 4765 1
→

Hash (cost=110.65..110.65 rows=4765 width=37) (actual time=1.964..1.964 rows=4765 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 338kB
35. 0.867 0.867 ↑ 1.0 4765 1
→

Seq Scan on orgunit ord (cost=0.00..110.65 rows=4765 width=37) (actual time=0.005..0.867 rows=4765 loops=1)

36. 102.367 194.280 ↑ 1.0 266883 1
→

Hash (cost=8593.83..8593.83 rows=266883 width=27) (actual time=194.280..194.280 rows=266883 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 17126kB
37. 91.913 91.913 ↑ 1.0 266883 1
→

Seq Scan on dicom_study ds (cost=0.00..8593.83 rows=266883 width=27) (actual time=0.006..91.913 rows=266883 loops=1)

38. 0.867 1.938 ↑ 1.0 4765 1
→

Hash (cost=110.65..110.65 rows=4765 width=9) (actual time=1.938..1.938 rows=4765 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 191kB
39. 1.071 1.071 ↑ 1.0 4765 1
→

Seq Scan on orgunit ou (cost=0.00..110.65 rows=4765 width=9) (actual time=0.003..1.071 rows=4765 loops=1)

40. 0.000 1114.106 ↓ 0.0 0 159158
→

Bitmap Heap Scan on session_query_helper sqh (cost=182.72..193.48 rows=8 width=42) (actual time=0.007..0.007 rows=0 loops=159158)

  • Recheck Cond: (((num_value1 = d.producer) OR (num_value1 = d.orderer) OR ((num_value1 = d.producer) OR (num_value1 = d.orderer))) OR (num_value1 = ds.orgunit_fk) OR ((d.index05)::text = (str_value1)::text))
  • Filter: ((pacs_session_fk = 1925347919::bigint) AND (((key)::text = 'ProducerWithoutMark'::text) OR ((key)::text = 'OrdererWithoutMark'::text) OR ((key)::text = 'ProducerWithMark'::text) OR ((key)::text = 'OrdererWithMark'::text) OR ((key)::text = 'ShareOrgunit'::text) OR ((key)::text = 'ViewByPID'::text)))
41. 159.158 1114.106 ↓ 0.0 0 159158
→

BitmapOr (cost=182.72..182.72 rows=8 width=0) (actual time=0.007..0.007 rows=0 loops=159158)

42. 159.158 636.632 ↓ 0.0 0 159158
→

BitmapOr (cost=121.90..121.90 rows=6 width=0) (actual time=0.004..0.004 rows=0 loops=159158)

43. 159.158 159.158 ↓ 0.0 0 159158
→

Bitmap Index Scan on sqh_num1_ix (cost=0.00..30.41 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=159158)

  • Index Cond: (num_value1 = d.producer)
44. 0.000 0.000 ↓ 0.0 0 159158
→

Bitmap Index Scan on sqh_num1_ix (cost=0.00..30.41 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=159158)

  • Index Cond: (num_value1 = d.orderer)
45. 159.158 318.316 ↓ 0.0 0 159158
→

BitmapOr (cost=60.82..60.82 rows=3 width=0) (actual time=0.002..0.002 rows=0 loops=159158)

46. 159.158 159.158 ↓ 0.0 0 159158
→

Bitmap Index Scan on sqh_num1_ix (cost=0.00..30.41 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=159158)

  • Index Cond: (num_value1 = d.producer)
47. 0.000 0.000 ↓ 0.0 0 159158
→

Bitmap Index Scan on sqh_num1_ix (cost=0.00..30.41 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=159158)

  • Index Cond: (num_value1 = d.orderer)
48. 0.000 0.000 ↓ 0.0 0 159158
→

Bitmap Index Scan on sqh_num1_ix (cost=0.00..30.41 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=159158)

  • Index Cond: (num_value1 = ds.orgunit_fk)
49. 318.316 318.316 ↓ 0.0 0 159158
→

Bitmap Index Scan on sqh_str1_ix (cost=0.00..30.42 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=159158)

  • Index Cond: ((d.index05)::text = (str_value1)::text)
50.          

SubPlan (forNested Loop)
51. 0.000 0.000 ↓ 0.0 0
→

Aggregate (cost=8.73..8.74 rows=1 width=0) (actual time=.. rows= loops=0)

52. 0.000 0.000 ↓ 0.0 0
→

Index Scan using link_document_keyword_pkey on link_document_keyword ldk (cost=0.00..8.73 rows=1 width=0) (actual time=.. rows= loops=0)

  • Index Cond: ((document_fk = d.id) AND (keyword_fk = sqh.num_value2))
53. 27.475 27.475 ↑ 2.4 7 3925
→

Seq Scan on session_query_helper inner_sqh (cost=0.00..10.64 rows=17 width=3) (actual time=0.006..0.007 rows=7 loops=3925)

  • Filter: ((pacs_session_fk = sqh.pacs_session_fk) AND ((key)::text = 'VIPStatus'::text))
54. 1.058 1.924 ↑ 1.0 4765 1
→

Hash (cost=110.65..110.65 rows=4765 width=37) (actual time=1.924..1.924 rows=4765 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 338kB
55. 0.866 0.866 ↑ 1.0 4765 1
→

Seq Scan on orgunit o (cost=0.00..110.65 rows=4765 width=37) (actual time=0.006..0.866 rows=4765 loops=1)