explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 24P5 : EG3.1.10

Settings
# exclusive inclusive rows x rows loops node
1. 0.092 44,182.026 ↑ 1.0 1,001 1

Append (cost=1,639,913.20..1,640,517.14 rows=1,001 width=112) (actual time=44,166.299..44,182.026 rows=1,001 loops=1)

2.          

CTE w

3. 5.682 44,174.855 ↑ 1.3 15,522 1

Subquery Scan on core_query (cost=1,639,547.97..1,639,913.20 rows=20,870 width=116) (actual time=44,166.292..44,174.855 rows=15,522 loops=1)

4.          

CTE xda2f5b0_keyword_xq

5. 0.408 0.408 ↑ 1.0 1 1

Result (cost=0.00..1.02 rows=1 width=0) (actual time=0.408..0.408 rows=1 loops=1)

6.          

CTE lang_with

7. 0.087 0.142 ↑ 1.0 1 1

Bitmap Heap Scan on coded_value_map (cost=9.93..51.22 rows=1 width=4) (actual time=0.076..0.142 rows=1 loops=1)

  • Recheck Cond: (ctype = 'item_lang'::text)
  • Filter: (code = 'eng'::text)
  • Rows Removed by Filter: 485
  • Heap Blocks: exact=5
8. 0.055 0.055 ↑ 1.0 486 1

Bitmap Index Scan on config_coded_value_map_ctype_idx (cost=0.00..9.92 rows=486 width=0) (actual time=0.055..0.055 rows=486 loops=1)

  • Index Cond: (ctype = 'item_lang'::text)
9.          

CTE pop_with

10. 11.550 13.551 ↑ 1.0 6,248 1

HashAggregate (cost=346.55..471.51 rows=6,248 width=20) (actual time=10.085..13.551 rows=6,248 loops=1)

  • Group Key: s.record
11. 1.180 2.001 ↑ 1.0 6,248 1

Nested Loop (cost=0.00..221.59 rows=6,248 width=20) (actual time=0.171..2.001 rows=6,248 loops=1)

  • Join Filter: (s.badge = b.id)
12. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on badge b (cost=0.00..1.01 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1)

  • Filter: (scope = ANY ('{1}'::integer[]))
13. 0.804 0.804 ↑ 1.0 6,248 1

Seq Scan on record_badge_score s (cost=0.00..142.48 rows=6,248 width=16) (actual time=0.146..0.804 rows=6,248 loops=1)

14.          

CTE c_attr

15. 5.442 5.442 ↑ 1.0 1 1

Function Scan on patron_default_visibility_mask x (cost=0.25..0.27 rows=1 width=32) (actual time=5.442..5.442 rows=1 loops=1)

16.          

CTE b_attr

17. 1.088 1.088 ↑ 1.0 1 1

Function Scan on patron_default_visibility_mask x_1 (cost=0.25..0.27 rows=1 width=32) (actual time=1.087..1.088 rows=1 loops=1)

18. 1.117 44,169.173 ↑ 1.3 15,522 1

Limit (cost=1,639,023.69..1,639,075.86 rows=20,870 width=453) (actual time=44,166.281..44,169.173 rows=15,522 loops=1)

19. 34.386 44,168.056 ↑ 1.3 15,522 1

Sort (cost=1,639,023.69..1,639,075.86 rows=20,870 width=453) (actual time=44,166.280..44,168.056 rows=15,522 loops=1)

  • 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[lang_with.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[lang_with.id])), false))::integer * 5), 1)))::double precision))::numeric) DESC
  • Sort Method: quicksort Memory: 2567kB
20. 486.183 44,133.670 ↑ 1.3 15,522 1

GroupAggregate (cost=271,721.82..1,637,526.35 rows=20,870 width=453) (actual time=41,139.339..44,133.670 rows=15,522 loops=1)

  • Group Key: m.source
21. 65.816 43,647.487 ↓ 1.4 28,840 1

Nested Loop (cost=271,721.82..1,619,786.85 rows=20,870 width=453) (actual time=41,139.023..43,647.487 rows=28,840 loops=1)

  • Join Filter: ((SubPlan 6) OR (b_attr.vis_test IS NULL) OR (bre.vis_attr_vector @@ b_attr.vis_test))
  • Rows Removed by Join Filter: 5751
22. 8.778 43,097.397 ↑ 1.2 34,591 1

Nested Loop Left Join (cost=271,721.82..438,668.85 rows=41,491 width=483) (actual time=41,132.337..43,097.397 rows=34,591 loops=1)

23. 13.763 42,465.981 ↓ 1.4 34,591 1

Merge Left Join (cost=271,721.38..417,196.89 rows=24,842 width=468) (actual time=41,132.285..42,465.981 rows=34,591 loops=1)

  • Merge Cond: (m.source = pop_with.record)
24. 17.761 42,432.266 ↓ 1.4 34,591 1

Nested Loop (cost=271,202.51..416,583.60 rows=24,842 width=404) (actual time=41,114.721..42,432.266 rows=34,591 loops=1)

25. 33.447 41,860.889 ↓ 1.4 34,601 1

Nested Loop (cost=271,202.09..403,547.36 rows=25,198 width=298) (actual time=41,114.657..41,860.889 rows=34,601 loops=1)

26. 117.561 41,529.418 ↑ 1.6 37,253 1

Merge Join (cost=271,201.66..317,713.75 rows=59,030 width=256) (actual time=41,114.595..41,529.418 rows=37,253 loops=1)

  • Merge Cond: (m.source = "*SELECT* 1_1".source)
27. 255.331 255.331 ↓ 1.0 1,434,809 1

Index Only Scan using metabib_metarecord_source_map_source_record_idx on metarecord_source_map m (cost=0.43..42,043.55 rows=1,433,408 width=8) (actual time=0.020..255.331 rows=1,434,809 loops=1)

  • Heap Fetches: 85128
28. 166.918 41,156.526 ↓ 1.5 88,300 1

Sort (cost=271,201.23..271,348.80 rows=59,030 width=248) (actual time=41,114.546..41,156.526 rows=88,300 loops=1)

  • Sort Key: "*SELECT* 1_1".source
  • Sort Method: quicksort Memory: 57467kB
29. 16.672 40,989.608 ↓ 1.5 88,300 1

Append (cost=4,206.08..266,523.35 rows=59,030 width=248) (actual time=2,206.549..40,989.608 rows=88,300 loops=1)

30. 9.951 16,328.775 ↓ 3.0 46,202 1

Subquery Scan on *SELECT* 1_1 (cost=4,206.08..32,520.82 rows=15,220 width=527) (actual time=2,206.548..16,328.775 rows=46,202 loops=1)

31. 36.335 16,318.824 ↓ 3.0 46,202 1

Hash Join (cost=4,206.08..32,368.62 rows=15,220 width=527) (actual time=2,206.548..16,318.824 rows=46,202 loops=1)

  • Hash Cond: (fe.field = fe_weight.id)
32. 16.382 16,282.462 ↓ 3.0 46,202 1

Nested Loop (cost=4,202.37..32,155.64 rows=15,220 width=527) (actual time=2,206.495..16,282.462 rows=46,202 loops=1)

33. 0.412 0.412 ↑ 1.0 1 1

CTE Scan on xda2f5b0_keyword_xq (cost=0.00..0.02 rows=1 width=64) (actual time=0.410..0.412 rows=1 loops=1)

34. 14,159.982 16,265.668 ↓ 3.0 46,202 1

Bitmap Heap Scan on keyword_field_entry fe (cost=4,202.37..32,003.42 rows=15,220 width=495) (actual time=2,206.079..16,265.668 rows=46,202 loops=1)

  • Recheck Cond: (index_vector @@ xda2f5b0_keyword_xq.tsq)
  • Rows Removed by Index Recheck: 410946
  • Filter: (id IS NOT NULL)
  • Heap Blocks: exact=299973
35. 2,105.686 2,105.686 ↓ 34.4 524,172 1

Bitmap Index Scan on metabib_keyword_field_entry_index_vector_idx (cost=0.00..4,198.56 rows=15,220 width=0) (actual time=2,105.686..2,105.686 rows=524,172 loops=1)

  • Index Cond: (index_vector @@ xda2f5b0_keyword_xq.tsq)
36. 0.017 0.027 ↑ 1.4 54 1

Hash (cost=2.76..2.76 rows=76 width=8) (actual time=0.027..0.027 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
37. 0.010 0.010 ↑ 1.4 54 1

Seq Scan on metabib_field fe_weight (cost=0.00..2.76 rows=76 width=8) (actual time=0.004..0.010 rows=54 loops=1)

38. 0.097 9,057.295 ↑ 34.2 784 1

Subquery Scan on *SELECT* 2 (cost=2,601.03..73,889.76 rows=26,790 width=112) (actual time=8,990.154..9,057.295 rows=784 loops=1)

39. 0.164 9,057.198 ↑ 34.2 784 1

Nested Loop (cost=2,601.03..73,621.86 rows=26,790 width=112) (actual time=8,990.153..9,057.198 rows=784 loops=1)

40. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on metabib_field_virtual_map fe_weight_1 (cost=0.00..1.61 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=1)

  • Filter: ((virtual = 45) AND ("real" = 8))
  • Rows Removed by Filter: 40
41. 0.185 9,057.018 ↑ 34.2 784 1

Nested Loop (cost=2,601.03..73,352.34 rows=26,790 width=112) (actual time=8,990.134..9,057.018 rows=784 loops=1)

42. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on xda2f5b0_keyword_xq xda2f5b0_keyword_xq_1 (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)

43. 66.983 9,056.831 ↑ 34.2 784 1

Bitmap Heap Scan on author_field_entry fe_1 (cost=2,601.03..73,084.42 rows=26,790 width=80) (actual time=8,990.125..9,056.831 rows=784 loops=1)

  • Recheck Cond: (index_vector @@ xda2f5b0_keyword_xq_1.tsq)
  • Rows Removed by Index Recheck: 200
  • Filter: ((id IS NOT NULL) AND (field = 8))
  • Rows Removed by Filter: 1058
  • Heap Blocks: exact=1073
44. 8,989.848 8,989.848 ↑ 23.7 2,127 1

Bitmap Index Scan on metabib_author_field_entry_index_vector_idx (cost=0.00..2,594.34 rows=50,389 width=0) (actual time=8,989.848..8,989.848 rows=2,127 loops=1)

  • Index Cond: (index_vector @@ xda2f5b0_keyword_xq_1.tsq)
45. 3.069 3,715.649 ↓ 1.8 23,963 1

Subquery Scan on *SELECT* 3 (cost=3,434.01..95,087.41 rows=13,384 width=156) (actual time=3,524.747..3,715.649 rows=23,963 loops=1)

46. 4.315 3,712.580 ↓ 1.8 23,963 1

Nested Loop (cost=3,434.01..94,953.57 rows=13,384 width=156) (actual time=3,524.747..3,712.580 rows=23,963 loops=1)

47. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on metabib_field_virtual_map fe_weight_2 (cost=0.00..1.61 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=1)

  • Filter: ((virtual = 45) AND ("real" = 16))
  • Rows Removed by Filter: 40
48. 3.900 3,708.250 ↓ 1.8 23,963 1

Nested Loop (cost=3,434.01..94,818.11 rows=13,384 width=156) (actual time=3,524.730..3,708.250 rows=23,963 loops=1)

49. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on xda2f5b0_keyword_xq xda2f5b0_keyword_xq_2 (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.003 rows=1 loops=1)

50. 186.285 3,704.347 ↓ 1.8 23,963 1

Bitmap Heap Scan on subject_field_entry fe_2 (cost=3,434.01..94,684.25 rows=13,384 width=124) (actual time=3,524.724..3,704.347 rows=23,963 loops=1)

  • Recheck Cond: (index_vector @@ xda2f5b0_keyword_xq_2.tsq)
  • Rows Removed by Index Recheck: 12
  • Filter: ((id IS NOT NULL) AND (field = 16))
  • Rows Removed by Filter: 39379
  • Heap Blocks: exact=26937
51. 3,518.062 3,518.062 ↓ 1.1 66,878 1

Bitmap Index Scan on metabib_subject_field_entry_index_vector_idx (cost=0.00..3,430.66 rows=59,766 width=0) (actual time=3,518.062..3,518.062 rows=66,878 loops=1)

  • Index Cond: (index_vector @@ xda2f5b0_keyword_xq_2.tsq)
52. 0.001 8,497.619 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=4,204.33..32,266.64 rows=2,598 width=527) (actual time=8,497.619..8,497.619 rows=0 loops=1)

53. 9.933 8,497.618 ↓ 0.0 0 1

Hash Join (cost=4,204.33..32,240.66 rows=2,598 width=527) (actual time=8,497.618..8,497.618 rows=0 loops=1)

  • Hash Cond: (fe_3.field = fe_weight_3."real")
54. 13.470 8,487.662 ↓ 3.0 46,202 1

Nested Loop (cost=4,202.37..32,155.64 rows=15,220 width=527) (actual time=2,821.113..8,487.662 rows=46,202 loops=1)

55. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on xda2f5b0_keyword_xq xda2f5b0_keyword_xq_3 (cost=0.00..0.02 rows=1 width=64) (actual time=0.002..0.006 rows=1 loops=1)

56. 5,755.060 8,474.186 ↓ 3.0 46,202 1

Bitmap Heap Scan on keyword_field_entry fe_3 (cost=4,202.37..32,003.42 rows=15,220 width=495) (actual time=2,821.103..8,474.186 rows=46,202 loops=1)

  • Recheck Cond: (index_vector @@ xda2f5b0_keyword_xq_3.tsq)
  • Rows Removed by Index Recheck: 410946
  • Filter: (id IS NOT NULL)
  • Heap Blocks: exact=299973
57. 2,719.126 2,719.126 ↓ 34.4 524,172 1

Bitmap Index Scan on metabib_keyword_field_entry_index_vector_idx (cost=0.00..4,198.56 rows=15,220 width=0) (actual time=2,719.126..2,719.126 rows=524,172 loops=1)

  • Index Cond: (index_vector @@ xda2f5b0_keyword_xq_3.tsq)
58. 0.003 0.023 ↑ 1.0 7 1

Hash (cost=1.87..1.87 rows=7 width=8) (actual time=0.023..0.023 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.020 0.020 ↑ 1.0 7 1

Seq Scan on metabib_field_virtual_map fe_weight_3 (cost=0.00..1.87 rows=7 width=8) (actual time=0.016..0.020 rows=7 loops=1)

  • Filter: ((virtual = 45) AND ("real" = ANY ('{39,41,42,46,47,48,50}'::integer[])))
  • Rows Removed by Filter: 34
60. 4.224 3,373.598 ↓ 16.7 17,351 1

Subquery Scan on *SELECT* 5 (cost=1,280.97..32,758.72 rows=1,038 width=145) (actual time=905.889..3,373.598 rows=17,351 loops=1)

61. 13.040 3,369.374 ↓ 16.7 17,351 1

Hash Join (cost=1,280.97..32,748.34 rows=1,038 width=145) (actual time=905.889..3,369.374 rows=17,351 loops=1)

  • Hash Cond: (fe_4.field = fe_weight_4."real")
62. 6.237 3,356.316 ↑ 1.0 20,499 1

Nested Loop (cost=1,279.33..32,656.52 rows=21,279 width=145) (actual time=905.848..3,356.316 rows=20,499 loops=1)

63. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on xda2f5b0_keyword_xq xda2f5b0_keyword_xq_4 (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)

64. 2,448.351 3,350.077 ↑ 1.0 20,499 1

Bitmap Heap Scan on title_field_entry fe_4 (cost=1,279.33..32,443.71 rows=21,279 width=113) (actual time=905.839..3,350.077 rows=20,499 loops=1)

  • Recheck Cond: (index_vector @@ xda2f5b0_keyword_xq_4.tsq)
  • Rows Removed by Index Recheck: 8
  • Filter: (id IS NOT NULL)
  • Heap Blocks: exact=16350
65. 901.726 901.726 ↓ 1.1 23,077 1

Bitmap Index Scan on metabib_title_field_entry_index_vector_idx (cost=0.00..1,274.01 rows=21,279 width=0) (actual time=901.726..901.726 rows=23,077 loops=1)

  • Index Cond: (index_vector @@ xda2f5b0_keyword_xq_4.tsq)
66. 0.002 0.018 ↑ 1.0 2 1

Hash (cost=1.61..1.61 rows=2 width=8) (actual time=0.018..0.018 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.016 0.016 ↑ 1.0 2 1

Seq Scan on metabib_field_virtual_map fe_weight_4 (cost=0.00..1.61 rows=2 width=8) (actual time=0.015..0.016 rows=2 loops=1)

  • Filter: (("real" = ANY ('{6,53}'::integer[])) AND (virtual = 45))
  • Rows Removed by Filter: 39
68. 298.024 298.024 ↑ 1.0 1 37,253

Index Scan using record_entry_pkey on record_entry bre (cost=0.43..1.44 rows=1 width=42) (actual time=0.008..0.008 rows=1 loops=37,253)

  • Index Cond: (id = m.source)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
69. 553.616 553.616 ↑ 1.0 1 34,601

Index Scan using record_attr_vector_list_pkey on record_attr_vector_list mrv (cost=0.43..0.51 rows=1 width=130) (actual time=0.015..0.016 rows=1 loops=34,601)

  • Index Cond: (source = m.source)
70. 4.357 19.952 ↓ 1.0 6,309 1

Sort (cost=518.87..534.49 rows=6,248 width=72) (actual time=17.558..19.952 rows=6,309 loops=1)

  • Sort Key: pop_with.record
  • Sort Method: quicksort Memory: 681kB
71. 15.595 15.595 ↑ 1.0 6,248 1

CTE Scan on pop_with (cost=0.00..124.96 rows=6,248 width=72) (actual time=10.088..15.595 rows=6,248 loops=1)

72. 622.638 622.638 ↑ 4.0 1 34,591

Index Scan using metabib_sorter_source_idx on record_sorter pubdate_t (cost=0.43..0.82 rows=4 width=23) (actual time=0.017..0.018 rows=1 loops=34,591)

  • Index Cond: (m.source = source)
  • Filter: (attr = 'pubdate'::text)
  • Rows Removed by Filter: 2
73. 0.000 0.000 ↑ 1.0 1 34,591

Materialize (cost=0.00..0.09 rows=1 width=68) (actual time=0.000..0.000 rows=1 loops=34,591)

74. 0.002 6.688 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.08 rows=1 width=68) (actual time=6.620..6.688 rows=1 loops=1)

75. 0.001 5.596 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.05 rows=1 width=36) (actual time=5.529..5.596 rows=1 loops=1)

76. 0.144 0.144 ↑ 1.0 1 1

CTE Scan on lang_with (cost=0.00..0.02 rows=1 width=4) (actual time=0.078..0.144 rows=1 loops=1)

77. 5.451 5.451 ↑ 1.0 1 1

CTE Scan on c_attr (cost=0.00..0.02 rows=1 width=32) (actual time=5.450..5.451 rows=1 loops=1)

78. 1.090 1.090 ↑ 1.0 1 1

CTE Scan on b_attr (cost=0.00..0.02 rows=1 width=32) (actual time=1.089..1.090 rows=1 loops=1)

79.          

SubPlan (forNested Loop)

80. 484.274 484.274 ↑ 1.0 1 34,591

Index Scan using copy_vis_attr_cache_record_idx on copy_vis_attr_cache (cost=0.43..28.45 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=34,591)

  • Index Cond: (record = m.source)
  • Filter: (vis_attr_vector @@ c_attr.vis_test)
  • Rows Removed by Filter: 0
81. 0.233 44,167.560 ↑ 1.0 1,000 1

Subquery Scan on *SELECT* 1 (cost=0.00..30.00 rows=1,000 width=112) (actual time=44,166.299..44,167.560 rows=1,000 loops=1)

82. 0.075 44,167.327 ↑ 1.0 1,000 1

Limit (cost=0.00..20.00 rows=1,000 width=112) (actual time=44,166.296..44,167.327 rows=1,000 loops=1)

83. 44,167.252 44,167.252 ↑ 20.9 1,000 1

CTE Scan on w (cost=0.00..417.40 rows=20,870 width=112) (actual time=44,166.296..44,167.252 rows=1,000 loops=1)

84. 1.071 14.374 ↑ 1.0 1 1

Aggregate (cost=573.93..573.94 rows=1 width=0) (actual time=14.374..14.374 rows=1 loops=1)

85. 13.303 13.303 ↑ 1.3 15,522 1

CTE Scan on w w_1 (cost=0.00..417.40 rows=20,870 width=0) (actual time=0.001..13.303 rows=15,522 loops=1)

Planning time : 94.782 ms
Execution time : 44,190.389 ms