Advertisement
Dyrcona

Pg 14 Explain

Dec 7th, 2022
226
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 35.73 KB | None | 0 0
  1. QUERY PLAN
  2. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3. Append (cost=2982542.69..2984835.21 rows=1001 width=144) (actual time=116380.822..116380.991 rows=120 loops=1)
  4. CTE w
  5. -> Subquery Scan on core_query (cost=2980792.69..2982542.69 rows=100000 width=112) (actual time=114439.638..114439.707 rows=119 loops=1)
  6. CTE x559f99d69060_keyword_xq
  7. -> Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)
  8. -> Limit (cost=2980792.68..2981042.68 rows=100000 width=180) (actual time=114439.629..114439.668 rows=119 loops=1)
  9. -> Sort (cost=2980792.68..2981232.42 rows=175895 width=180) (actual time=114439.627..114439.658 rows=119 loops=1)
  10. Sort Key: ((1.0 / ((avg(COALESCE(((ts_rank_cd('{0.1,0.2,0.4,1}'::real[], "*SELECT* 1_1".index_vector, "*SELECT* 1_1".tsq_rank, 14) * ("*SELECT* 1_1".weight)::double precision) * '1000'::double precision), '0'::double precision)) + ((1 * COALESCE(((NULLIF(first((mrv.vlist @> ARRAY[coded_value_map.id])), false))::integer * 5), 1)))::double precision))::numeric)), (first(pubdate_t.value)) DESC NULLS LAST, (((avg(COALESCE(((ts_rank_cd('{0.1,0.2,0.4,1}'::real[], "*SELECT* 1_1".index_vector, "*SELECT* 1_1".tsq_rank, 14) * ("*SELECT* 1_1".weight)::double precision) * '1000'::double precision), '0'::double precision)) + ((1 * COALESCE(((NULLIF(first((mrv.vlist @> ARRAY[coded_value_map.id])), false))::integer * 5), 1)))::double precision))::numeric) DESC
  11. Sort Method: quicksort Memory: 41kB
  12. -> GroupAggregate (cost=2695235.52..2965468.39 rows=175895 width=180) (actual time=114294.576..114439.438 rows=119 loops=1)
  13. Group Key: m.source
  14. -> Nested Loop (cost=2695235.52..2863009.56 rows=175895 width=406) (actual time=114294.203..114402.842 rows=504 loops=1)
  15. -> Nested Loop Left Join (cost=2695230.55..2860775.31 rows=175895 width=402) (actual time=114291.347..114399.465 rows=504 loops=1)
  16. -> Nested Loop (cost=2695230.12..2767443.99 rows=118411 width=383) (actual time=114291.331..114395.989 rows=504 loops=1)
  17. -> Merge Left Join (cost=2695229.69..2697830.38 rows=131421 width=277) (actual time=114291.307..114392.572 rows=504 loops=1)
  18. Merge Cond: (m.source = s.record)
  19. -> Sort (cost=2690957.87..2691286.43 rows=131421 width=213) (actual time=114249.689..114249.868 rows=504 loops=1)
  20. Sort Key: m.source
  21. Sort Method: quicksort Memory: 421kB
  22. -> Hash Join (cost=868185.46..2679784.57 rows=131421 width=213) (actual time=114073.480..114249.475 rows=504 loops=1)
  23. Hash Cond: ("*SELECT* 1_1".source = m.source)
  24. -> Append (cost=1938.44..1810972.76 rows=333487 width=197) (actual time=123.499..299.292 rows=750 loops=1)
  25. -> Subquery Scan on "*SELECT* 1_1" (cost=1938.44..603139.14 rows=207124 width=214) (actual time=123.497..124.216 rows=504 loops=1)
  26. -> Hash Join (cost=1938.44..601067.90 rows=207124 width=290) (actual time=123.496..124.168 rows=504 loops=1)
  27. Hash Cond: (fe.field = fe_weight.id)
  28. -> Nested Loop (cost=1933.21..600495.70 rows=207124 width=214) (actual time=123.414..123.974 rows=504 loops=1)
  29. -> CTE Scan on x559f99d69060_keyword_xq (cost=0.00..0.02 rows=1 width=64) (actual time=0.004..0.006 rows=1 loops=1)
  30. -> Bitmap Heap Scan on keyword_field_entry fe (cost=1933.21..598424.44 rows=207124 width=182) (actual time=123.403..123.873 rows=504 loops=1)
  31. Recheck Cond: (index_vector @@ x559f99d69060_keyword_xq.tsq)
  32. Filter: (id IS NOT NULL)
  33. Heap Blocks: exact=293
  34. -> Bitmap Index Scan on metabib_keyword_field_entry_index_vector_idx (cost=0.00..1881.43 rows=207124 width=0) (actual time=123.351..123.351 rows=504 loops=1)
  35. Index Cond: (index_vector @@ x559f99d69060_keyword_xq.tsq)
  36. -> Hash (cost=3.99..3.99 rows=99 width=8) (actual time=0.069..0.070 rows=99 loops=1)
  37. Buckets: 1024 Batches: 1 Memory Usage: 12kB
  38. -> Seq Scan on metabib_field fe_weight (cost=0.00..3.99 rows=99 width=8) (actual time=0.022..0.046 rows=99 loops=1)
  39. -> Subquery Scan on "*SELECT* 2_1" (cost=432.31..117098.72 rows=1038 width=179) (actual time=29.470..29.749 rows=118 loops=1)
  40. -> Hash Join (cost=432.31..117088.34 rows=1038 width=255) (actual time=29.468..29.736 rows=118 loops=1)
  41. Hash Cond: (fe_1.field = fe_weight_1."real")
  42. -> Nested Loop (cost=430.00..116908.22 rows=44645 width=179) (actual time=29.421..29.651 rows=239 loops=1)
  43. -> CTE Scan on x559f99d69060_keyword_xq x559f99d69060_keyword_xq_1 (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)
  44. -> Bitmap Heap Scan on title_field_entry fe_1 (cost=430.00..116461.75 rows=44645 width=147) (actual time=29.412..29.610 rows=239 loops=1)
  45. Recheck Cond: (index_vector @@ x559f99d69060_keyword_xq_1.tsq)
  46. Filter: (id IS NOT NULL)
  47. Heap Blocks: exact=141
  48. -> Bitmap Index Scan on metabib_title_field_entry_index_vector_idx (cost=0.00..418.84 rows=44645 width=0) (actual time=29.388..29.388 rows=239 loops=1)
  49. Index Cond: (index_vector @@ x559f99d69060_keyword_xq_1.tsq)
  50. -> Hash (cost=2.29..2.29 rows=2 width=8) (actual time=0.029..0.030 rows=2 loops=1)
  51. Buckets: 1024 Batches: 1 Memory Usage: 9kB
  52. -> Seq Scan on metabib_field_virtual_map fe_weight_1 (cost=0.00..2.29 rows=2 width=8) (actual time=0.026..0.027 rows=2 loops=1)
  53. Filter: (("real" = ANY ('{6,53}'::integer[])) AND (virtual = 45))
  54. Rows Removed by Filter: 84
  55. -> Subquery Scan on "*SELECT* 3" (cost=1936.13..601612.51 rows=16859 width=214) (actual time=122.653..123.158 rows=116 loops=1)
  56. -> Hash Join (cost=1936.13..601443.92 rows=16859 width=290) (actual time=122.652..123.145 rows=116 loops=1)
  57. Hash Cond: (fe_2.field = fe_weight_2."real")
  58. -> Nested Loop (cost=1933.21..600495.70 rows=207124 width=214) (actual time=122.596..123.052 rows=504 loops=1)
  59. -> CTE Scan on x559f99d69060_keyword_xq x559f99d69060_keyword_xq_2 (cost=0.00..0.02 rows=1 width=64) (actual time=0.000..0.002 rows=1 loops=1)
  60. -> Bitmap Heap Scan on keyword_field_entry fe_2 (cost=1933.21..598424.44 rows=207124 width=182) (actual time=122.589..122.963 rows=504 loops=1)
  61. Recheck Cond: (index_vector @@ x559f99d69060_keyword_xq_2.tsq)
  62. Filter: (id IS NOT NULL)
  63. Heap Blocks: exact=293
  64. -> Bitmap Index Scan on metabib_keyword_field_entry_index_vector_idx (cost=0.00..1881.43 rows=207124 width=0) (actual time=122.541..122.541 rows=504 loops=1)
  65. Index Cond: (index_vector @@ x559f99d69060_keyword_xq_2.tsq)
  66. -> Hash (cost=2.83..2.83 rows=7 width=8) (actual time=0.023..0.024 rows=7 loops=1)
  67. Buckets: 1024 Batches: 1 Memory Usage: 9kB
  68. -> Seq Scan on metabib_field_virtual_map fe_weight_2 (cost=0.00..2.83 rows=7 width=8) (actual time=0.018..0.020 rows=7 loops=1)
  69. Filter: ((virtual = 45) AND ("real" = ANY ('{39,41,42,46,47,48,50}'::integer[])))
  70. Rows Removed by Filter: 79
  71. -> Subquery Scan on "*SELECT* 4" (cost=739.69..189382.85 rows=27220 width=132) (actual time=6.068..6.070 rows=0 loops=1)
  72. -> Nested Loop (cost=739.69..189110.65 rows=27220 width=208) (actual time=6.068..6.070 rows=0 loops=1)
  73. -> Seq Scan on metabib_field_virtual_map fe_weight_3 (cost=0.00..2.29 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=1)
  74. Filter: ((virtual = 45) AND ("real" = 8))
  75. Rows Removed by Filter: 85
  76. -> Nested Loop (cost=739.69..188836.16 rows=27220 width=132) (actual time=6.047..6.048 rows=0 loops=1)
  77. -> CTE Scan on x559f99d69060_keyword_xq x559f99d69060_keyword_xq_3 (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=1)
  78. -> Bitmap Heap Scan on author_field_entry fe_3 (cost=739.69..188563.94 rows=27220 width=100) (actual time=6.042..6.043 rows=0 loops=1)
  79. Recheck Cond: (index_vector @@ x559f99d69060_keyword_xq_3.tsq)
  80. Filter: ((id IS NOT NULL) AND (field = 8))
  81. Rows Removed by Filter: 2
  82. Heap Blocks: exact=2
  83. -> Bitmap Index Scan on metabib_author_field_entry_index_vector_idx (cost=0.00..732.88 rows=79584 width=0) (actual time=6.032..6.032 rows=2 loops=1)
  84. Index Cond: (index_vector @@ x559f99d69060_keyword_xq_3.tsq)
  85. -> Subquery Scan on "*SELECT* 5" (cost=1052.46..298072.12 rows=81246 width=171) (actual time=16.008..16.027 rows=12 loops=1)
  86. -> Nested Loop (cost=1052.46..297259.66 rows=81246 width=247) (actual time=16.007..16.024 rows=12 loops=1)
  87. -> Seq Scan on metabib_field_virtual_map fe_weight_4 (cost=0.00..2.29 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
  88. Filter: ((virtual = 45) AND ("real" = 16))
  89. Rows Removed by Filter: 85
  90. -> Nested Loop (cost=1052.46..296444.91 rows=81246 width=171) (actual time=15.994..16.008 rows=12 loops=1)
  91. -> CTE Scan on x559f99d69060_keyword_xq x559f99d69060_keyword_xq_4 (cost=0.00..0.02 rows=1 width=64) (actual time=0.000..0.001 rows=1 loops=1)
  92. -> Bitmap Heap Scan on subject_field_entry fe_4 (cost=1052.46..295632.43 rows=81246 width=139) (actual time=15.989..16.000 rows=12 loops=1)
  93. Recheck Cond: (index_vector @@ x559f99d69060_keyword_xq_4.tsq)
  94. Filter: ((id IS NOT NULL) AND (field = 16))
  95. Heap Blocks: exact=8
  96. -> Bitmap Index Scan on metabib_subject_field_entry_index_vector_idx (cost=0.00..1032.15 rows=113087 width=0) (actual time=15.980..15.980 rows=12 loops=1)
  97. Index Cond: (index_vector @@ x559f99d69060_keyword_xq_4.tsq)
  98. -> Hash (cost=854017.95..854017.95 rows=978326 width=16) (actual time=113948.878..113948.881 rows=1857179 loops=1)
  99. Buckets: 2097152 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 103440kB
  100. -> Hash Join (cost=769985.74..854017.95 rows=978326 width=16) (actual time=2054.095..113050.231 rows=1857179 loops=1)
  101. Hash Cond: (m.source = bre.id)
  102. Join Filter: ((SubPlan 2) OR ((NOT (SubPlan 3)) AND ((bre.vis_attr_vector IS NULL) OR (NOT ('[0,268435456)'::int4range @> ANY (bre.vis_attr_vector))))) OR (bre.vis_attr_vector @@ '1'::query_int))
  103. Rows Removed by Join Filter: 379836
  104. -> Seq Scan on metarecord_source_map m (cost=0.00..40638.51 rows=2482551 width=8) (actual time=0.020..431.087 rows=2482551 loops=1)
  105. -> Hash (cost=724536.44..724536.44 rows=2237464 width=33) (actual time=2051.767..2051.768 rows=2237018 loops=1)
  106. Buckets: 2097152 Batches: 2 Memory Usage: 88398kB
  107. -> Seq Scan on record_entry bre (cost=0.00..724536.44 rows=2237464 width=33) (actual time=0.028..1495.507 rows=2237018 loops=1)
  108. Filter: (NOT deleted)
  109. Rows Removed by Filter: 1985205
  110. SubPlan 2
  111. -> Index Scan using copy_vis_attr_cache_record_idx on copy_vis_attr_cache (cost=0.43..132.86 rows=21 width=0) (actual time=0.048..0.048 rows=1 loops=2237015)
  112. Index Cond: (record = m.source)
  113. Filter: (vis_attr_vector @@ (array_to_string('{(536870913|536871164|536871133|536871277|536870999|536871233|536871075|536871052|536871064|536871288|536871043|536871018|536871014|536871240|536870958|536871031|536871265|536871057|536871123|536870977|536870933|536871068|536871138|536871115|536871027|536871213|536871094|536870914|536871145|536870980|536871172|536870997|536871008|536871274|536870992|536871143|536871258|536871187|536871109|536870970|536871204|536871096|536871034|536870918|536871256|536870943|536870967|536871128|536871089|536871223|536871036|536871179|536871125|536870945|536870937|536870951|536871121|536871321|536871171|536871146|536871170|536871038|536871029|536871015|536871272|536871103|536870994|536871054|536870915|536870929|536871320|536871158|536871106|536870989|536871160|536870991|536871159|536871268|536871019|536871175|536871323|536871011|536871022|536870927|536871191|536871317|536871078|536871152|536871024|536871200|536871155|536871156|536871154|536871163|536871151|536871153|536871315|536871205|536871257|536871209|536871210|536870998|536871264|536870948|536871284|536871212|536871087|536871139|536871166|536871162|536871243|536871030|536870949|536871174|536870940|536870986|536871267|536871322|536870993|536870996|536871307|536870935|536870923|536871192|536870941|536870974|536871113|536871218|536870971|536870975|536870972|536871047|536870936|536871206|536871105|536871000|536871215|536871006|536871316|536870961|536871023|536871035|536871314|536870938|536871033|536871032|536871049|536871050|536871013|536871067|536871044|536871111|536871056|536871147|536871104|536871286|536870985|536871009|536870921|536871193|536871065|536871002|536871167|536871140|536870917|536871168|536871165|536871196|536871007|536871247|536870969|536870916|536871254|536870964|536871248|536871157|536871060|536871266|536871114|536871319|536871003|536871004|536871005|536871016|536871219|536871220|536871046|536871086|536871021|536871079|536870957|536870930|536871141|536871098|536871255|536871278|536871088|536871059|536871173|536871245|536871028|536871069|536871148|536871122|536871061|536871001|536871120|536871099|536870960|536871073|536871048|536871077|536871118|536870939|536871084|536871070|536871119|536871116|536871124|536871211|536871100|536871221|536871051|536870953|536871092|536871225|536871137|536871020|536871197|536871235|536871234|536871261|536871081|536871095|536871083|536871117|536870968|536870963|536871226|536870990|536871263|536871063|536870955|536870965|536871283|536871229|536870983|536871112|536871194|536871281|536871231|536871142|536870979|536871108|536871025|536870978|536871062|536871198|536870962|536871269|536871135|536871072|536871082|536870984|536871203|536871199|536871262|536871270|536871232|536871202|536871169|536871242|536871010|536870973|536871107|536871217|536871230|536871279|536871053|536871149|536871066|536871216|536871241|536871085|536871012|536871190|536870987|536871313|536871188|536871080|536871026|536871110|536870966|536871178|536871177|536870982|536871207|536871208|536871134|536871102|536870995|536871252|536870934|536870981|536871176|536871058|536871201|536871097|536871292|536871055|536870922|536871045|536871294|536871249|536871161|536871290|536870932|536870920|536870959|536871136|536871291|536871297|536871150|536871293|536871289|536871295|536871296|536871144|536871298|536871285|536870976|536871071|536871310|536871311|536871017|536870919|536870925|536871131|536871132|536871101|536871222|536870942|536871236|536871259|536870988|536870952|536870956|536870950|536871303|536871305|536871308|536871306|536871299|536871091|536871090|536871093|536870931|536871309|536870926|536870924|536870928|536871129|536871130|536871037|536871039|536871040|536871041|536871042|536871181|536871184|536871182|536870944|536871183|536871185|536871180|536871273|536871127|536871126|536870946|536870947|536871074|536871301|536871300|536871302|536871304|536870954|536871228|536871227|536871224|536871238|536871287|536871076)}'::text[], '&'::text))::query_int)
  114. Rows Removed by Filter: 0
  115. SubPlan 3
  116. -> Index Only Scan using copy_vis_attr_cache_record_idx on copy_vis_attr_cache copy_vis_attr_cache_1 (cost=0.43..4.99 rows=32 width=0) (actual time=0.002..0.002 rows=0 loops=465391)
  117. Index Cond: (record = m.source)
  118. Heap Fetches: 0
  119. -> GroupAggregate (cost=4271.81..5759.36 rows=35001 width=72) (actual time=38.533..138.686 rows=50110 loops=1)
  120. Group Key: s.record
  121. -> Sort (cost=4271.81..4359.32 rows=35001 width=20) (actual time=38.499..45.047 rows=68346 loops=1)
  122. Sort Key: s.record
  123. Sort Method: quicksort Memory: 8541kB
  124. -> Nested Loop (cost=395.55..1630.10 rows=35001 width=20) (actual time=1.218..18.046 rows=70002 loops=1)
  125. -> Seq Scan on badge b (cost=0.00..1.02 rows=1 width=8) (actual time=0.009..0.011 rows=2 loops=1)
  126. Filter: (scope = ANY ('{1}'::integer[]))
  127. -> Bitmap Heap Scan on record_badge_score s (cost=395.55..1279.06 rows=35001 width=16) (actual time=1.120..5.598 rows=35001 loops=2)
  128. Recheck Cond: (badge = b.id)
  129. Heap Blocks: exact=451
  130. -> Bitmap Index Scan on record_badge_score_badge_idx (cost=0.00..386.80 rows=35001 width=0) (actual time=1.087..1.087 rows=35001 loops=2)
  131. Index Cond: (badge = b.id)
  132. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list mrv (cost=0.43..0.53 rows=1 width=130) (actual time=0.005..0.005 rows=1 loops=504)
  133. Index Cond: (source = m.source)
  134. -> Index Scan using metabib_sorter_source_idx on record_sorter pubdate_t (cost=0.43..0.77 rows=2 width=27) (actual time=0.004..0.006 rows=1 loops=504)
  135. Index Cond: (source = m.source)
  136. Filter: (attr = 'pubdate'::text)
  137. Rows Removed by Filter: 2
  138. -> Materialize (cost=4.97..35.56 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=504)
  139. -> Nested Loop (cost=4.97..35.55 rows=1 width=4) (actual time=2.850..2.861 rows=1 loops=1)
  140. -> Nested Loop (cost=0.50..0.53 rows=1 width=0) (actual time=2.825..2.826 rows=1 loops=1)
  141. -> Function Scan on patron_default_visibility_mask x (cost=0.25..0.26 rows=1 width=0) (actual time=1.827..1.828 rows=1 loops=1)
  142. -> Function Scan on patron_default_visibility_mask x_1 (cost=0.25..0.26 rows=1 width=0) (actual time=0.992..0.992 rows=1 loops=1)
  143. -> Bitmap Heap Scan on coded_value_map (cost=4.47..35.01 rows=1 width=4) (actual time=0.019..0.028 rows=1 loops=1)
  144. Recheck Cond: (ctype = 'item_lang'::text)
  145. Filter: (code = 'eng'::text)
  146. Rows Removed by Filter: 24
  147. Heap Blocks: exact=4
  148. -> Bitmap Index Scan on config_coded_value_map_ctype_idx (cost=0.00..4.47 rows=25 width=0) (actual time=0.011..0.011 rows=25 loops=1)
  149. Index Cond: (ctype = 'item_lang'::text)
  150. -> Subquery Scan on "*SELECT* 1" (cost=0.00..37.50 rows=1000 width=124) (actual time=116380.820..116380.933 rows=119 loops=1)
  151. -> Limit (cost=0.00..20.00 rows=1000 width=112) (actual time=116380.818..116380.914 rows=119 loops=1)
  152. -> CTE Scan on w (cost=0.00..2000.00 rows=100000 width=112) (actual time=114439.643..114439.730 rows=119 loops=1)
  153. -> Aggregate (cost=2250.00..2250.01 rows=1 width=144) (actual time=0.019..0.020 rows=1 loops=1)
  154. -> CTE Scan on w w_1 (cost=0.00..2000.00 rows=100000 width=0) (actual time=0.000..0.011 rows=119 loops=1)
  155. Planning Time: 14.349 ms
  156. JIT:
  157. Functions: 164
  158. Options: Inlining true, Optimization true, Expressions true, Deforming true
  159. Timing: Generation 36.159 ms, Inlining 68.240 ms, Optimization 1331.693 ms, Emission 540.453 ms, Total 1976.546 ms
  160. Execution Time: 116418.645 ms
  161. (158 rows)
  162.  
  163.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement