API tools faq
paste
Advertisement
chrissharp123

Untitled

chrissharp123
Aug 31st, 2017
298
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.79 KB | None | 0 0
raw download clone embed print report
  1. explain analyze SELECT DISTINCT(record),
  2. COALESCE(n72.quality, 0) + COALESCE(n73.quality, 0) + COALESCE(n74.quality, 0) + COALESCE(n75.quality, 0) + COALESCE(n76.quality, 0) + COALESCE(n77.quality, 0) AS quality
  3. FROM
  4. (SELECT *, 2 AS quality FROM metabib.full_rec mfr WHERE mfr.tag = '020' AND mfr.subfield = 'a' AND (LOWER(mfr.value) LIKE '9781492629887%' OR LOWER(mfr.value) LIKE '149262988x%')) n72
  5. FULL OUTER JOIN (SELECT *, 2 AS quality FROM metabib.full_rec mfr WHERE mfr.tag = '022' AND mfr.subfield = 'a' AND (LOWER(mfr.value) LIKE NULL)) n73 USING (record)
  6. FULL OUTER JOIN (SELECT *, 2 AS quality FROM metabib.full_rec mfr WHERE mfr.tag = '024' AND mfr.subfield = 'a' AND (LOWER(mfr.value) LIKE NULL)) n74 USING (record)
  7. FULL OUTER JOIN (SELECT *, 2 AS quality FROM metabib.full_rec mfr WHERE mfr.tag = '028' AND mfr.subfield = 'a' AND (mfr.value = NULL)) n75 USING (record)
  8. FULL OUTER JOIN (SELECT *, id AS record, 16 AS quality FROM metabib.record_attr_flat mraf WHERE mraf.attr = 'bib_level' AND mraf.value = 'm') n76 USING (record)
  9. FULL OUTER JOIN (SELECT *, id AS record, 16 AS quality FROM metabib.record_attr_flat mraf WHERE mraf.attr = 'item_type' AND mraf.value = 'a') n77 USING (record)
  10. JOIN biblio.record_entry bre ON (bre.id = record)
  11. WHERE (((n72.id IS NOT NULL) OR (n73.id IS NOT NULL) OR (n74.id IS NOT NULL) OR (n75.id IS NOT NULL)) AND (n76.id IS NOT NULL) AND (n77.id IS NOT NULL)) AND not bre.deleted;
  12.  
  13. result:
  14. QUERY PLAN
  15. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  16. HashAggregate (cost=11498437.71..11498437.76 rows=2 width=72) (actual time=12840.131..12840.133 rows=1 loops=1)
  17. Group Key: COALESCE(COALESCE(COALESCE(COALESCE(COALESCE(real_full_rec.record, real_full_rec_1.record), real_full_rec_2.record), real_full_rec_3.record), mraf.id), mraf_1.id), (((((COALESCE((2), 0) + COALESCE((2), 0)) + COALESCE((2), 0)) + COALESCE((2), 0)) + COALESCE((16), 0)) + COALESCE((16), 0))
  18. -> Hash Join (cost=9948150.47..11364916.07 rows=26704328 width=72) (actual time=12840.104..12840.112 rows=2 loops=1)
  19. Hash Cond: (COALESCE(COALESCE(COALESCE(COALESCE(COALESCE(real_full_rec.record, real_full_rec_1.record), real_full_rec_2.record), real_full_rec_3.record), mraf.id), mraf_1.id) = bre.id)
  20. -> Merge Join (cost=9165034.80..9776985.86 rows=40793432 width=72) (actual time=10068.682..10068.688 rows=2 loops=1)
  21. Merge Cond: (mraf_1.id = (COALESCE(COALESCE(COALESCE(COALESCE(real_full_rec.record, real_full_rec_1.record), real_full_rec_2.record), real_full_rec_3.record), mraf.id)))
  22. -> Sort (cost=207563.86..207588.65 rows=9916 width=12) (actual time=4620.808..4943.388 rows=1498506 loops=1)
  23. Sort Key: mraf_1.id
  24. Sort Method: quicksort Memory: 122345kB
  25. -> Subquery Scan on mraf_1 (cost=206707.34..206905.66 rows=9916 width=12) (actual time=2782.914..3855.859 rows=1561443 loops=1)
  26. -> HashAggregate (cost=206707.34..206806.50 rows=9916 width=22) (actual time=2782.909..3500.596 rows=1561443 loops=1)
  27. Group Key: v.source, m.attr, m.value
  28. -> Append (cost=0.28..206632.97 rows=9916 width=22) (actual time=0.119..1595.257 rows=1561443 loops=1)
  29. -> Nested Loop (cost=0.28..103255.63 rows=4410 width=17) (actual time=0.038..0.038 rows=0 loops=1)
  30. Join Filter: (m.id = ANY (v.vlist))
  31. -> Index Scan using muv_once_idx on uncontrolled_record_attr_value m (cost=0.28..6.23 rows=1 width=17) (actual time=0.035..0.035 rows=0 loops=1)
  32. Index Cond: ((attr = 'item_type'::text) AND (value = 'a'::text))
  33. -> Seq Scan on record_attr_vector_list v (cost=0.00..62760.20 rows=1799520 width=111) (never executed)
  34. Filter: (source IS NOT NULL)
  35. -> Nested Loop (cost=3.42..103278.18 rows=5506 width=26) (actual time=0.079..1321.168 rows=1561443 loops=1)
  36. Join Filter: (c.id = ANY (v_1.vlist))
  37. Rows Removed by Join Filter: 238115
  38. -> Bitmap Heap Scan on coded_value_map c (cost=3.42..28.78 rows=1 width=22) (actual time=0.050..0.071 rows=1 loops=1)
  39. Recheck Cond: (ctype = 'item_type'::text)
  40. Filter: (code = 'a'::text)
  41. Rows Removed by Filter: 18
  42. Heap Blocks: exact=3
  43. -> Bitmap Index Scan on config_coded_value_map_ctype_idx (cost=0.00..3.42 rows=19 width=0) (actual time=0.025..0.025 rows=19 loops=1)
  44. Index Cond: (ctype = 'item_type'::text)
  45. -> Seq Scan on record_attr_vector_list v_1 (cost=0.00..62760.20 rows=1799520 width=111) (actual time=0.009..529.943 rows=1799558 loops=1)
  46. Filter: (source IS NOT NULL)
  47. -> Sort (cost=8957470.94..8959527.89 rows=822780 width=60) (actual time=4899.926..4899.926 rows=2 loops=1)
  48. Sort Key: (COALESCE(COALESCE(COALESCE(COALESCE(real_full_rec.record, real_full_rec_1.record), real_full_rec_2.record), real_full_rec_3.record), mraf.id))
  49. Sort Method: quicksort Memory: 25kB
  50. -> Hash Right Join (cost=5983300.16..8876632.20 rows=822780 width=60) (actual time=4620.009..4899.869 rows=2 loops=1)
  51. Hash Cond: (COALESCE(COALESCE(COALESCE(real_full_rec.record, real_full_rec_1.record), real_full_rec_2.record), real_full_rec_3.record) = mraf.id)
  52. Filter: ((real_full_rec.id IS NOT NULL) OR (real_full_rec_1.id IS NOT NULL) OR (real_full_rec_2.id IS NOT NULL) OR (real_full_rec_3.id IS NOT NULL))
  53. Rows Removed by Filter: 1784954
  54. -> Hash Full Join (cost=5776281.25..8640766.20 rows=16595 width=80) (actual time=0.406..0.501 rows=2 loops=1)
  55. Hash Cond: (COALESCE(COALESCE(real_full_rec.record, real_full_rec_1.record), real_full_rec_2.record) = real_full_rec_3.record)
  56. -> Hash Full Join (cost=2911924.84..5776347.55 rows=16595 width=60) (actual time=0.351..0.443 rows=2 loops=1)
  57. Hash Cond: (COALESCE(real_full_rec.record, real_full_rec_1.record) = real_full_rec_2.record)
  58. -> Hash Full Join (cost=47568.42..2911928.90 rows=16595 width=40) (actual time=0.333..0.419 rows=2 loops=1)
  59. Hash Cond: (real_full_rec_1.record = real_full_rec.record)
  60. -> Result (cost=0.00..2864356.40 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1)
  61. One-Time Filter: false
  62. -> Seq Scan on real_full_rec real_full_rec_1 (cost=0.00..2864356.40 rows=1 width=20) (never executed)
  63. -> Hash (cost=47360.99..47360.99 rows=16595 width=20) (actual time=0.136..0.136 rows=2 loops=1)
  64. Buckets: 32768 Batches: 1 Memory Usage: 257kB
  65. -> Bitmap Heap Scan on real_full_rec (cost=522.95..47360.99 rows=16595 width=20) (actual time=0.122..0.129 rows=2 loops=1)
  66. Recheck Cond: (((tag = '020'::bpchar) AND (subfield = 'a'::text) AND (lower("substring"(value, 1, 1024)) ~~ '9781492629887%'::text)) OR ((tag = '020'::bpchar) AND (subfield = 'a'::text) AND (lower("substring"(value, 1, 1024)) ~~ '149262988x%'::text)))
  67. Filter: ((lower("substring"(value, 1, 1024)) ~~ '9781492629887%'::text) OR (lower("substring"(value, 1, 1024)) ~~ '149262988x%'::text))
  68. Heap Blocks: exact=3
  69. -> BitmapOr (cost=522.95..522.95 rows=16636 width=0) (actual time=0.084..0.084 rows=0 loops=1)
  70. -> Bitmap Index Scan on metabib_full_rec_02x_tag_subfield_lower_substring (cost=0.00..257.32 rows=8318 width=0) (actual time=0.064..0.064 rows=2 loops=1)
  71. Index Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text) AND (lower("substring"(value, 1, 1024)) >= '9781492629887'::text) AND (lower("substring"(value, 1, 1024)) < '9781492629888'::text))
  72. -> Bitmap Index Scan on metabib_full_rec_02x_tag_subfield_lower_substring (cost=0.00..257.32 rows=8318 width=0) (actual time=0.019..0.019 rows=2 loops=1)
  73. Index Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text) AND (lower("substring"(value, 1, 1024)) >= '149262988x'::text) AND (lower("substring"(value, 1, 1024)) < '149262988y'::text))
  74. -> Hash (cost=2864356.40..2864356.40 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=1)
  75. Buckets: 1024 Batches: 1 Memory Usage: 8kB
  76. -> Result (cost=0.00..2864356.40 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=1)
  77. One-Time Filter: false
  78. -> Seq Scan on real_full_rec real_full_rec_2 (cost=0.00..2864356.40 rows=1 width=20) (never executed)
  79. -> Hash (cost=2864356.40..2864356.40 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=1)
  80. Buckets: 1024 Batches: 1 Memory Usage: 8kB
  81. -> Result (cost=0.00..2864356.40 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=1)
  82. One-Time Filter: false
  83. -> Seq Scan on real_full_rec real_full_rec_3 (cost=0.00..2864356.40 rows=1 width=20) (never executed)
  84. -> Hash (cost=206894.96..206894.96 rows=9916 width=12) (actual time=4619.520..4619.520 rows=1784955 loops=1)
  85. Buckets: 2097152 (originally 16384) Batches: 1 (originally 1) Memory Usage: 93082kB
  86. -> Subquery Scan on mraf (cost=206696.64..206894.96 rows=9916 width=12) (actual time=2881.340..4088.064 rows=1784955 loops=1)
  87. -> HashAggregate (cost=206696.64..206795.80 rows=9916 width=22) (actual time=2881.335..3675.337 rows=1784955 loops=1)
  88. Group Key: v_2.source, m_1.attr, m_1.value
  89. -> Append (cost=0.28..206622.27 rows=9916 width=22) (actual time=0.120..1612.990 rows=1784955 loops=1)
  90. -> Nested Loop (cost=0.28..103255.63 rows=4410 width=17) (actual time=0.041..0.041 rows=0 loops=1)
  91. Join Filter: (m_1.id = ANY (v_2.vlist))
  92. -> Index Scan using muv_once_idx on uncontrolled_record_attr_value m_1 (cost=0.28..6.23 rows=1 width=17) (actual time=0.038..0.038 rows=0 loops=1)
  93. Index Cond: ((attr = 'bib_level'::text) AND (value = 'm'::text))
  94. -> Seq Scan on record_attr_vector_list v_2 (cost=0.00..62760.20 rows=1799520 width=111) (never executed)
  95. Filter: (source IS NOT NULL)
  96. -> Nested Loop (cost=3.33..103267.49 rows=5506 width=26) (actual time=0.077..1295.323 rows=1784955 loops=1)
  97. Join Filter: (c_1.id = ANY (v_3.vlist))
  98. Rows Removed by Join Filter: 14603
  99. -> Bitmap Heap Scan on coded_value_map c_1 (cost=3.33..18.09 rows=1 width=22) (actual time=0.049..0.074 rows=1 loops=1)
  100. Recheck Cond: (ctype = 'bib_level'::text)
  101. Filter: (code = 'm'::text)
  102. Rows Removed by Filter: 6
  103. Heap Blocks: exact=4
  104. -> Bitmap Index Scan on config_coded_value_map_ctype_idx (cost=0.00..3.33 rows=7 width=0) (actual time=0.021..0.021 rows=7 loops=1)
  105. Index Cond: (ctype = 'bib_level'::text)
  106. -> Seq Scan on record_attr_vector_list v_3 (cost=0.00..62760.20 rows=1799520 width=111) (actual time=0.009..512.828 rows=1799558 loops=1)
  107. Filter: (source IS NOT NULL)
  108. -> Hash (cost=760178.72..760178.72 rows=1834956 width=8) (actual time=2755.076..2755.076 rows=1709519 loops=1)
  109. Buckets: 2097152 Batches: 1 Memory Usage: 83163kB
  110. -> Seq Scan on record_entry bre (cost=0.00..760178.72 rows=1834956 width=8) (actual time=0.021..2007.377 rows=1709519 loops=1)
  111. Filter: (NOT deleted)
  112. Rows Removed by Filter: 895801
  113. Planning time: 8.898 ms
  114. Execution time: 12846.116 ms
  115. (99 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!