explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ViCa

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.136 62.397 ↓ 0.0 0 1

Nested Loop (cost=9,251,399.85..14,338,202.43 rows=226,032,768 width=32) (actual time=62.397..62.397 rows=0 loops=1)

2.          

CTE disposition_src

3. 3.444 22.383 ↑ 2.3 3 1

GroupAggregate (cost=80.12..83.77 rows=7 width=532) (actual time=15.645..22.383 rows=3 loops=1)

  • Group Key: ix.disposition_status_id, ds.name
  • Group Key: ()
4. 7.207 18.939 ↑ 1.0 358 1

Sort (cost=80.12..81.02 rows=358 width=532) (actual time=15.270..18.939 rows=358 loops=1)

  • Sort Key: ix.disposition_status_id, ds.name
  • Sort Method: quicksort Memory: 52kB
5. 7.444 11.732 ↑ 1.0 358 1

Hash Join (cost=1.47..64.93 rows=358 width=532) (actual time=0.281..11.732 rows=358 loops=1)

  • Hash Cond: (ix.disposition_status_id = ds.id)
6. 4.077 4.077 ↑ 1.0 358 1

Index Only Scan using idx__event_index__compound on event_index ix (cost=0.41..58.94 rows=358 width=16) (actual time=0.031..4.077 rows=358 loops=1)

  • Index Cond: (screening_id = 22)
  • Heap Fetches: 358
7. 0.121 0.211 ↓ 1.3 4 1

Hash (cost=1.03..1.03 rows=3 width=520) (actual time=0.211..0.211 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.090 0.090 ↓ 1.3 4 1

Seq Scan on data_status ds (cost=0.00..1.03 rows=3 width=520) (actual time=0.020..0.090 rows=4 loops=1)

9.          

CTE disposition_facets

10. 0.062 22.656 ↑ 1.0 1 1

Nested Loop (cost=0.23..0.40 rows=1 width=32) (actual time=22.615..22.656 rows=1 loops=1)

11. 0.123 22.575 ↑ 1.0 1 1

Aggregate (cost=0.23..0.24 rows=1 width=32) (actual time=22.567..22.575 rows=1 loops=1)

12. 22.452 22.452 ↑ 3.5 2 1

CTE Scan on disposition_src disposition_src_1 (cost=0.00..0.14 rows=7 width=532) (actual time=15.668..22.452 rows=2 loops=1)

  • Filter: (id IS NOT NULL)
  • Rows Removed by Filter: 1
13. 0.019 0.019 ↑ 1.0 1 1

CTE Scan on disposition_src (cost=0.00..0.14 rows=1 width=8) (actual time=0.010..0.019 rows=1 loops=1)

  • Filter: (id IS NULL)
  • Rows Removed by Filter: 2
14.          

CTE language_src

15. 0.270 0.792 ↑ 1.0 2 1

GroupAggregate (cost=52.82..53.62 rows=2 width=14) (actual time=0.771..0.792 rows=2 loops=1)

  • Group Key: ix_1.language_code
  • Group Key: ()
16. 0.318 0.522 ↑ 6.5 16 1

Sort (cost=52.82..53.08 rows=104 width=11) (actual time=0.364..0.522 rows=16 loops=1)

  • Sort Key: ix_1.language_code
  • Sort Method: quicksort Memory: 25kB
17. 0.204 0.204 ↑ 6.5 16 1

Index Only Scan using idx__event_index__compound on event_index ix_1 (cost=0.41..49.34 rows=104 width=11) (actual time=0.027..0.204 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])))
  • Heap Fetches: 16
18.          

CTE language_facets

19. 0.066 1.013 ↑ 1.0 1 1

Nested Loop (cost=0.06..0.13 rows=1 width=32) (actual time=0.972..1.013 rows=1 loops=1)

20. 0.080 0.927 ↑ 1.0 1 1

Aggregate (cost=0.06..0.07 rows=1 width=32) (actual time=0.919..0.927 rows=1 loops=1)

21. 0.847 0.847 ↑ 2.0 1 1

CTE Scan on language_src language_src_1 (cost=0.00..0.04 rows=2 width=72) (actual time=0.793..0.847 rows=1 loops=1)

  • Filter: (id IS NOT NULL)
  • Rows Removed by Filter: 1
22. 0.020 0.020 ↑ 1.0 1 1

CTE Scan on language_src (cost=0.00..0.04 rows=1 width=8) (actual time=0.012..0.020 rows=1 loops=1)

  • Filter: (id IS NULL)
  • Rows Removed by Filter: 1
23.          

CTE name_counts

24. 4.564 9.819 ↑ 900.0 16 1

HashAggregate (cost=61.63..134.71 rows=14,400 width=16) (actual time=9.682..9.819 rows=16 loops=1)

  • Group Key: unnest(ix_2.names)
25. 5.255 5.255 ↓ 1.2 434 1

Index Only Scan using idx__event_index__compound on event_index ix_2 (cost=0.41..59.84 rows=358 width=8) (actual time=0.044..5.255 rows=434 loops=1)

  • Index Cond: (screening_id = 22)
  • Heap Fetches: 358
26.          

CTE name_src

27. 0.133 3.335 ↑ 93,600.0 6 1

Sort (cost=73,546.59..74,950.59 rows=561,600 width=42) (actual time=3.276..3.335 rows=6 loops=1)

  • Sort Key: cnt.ord DESC
  • Sort Method: quicksort Memory: 25kB
28. 0.273 3.202 ↑ 93,600.0 6 1

Merge Join (cost=1,389.72..10,044.09 rows=561,600 width=42) (actual time=1.829..3.202 rows=6 loops=1)

  • Merge Cond: ((unnest(ix2.names)) = cnt.name_id)
29. 0.378 2.505 ↑ 1,114.3 7 1

Merge Left Join (cost=107.13..317.99 rows=7,800 width=42) (actual time=1.459..2.505 rows=7 loops=1)

  • Merge Cond: ((unnest(ix2.names)) = n.id)
30. 0.308 1.651 ↑ 1,114.3 7 1

GroupAggregate (cost=106.99..147.35 rows=7,800 width=16) (actual time=1.194..1.651 rows=7 loops=1)

  • Group Key: (unnest(ix2.names))
  • Group Key: ()
31. 0.405 1.343 ↑ 4.5 23 1

Sort (cost=106.99..107.25 rows=104 width=16) (actual time=1.155..1.343 rows=23 loops=1)

  • Sort Key: (unnest(ix2.names))
  • Sort Method: quicksort Memory: 26kB
32. 0.391 0.938 ↑ 4.5 23 1

Hash Right Join (cost=51.94..103.50 rows=104 width=16) (actual time=0.402..0.938 rows=23 loops=1)

  • Hash Cond: (ix_3.id = ix2.id)
33. 0.205 0.205 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix_3 (cost=0.41..51.28 rows=104 width=16) (actual time=0.024..0.205 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
  • Filter: (((NOT (locations <@ '{}'::bigint[])) OR (locations IS NULL)) AND ((NOT (occupations <@ '{}'::bigint[])) OR (occupations IS NULL)) AND ((NOT (dates_of_birth <@ '{}'::bigint[])) OR (dates_of_birth IS NULL)) AND (NOT (risk_ids <@ '{}'::bigint[])))
34. 0.154 0.342 ↑ 6.5 16 1

Hash (cost=50.24..50.24 rows=104 width=38) (actual time=0.342..0.342 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
35. 0.188 0.188 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix2 (cost=0.41..50.24 rows=104 width=38) (actual time=0.020..0.188 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
36. 0.305 0.476 ↑ 6.1 19 1

Materialize (cost=0.14..6.16 rows=115 width=26) (actual time=0.031..0.476 rows=19 loops=1)

37. 0.171 0.171 ↑ 6.1 19 1

Index Scan using name_pkey on name n (cost=0.14..5.87 rows=115 width=26) (actual time=0.014..0.171 rows=19 loops=1)

38. 0.282 0.424 ↑ 900.0 16 1

Sort (cost=1,282.59..1,318.59 rows=14,400 width=16) (actual time=0.296..0.424 rows=16 loops=1)

  • Sort Key: cnt.name_id
  • Sort Method: quicksort Memory: 25kB
39. 0.142 0.142 ↑ 900.0 16 1

CTE Scan on name_counts cnt (cost=0.00..288.00 rows=14,400 width=16) (actual time=0.009..0.142 rows=16 loops=1)

40.          

CTE name_facets

41. 0.035 15.156 ↓ 0.0 0 1

Nested Loop (cost=9,163,038.63..9,174,305.75 rows=2,808 width=32) (actual time=15.156..15.156 rows=0 loops=1)

42. 0.126 15.112 ↑ 1.0 1 1

Aggregate (cost=9,163,038.63..9,163,038.64 rows=1 width=32) (actual time=15.104..15.112 rows=1 loops=1)

43. 0.142 14.986 ↑ 6,666.7 6 1

Hash Join (cost=8,356,728.14..9,162,838.62 rows=40,000 width=64) (actual time=14.691..14.986 rows=6 loops=1)

  • Hash Cond: (src.subcategory_id = nmk.id)
44. 0.203 14.726 ↑ 6,666.7 6 1

GroupAggregate (cost=8,356,689.57..9,161,850.05 rows=40,000 width=44) (actual time=14.533..14.726 rows=6 loops=1)

  • Group Key: cnt_1.ord, src.subcategory_id
45. 0.131 14.523 ↑ 6,705,504.0 6 1

Sort (cost=8,356,689.57..8,457,272.13 rows=40,233,024 width=60) (actual time=14.473..14.523 rows=6 loops=1)

  • Sort Key: cnt_1.ord DESC, src.subcategory_id
  • Sort Method: quicksort Memory: 25kB
46. 0.271 14.392 ↑ 6,705,504.0 6 1

Merge Join (cost=76,770.74..681,735.08 rows=40,233,024 width=60) (actual time=13.922..14.392 rows=6 loops=1)

  • Merge Cond: (cnt_1.name_id = src.id)
47. 0.293 10.398 ↑ 900.0 16 1

Sort (cost=1,282.59..1,318.59 rows=14,400 width=16) (actual time=10.263..10.398 rows=16 loops=1)

  • Sort Key: cnt_1.name_id
  • Sort Method: quicksort Memory: 25kB
48. 10.105 10.105 ↑ 900.0 16 1

CTE Scan on name_counts cnt_1 (cost=0.00..288.00 rows=14,400 width=16) (actual time=9.702..10.105 rows=16 loops=1)

49. 0.118 3.723 ↑ 93,132.0 6 1

Materialize (cost=75,488.15..78,282.11 rows=558,792 width=52) (actual time=3.572..3.723 rows=6 loops=1)

50. 0.135 3.605 ↑ 93,132.0 6 1

Sort (cost=75,488.15..76,885.13 rows=558,792 width=52) (actual time=3.553..3.605 rows=6 loops=1)

  • Sort Key: src.id
  • Sort Method: quicksort Memory: 25kB
51. 3.470 3.470 ↑ 93,132.0 6 1

CTE Scan on name_src src (cost=0.00..11,232.00 rows=558,792 width=52) (actual time=3.295..3.470 rows=6 loops=1)

  • Filter: (id IS NOT NULL)
52. 0.060 0.118 ↑ 317.5 4 1

Hash (cost=22.70..22.70 rows=1,270 width=36) (actual time=0.118..0.118 rows=4 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
53. 0.058 0.058 ↑ 317.5 4 1

Seq Scan on name_match_kind nmk (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.019..0.058 rows=4 loops=1)

54. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on name_src (cost=0.00..11,232.00 rows=2,808 width=8) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: (id IS NULL)
  • Rows Removed by Filter: 6
55.          

CTE location_src

56. 0.140 2.832 ↑ 400.0 3 1

Merge Left Join (cost=157.54..179.50 rows=1,200 width=48) (actual time=2.478..2.832 rows=3 loops=1)

  • Merge Cond: ((unnest(ix2_1.locations)) = l.id)
57. 0.237 2.267 ↑ 400.0 3 1

GroupAggregate (cost=156.52..163.39 rows=1,200 width=16) (actual time=2.162..2.267 rows=3 loops=1)

  • Group Key: (unnest(ix2_1.locations))
  • Group Key: ()
58. 0.350 2.030 ↑ 6.5 16 1

Sort (cost=156.52..156.78 rows=104 width=16) (actual time=1.873..2.030 rows=16 loops=1)

  • Sort Key: (unnest(ix2_1.locations))
  • Sort Method: quicksort Memory: 25kB
59. 0.784 1.680 ↑ 6.5 16 1

Nested Loop Left Join (cost=0.69..153.04 rows=104 width=16) (actual time=0.121..1.680 rows=16 loops=1)

60. 0.384 0.384 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix2_1 (cost=0.41..50.24 rows=104 width=37) (actual time=0.033..0.384 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
61. 0.512 0.512 ↑ 1.0 1 16

Index Scan using event_index_pkey on event_index ix_4 (cost=0.28..0.98 rows=1 width=16) (actual time=0.021..0.032 rows=1 loops=16)

  • Index Cond: (id = ix2_1.id)
  • Filter: (((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])) AND (NOT (names <@ '{}'::bigint[])) AND ((NOT (occupations <@ '{}'::bigint[])) OR (occupations IS NULL)) AND ((NOT (dates_of_birth <@ '{}'::bigint[])) OR (dates_of_birth IS NULL)) AND (NOT (risk_ids <@ '{}'::bigint[]))) OR (risk_ids IS NULL))
62. 0.115 0.425 ↓ 6.0 6 1

Materialize (cost=1.02..1.03 rows=1 width=40) (actual time=0.282..0.425 rows=6 loops=1)

63. 0.178 0.310 ↓ 6.0 6 1

Sort (cost=1.02..1.02 rows=1 width=40) (actual time=0.262..0.310 rows=6 loops=1)

  • Sort Key: l.id
  • Sort Method: quicksort Memory: 25kB
64. 0.132 0.132 ↓ 12.0 12 1

Seq Scan on location l (cost=0.00..1.01 rows=1 width=40) (actual time=0.018..0.132 rows=12 loops=1)

65.          

CTE location_facets

66. 0.090 3.114 ↑ 6.0 1 1

Nested Loop (cost=38.93..63.04 rows=6 width=32) (actual time=3.073..3.114 rows=1 loops=1)

67. 0.101 3.005 ↑ 1.0 1 1

Aggregate (cost=38.93..38.94 rows=1 width=32) (actual time=2.997..3.005 rows=1 loops=1)

68. 2.904 2.904 ↑ 597.0 2 1

CTE Scan on location_src location_src_1 (cost=0.00..24.00 rows=1,194 width=48) (actual time=2.499..2.904 rows=2 loops=1)

  • Filter: (id IS NOT NULL)
  • Rows Removed by Filter: 1
69. 0.019 0.019 ↑ 6.0 1 1

CTE Scan on location_src (cost=0.00..24.00 rows=6 width=8) (actual time=0.011..0.019 rows=1 loops=1)

  • Filter: (id IS NULL)
  • Rows Removed by Filter: 2
70.          

CTE occupation_src

71. 0.644 5.378 ↑ 866.7 6 1

Merge Left Join (cost=107.13..220.52 rows=5,200 width=26) (actual time=2.729..5.378 rows=6 loops=1)

  • Merge Cond: ((unnest(ix2_2.occupations)) = o.id)
72. 0.654 3.162 ↑ 866.7 6 1

GroupAggregate (cost=106.99..134.16 rows=5,200 width=16) (actual time=2.585..3.162 rows=6 loops=1)

  • Group Key: (unnest(ix2_2.occupations))
  • Group Key: ()
73. 0.620 2.508 ↑ 5.5 19 1

Sort (cost=106.99..107.25 rows=104 width=16) (actual time=2.309..2.508 rows=19 loops=1)

  • Sort Key: (unnest(ix2_2.occupations))
  • Sort Method: quicksort Memory: 25kB
74. 0.792 1.888 ↑ 5.5 19 1

Hash Right Join (cost=51.94..103.50 rows=104 width=16) (actual time=0.792..1.888 rows=19 loops=1)

  • Hash Cond: (ix_5.id = ix2_2.id)
75. 0.408 0.408 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix_5 (cost=0.41..51.28 rows=104 width=16) (actual time=0.060..0.408 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
  • Filter: ((NOT (names <@ '{}'::bigint[])) AND ((NOT (locations <@ '{}'::bigint[])) OR (locations IS NULL)) AND ((NOT (dates_of_birth <@ '{}'::bigint[])) OR (dates_of_birth IS NULL)) AND (NOT (risk_ids <@ '{}'::bigint[])))
76. 0.267 0.688 ↑ 6.5 16 1

Hash (cost=50.24..50.24 rows=104 width=37) (actual time=0.688..0.688 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
77. 0.421 0.421 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix2_2 (cost=0.41..50.24 rows=104 width=37) (actual time=0.052..0.421 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
78. 1.086 1.572 ↑ 2.2 20 1

Materialize (cost=0.14..3.93 rows=45 width=18) (actual time=0.108..1.572 rows=20 loops=1)

79. 0.486 0.486 ↑ 2.2 20 1

Index Scan using occupation_pkey on occupation o (cost=0.14..3.82 rows=45 width=18) (actual time=0.088..0.486 rows=20 loops=1)

80.          

CTE occupation_facets

81. 0.079 6.727 ↑ 26.0 1 1

Nested Loop (cost=168.68..273.09 rows=26 width=32) (actual time=6.686..6.727 rows=1 loops=1)

82. 0.689 6.352 ↑ 1.0 1 1

Aggregate (cost=168.68..168.69 rows=1 width=32) (actual time=6.344..6.352 rows=1 loops=1)

83. 5.663 5.663 ↑ 1,034.8 5 1

CTE Scan on occupation_src occupation_src_1 (cost=0.00..104.00 rows=5,174 width=48) (actual time=2.750..5.663 rows=5 loops=1)

  • Filter: (id IS NOT NULL)
  • Rows Removed by Filter: 1
84. 0.296 0.296 ↑ 26.0 1 1

CTE Scan on occupation_src (cost=0.00..104.00 rows=26 width=8) (actual time=0.287..0.296 rows=1 loops=1)

  • Filter: (id IS NULL)
  • Rows Removed by Filter: 5
85.          

CTE dob_src

86. 0.233 5.403 ↑ 833.3 3 1

Merge Left Join (cost=108.01..152.91 rows=2,500 width=28) (actual time=4.862..5.403 rows=3 loops=1)

  • Merge Cond: ((unnest(ix2_3.dates_of_birth)) = dob.id)
87. 0.525 3.761 ↑ 833.3 3 1

GroupAggregate (cost=106.99..120.45 rows=2,500 width=16) (actual time=3.684..3.761 rows=3 loops=1)

  • Group Key: (unnest(ix2_3.dates_of_birth))
  • Group Key: ()
88. 1.003 3.236 ↑ 6.5 16 1

Sort (cost=106.99..107.25 rows=104 width=16) (actual time=2.635..3.236 rows=16 loops=1)

  • Sort Key: (unnest(ix2_3.dates_of_birth))
  • Sort Method: quicksort Memory: 25kB
89. 1.005 2.233 ↑ 6.5 16 1

Hash Right Join (cost=51.94..103.50 rows=104 width=16) (actual time=0.938..2.233 rows=16 loops=1)

  • Hash Cond: (ix_6.id = ix2_3.id)
90. 0.368 0.368 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix_6 (cost=0.41..51.28 rows=104 width=16) (actual time=0.033..0.368 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
  • Filter: ((NOT (names <@ '{}'::bigint[])) AND ((NOT (locations <@ '{}'::bigint[])) OR (locations IS NULL)) AND ((NOT (occupations <@ '{}'::bigint[])) OR (occupations IS NULL)) AND (NOT (risk_ids <@ '{}'::bigint[])))
91. 0.259 0.860 ↑ 6.5 16 1

Hash (cost=50.24..50.24 rows=104 width=37) (actual time=0.860..0.860 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
92. 0.601 0.601 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix2_3 (cost=0.41..50.24 rows=104 width=37) (actual time=0.034..0.601 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
93. 0.203 1.409 ↓ 8.0 8 1

Materialize (cost=1.02..1.03 rows=1 width=20) (actual time=1.145..1.409 rows=8 loops=1)

94. 0.697 1.206 ↓ 8.0 8 1

Sort (cost=1.02..1.02 rows=1 width=20) (actual time=1.124..1.206 rows=8 loops=1)

  • Sort Key: dob.id
  • Sort Method: quicksort Memory: 26kB
95. 0.509 0.509 ↓ 26.0 26 1

Seq Scan on date_of_birth dob (cost=0.00..1.01 rows=1 width=20) (actual time=0.024..0.509 rows=26 loops=1)

96.          

CTE dob_facets

97. 0.096 5.797 ↑ 12.0 1 1

Nested Loop (cost=87.32..137.52 rows=12 width=32) (actual time=5.755..5.797 rows=1 loops=1)

98. 0.160 5.641 ↑ 1.0 1 1

Aggregate (cost=87.32..87.33 rows=1 width=32) (actual time=5.632..5.641 rows=1 loops=1)

99. 5.481 5.481 ↑ 1,244.0 2 1

CTE Scan on dob_src dob_src_1 (cost=0.00..50.00 rows=2,488 width=28) (actual time=4.881..5.481 rows=2 loops=1)

  • Filter: (id IS NOT NULL)
  • Rows Removed by Filter: 1
100. 0.060 0.060 ↑ 12.0 1 1

CTE Scan on dob_src (cost=0.00..50.00 rows=12 width=8) (actual time=0.051..0.060 rows=1 loops=1)

  • Filter: (id IS NULL)
  • Rows Removed by Filter: 2
101.          

CTE risk_concept_src

102. 0.447 5.718 ↑ 661.5 13 1

Hash Left Join (cost=143.99..392.66 rows=8,600 width=48) (actual time=3.912..5.718 rows=13 loops=1)

  • Hash Cond: ((unnest(ix2_4.risk_ids)) = rc.id)
103. 1.027 4.252 ↑ 661.5 13 1

GroupAggregate (cost=106.99..151.41 rows=8,600 width=16) (actual time=2.852..4.252 rows=13 loops=1)

  • Group Key: (unnest(ix2_4.risk_ids))
  • Group Key: ()
104. 1.261 3.225 ↑ 3.7 28 1

Sort (cost=106.99..107.25 rows=104 width=16) (actual time=2.614..3.225 rows=28 loops=1)

  • Sort Key: (unnest(ix2_4.risk_ids))
  • Sort Method: quicksort Memory: 26kB
105. 1.073 1.964 ↑ 3.7 28 1

Hash Right Join (cost=51.94..103.50 rows=104 width=16) (actual time=0.572..1.964 rows=28 loops=1)

  • Hash Cond: (ix_7.id = ix2_4.id)
106. 0.391 0.391 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix_7 (cost=0.41..51.28 rows=104 width=16) (actual time=0.031..0.391 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
  • Filter: ((NOT (names <@ '{}'::bigint[])) AND ((NOT (locations <@ '{}'::bigint[])) OR (locations IS NULL)) AND ((NOT (occupations <@ '{}'::bigint[])) OR (occupations IS NULL)) AND ((NOT (dates_of_birth <@ '{}'::bigint[])) OR (dates_of_birth IS NULL)))
107. 0.229 0.500 ↑ 6.5 16 1

Hash (cost=50.24..50.24 rows=104 width=40) (actual time=0.500..0.500 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
108. 0.271 0.271 ↑ 6.5 16 1

Index Scan using idx__event_index__compound on event_index ix2_4 (cost=0.41..50.24 rows=104 width=40) (actual time=0.037..0.271 rows=16 loops=1)

  • Index Cond: ((screening_id = 22) AND (disposition_status_id = ANY ('{1}'::integer[])) AND (language_code = ANY ('{en}'::text[])))
109. 0.614 1.019 ↑ 36.4 33 1

Hash (cost=22.00..22.00 rows=1,200 width=40) (actual time=1.019..1.019 rows=33 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 18kB
110. 0.405 0.405 ↑ 36.4 33 1

Seq Scan on risk_concept rc (cost=0.00..22.00 rows=1,200 width=40) (actual time=0.018..0.405 rows=33 loops=1)

111.          

CTE risk_concept_facets

112. 0.134 7.018 ↑ 43.0 1 1

Nested Loop (cost=278.96..451.63 rows=43 width=32) (actual time=6.940..7.018 rows=1 loops=1)

113. 0.763 6.855 ↑ 1.0 1 1

Aggregate (cost=278.96..278.97 rows=1 width=32) (actual time=6.833..6.855 rows=1 loops=1)

114. 6.092 6.092 ↑ 713.1 12 1

CTE Scan on risk_concept_src risk_concept_src_1 (cost=0.00..172.00 rows=8,557 width=48) (actual time=3.935..6.092 rows=12 loops=1)

  • Filter: (id IS NOT NULL)
  • Rows Removed by Filter: 1
115. 0.029 0.029 ↑ 43.0 1 1

CTE Scan on risk_concept_src (cost=0.00..172.00 rows=43 width=8) (actual time=0.019..0.029 rows=1 loops=1)

  • Filter: (id IS NULL)
  • Rows Removed by Filter: 12
116. 0.055 47.087 ↑ 80,496.0 1 1

Nested Loop (cost=0.00..1,037.22 rows=80,496 width=192) (actual time=46.495..47.087 rows=1 loops=1)

117. 7.094 7.094 ↑ 43.0 1 1

CTE Scan on risk_concept_facets (cost=0.00..0.86 rows=43 width=32) (actual time=6.963..7.094 rows=1 loops=1)

118. 0.067 39.938 ↑ 1,872.0 1 1

Materialize (cost=0.00..34.84 rows=1,872 width=160) (actual time=39.503..39.938 rows=1 loops=1)

119. 0.062 39.871 ↑ 1,872.0 1 1

Nested Loop (cost=0.00..25.48 rows=1,872 width=160) (actual time=39.452..39.871 rows=1 loops=1)

120. 6.774 6.774 ↑ 26.0 1 1

CTE Scan on occupation_facets (cost=0.00..0.52 rows=26 width=32) (actual time=6.716..6.774 rows=1 loops=1)

121. 0.039 33.035 ↑ 72.0 1 1

Materialize (cost=0.00..1.75 rows=72 width=128) (actual time=32.698..33.035 rows=1 loops=1)

122. 0.086 32.996 ↑ 72.0 1 1

Nested Loop (cost=0.00..1.39 rows=72 width=128) (actual time=32.676..32.996 rows=1 loops=1)

123. 5.837 5.837 ↑ 12.0 1 1

CTE Scan on dob_facets (cost=0.00..0.24 rows=12 width=32) (actual time=5.778..5.837 rows=1 loops=1)

124. 0.048 27.073 ↑ 6.0 1 1

Materialize (cost=0.00..0.26 rows=6 width=96) (actual time=26.836..27.073 rows=1 loops=1)

125. 0.058 27.025 ↑ 6.0 1 1

Nested Loop (cost=0.00..0.23 rows=6 width=96) (actual time=26.804..27.025 rows=1 loops=1)

126. 0.056 23.815 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.05 rows=1 width=64) (actual time=23.676..23.815 rows=1 loops=1)

127. 22.709 22.709 ↑ 1.0 1 1

CTE Scan on disposition_facets (cost=0.00..0.02 rows=1 width=32) (actual time=22.651..22.709 rows=1 loops=1)

128. 1.050 1.050 ↑ 1.0 1 1

CTE Scan on language_facets (cost=0.00..0.02 rows=1 width=32) (actual time=0.993..1.050 rows=1 loops=1)

129. 3.152 3.152 ↑ 6.0 1 1

CTE Scan on location_facets (cost=0.00..0.12 rows=6 width=32) (actual time=3.094..3.152 rows=1 loops=1)

130. 15.174 15.174 ↓ 0.0 0 1

CTE Scan on name_facets (cost=0.00..56.16 rows=2,808 width=32) (actual time=15.174..15.174 rows=0 loops=1)

Planning time : 9.248 ms
Execution time : 63.760 ms