explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2e3g : Optimization for: plan #MqDk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Append (cost=1,372,921.06..1,373,346.09 rows=1,001 width=112) (actual rows= loops=)

2.          

CTE w

3. 0.000 0.000 ↓ 0.0

Subquery Scan on core_query (cost=1,372,669.69..1,372,921.06 rows=14,364 width=116) (actual rows= loops=)

4.          

CTE xfab57e8_keyword_xq

5. 0.000 0.000 ↓ 0.0

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

6.          

CTE lang_with

7. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on coded_value_map (cost=7.93..24.22 rows=1 width=4) (actual rows= loops=)

  • Recheck Cond: (ctype = 'item_lang'::text)
  • Filter: (code = 'eng'::text)
8. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on config_coded_value_map_ctype_idx (cost=0.00..7.92 rows=486 width=0) (actual rows= loops=)

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

CTE pop_with

10. 0.000 0.000 ↓ 0.0

HashAggregate (cost=16.73..17.35 rows=31 width=20) (actual rows= loops=)

  • Group Key: s.record
11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4.40..16.11 rows=31 width=20) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

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

  • Filter: (scope = ANY ('{9,1}'::integer[]))
13. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on record_badge_score s (cost=4.40..14.79 rows=31 width=16) (actual rows= loops=)

  • Recheck Cond: (badge = b.id)
14. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on record_badge_score_badge_idx (cost=0.00..4.39 rows=31 width=0) (actual rows= loops=)

  • Index Cond: (badge = b.id)
15.          

CTE c_attr

16. 0.000 0.000 ↓ 0.0

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

17.          

CTE b_attr

18. 0.000 0.000 ↓ 0.0

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

19. 0.000 0.000 ↓ 0.0

Limit (cost=1,372,626.57..1,372,662.48 rows=14,364 width=701) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Sort (cost=1,372,626.57..1,372,662.48 rows=14,364 width=701) (actual rows= loops=)

  • 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 precis
21. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,359,389.41..1,371,634.72 rows=14,364 width=701) (actual rows= loops=)

  • Group Key: m.source
22. 0.000 0.000 ↓ 0.0

Sort (cost=1,359,389.41..1,359,425.32 rows=14,364 width=701) (actual rows= loops=)

  • Sort Key: m.source
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4.57..1,358,397.57 rows=14,364 width=701) (actual rows= loops=)

  • Join Filter: ((SubPlan 6) OR (b_attr.vis_test IS NULL) OR (bre.vis_attr_vector @@ b_attr.vis_test))
24. 0.000 0.000 ↓ 0.0

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

25. 0.000 0.000 ↓ 0.0

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

26. 0.000 0.000 ↓ 0.0

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

27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

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

29. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.57..676,809.21 rows=28,557 width=722) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4.14..662,549.96 rows=28,557 width=706) (actual rows= loops=)

  • Hash Cond: (m.source = pop_with.record)
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.14..662,441.85 rows=28,557 width=642) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.84..652,092.80 rows=29,770 width=550) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.54..619,866.15 rows=34,214 width=517) (actual rows= loops=)

  • -> Index Only Scan using metabib_metarecord_source_map_source_record_idx on metarecord_source_map m (cost=0.30..0.52 rows
34. 0.000 0.000 ↓ 0.0

Append (cost=2.24..601,901.06 rows=34,214 width=509) (actual rows= loops=)

  • Index Cond: (source = "*SELECT* 1_1".source)
35. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1_1 (cost=2.24..216,777.17 rows=21,452 width=625) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.24..216,562.65 rows=21,452 width=625) (actual rows= loops=)

  • Hash Cond: (fe.field = fe_weight.id)
37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..216,265.45 rows=21,452 width=625) (actual rows= loops=)

  • Join Filter: (fe.index_vector @@ xfab57e8_keyword_xq.tsq)
38. 0.000 0.000 ↓ 0.0

CTE Scan on xfab57e8_keyword_xq (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on keyword_field_entry fe (cost=0.00..162,635.19 rows=4,290,419 width=593) (actual rows= loops=)

  • Filter: (id IS NOT NULL)
40. 0.000 0.000 ↓ 0.0

Hash (cost=1.55..1.55 rows=55 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on metabib_field fe_weight (cost=0.00..1.55 rows=55 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=1.72..61,407.95 rows=1,095 width=117) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.72..61,397.00 rows=1,095 width=117) (actual rows= loops=)

  • Join Filter: (fe_1.index_vector @@ xfab57e8_keyword_xq_1.tsq)
44. 0.000 0.000 ↓ 0.0

CTE Scan on xfab57e8_keyword_xq xfab57e8_keyword_xq_1 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.72..58,659.75 rows=218,978 width=85) (actual rows= loops=)

  • Hash Cond: (fe_1.field = fe_weight_1."real")
46. 0.000 0.000 ↓ 0.0

Seq Scan on author_field_entry fe_1 (cost=0.00..44,971.91 rows=3,065,691 width=85) (actual rows= loops=)

  • Filter: (id IS NOT NULL)
47. 0.000 0.000 ↓ 0.0

Hash (cost=1.68..1.68 rows=3 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on metabib_field_virtual_map fe_weight_1 (cost=0.00..1.68 rows=3 width=8) (actual rows= loops=)

  • Filter: ((virtual = 45) AND ("real" = ANY ('{7,6,8}'::integer[])))
49. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=0.00..42,241.84 rows=3,567 width=146) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..42,206.17 rows=3,567 width=146) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on metabib_field_virtual_map fe_weight_2 (cost=0.00..1.63 rows=1 width=8) (actual rows= loops=)

  • Filter: ((virtual = 45) AND ("real" = 53))
52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..42,168.87 rows=3,567 width=146) (actual rows= loops=)

  • Join Filter: (fe_2.index_vector @@ xfab57e8_keyword_xq_2.tsq)
53. 0.000 0.000 ↓ 0.0

CTE Scan on xfab57e8_keyword_xq xfab57e8_keyword_xq_2 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on title_field_entry fe_2 (cost=0.00..33,251.19 rows=713,413 width=114) (actual rows= loops=)

  • Filter: ((id IS NOT NULL) AND (field = 53))
55. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=2.11..199,458.32 rows=4,597 width=625) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.11..199,412.35 rows=4,597 width=625) (actual rows= loops=)

  • Join Filter: (fe_3.index_vector @@ xfab57e8_keyword_xq_3.tsq)
57. 0.000 0.000 ↓ 0.0

CTE Scan on xfab57e8_keyword_xq xfab57e8_keyword_xq_3 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.11..187,920.13 rows=919,376 width=593) (actual rows= loops=)

  • Hash Cond: (fe_3.field = fe_weight_3."real")
59. 0.000 0.000 ↓ 0.0

Seq Scan on keyword_field_entry fe_3 (cost=0.00..162,635.19 rows=4,290,419 width=593) (actual rows= loops=)

  • Filter: (id IS NOT NULL)
60. 0.000 0.000 ↓ 0.0

Hash (cost=2.00..2.00 rows=9 width=8) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on metabib_field_virtual_map fe_weight_3 (cost=0.00..2.00 rows=9 width=8) (actual rows= loops=)

  • Filter: ((virtual = 45) AND ("real" = ANY ('{1122,1124,39,41,42,46,47,48,50}'::integer[
62. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 5 (cost=0.00..82,015.79 rows=3,503 width=139) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..81,980.76 rows=3,503 width=139) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on metabib_field_virtual_map fe_weight_4 (cost=0.00..1.63 rows=1 width=8) (actual rows= loops=)

  • Filter: ((virtual = 45) AND ("real" = 16))
65. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..81,944.10 rows=3,503 width=139) (actual rows= loops=)

  • Join Filter: (fe_4.index_vector @@ xfab57e8_keyword_xq_4.tsq)
66. 0.000 0.000 ↓ 0.0

CTE Scan on xfab57e8_keyword_xq xfab57e8_keyword_xq_4 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on subject_field_entry fe_4 (cost=0.00..73,187.29 rows=700,543 width=107) (actual rows= loops=)

  • Filter: ((id IS NOT NULL) AND (field = 16))
68. 0.000 0.000 ↓ 0.0

Index Scan using record_entry_pkey on record_entry bre (cost=0.30..0.93 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (id = m.source)
  • Filter: (NOT deleted)
69. 0.000 0.000 ↓ 0.0

Index Scan using record_attr_vector_list_pkey on record_attr_vector_list mrv (cost=0.30..0.34 rows=1 width=116) (actual rows= loops=)

  • Index Cond: (source = m.source)
70. 0.000 0.000 ↓ 0.0

Hash (cost=0.62..0.62 rows=31 width=72) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

CTE Scan on pop_with (cost=0.00..0.62 rows=31 width=72) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Index Scan using metabib_sorter_s_a_idx on record_sorter pubdate_t (cost=0.43..0.49 rows=1 width=24) (actual rows= loops=)

  • Index Cond: ((m.source = source) AND (attr = 'pubdate'::text))
73.          

SubPlan (forNested Loop)

74. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on copy_vis_attr_cache (cost=4.34..23.86 rows=1 width=0) (actual rows= loops=)

  • Recheck Cond: (record = m.source)
  • Filter: (vis_attr_vector @@ c_attr.vis_test)
75. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on copy_vis_attr_cache_record_idx (cost=0.00..4.34 rows=5 width=0) (actual rows= loops=)

  • Index Cond: (record = m.source)
76. 0.000 0.000 ↓ 0.0

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

77. 0.000 0.000 ↓ 0.0

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

78. 0.000 0.000 ↓ 0.0

CTE Scan on w (cost=0.00..287.28 rows=14,364 width=112) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Aggregate (cost=395.01..395.02 rows=1 width=0) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

CTE Scan on w w_1 (cost=0.00..287.28 rows=14,364 width=0) (actual rows= loops=)