explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fNpD

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

Append (cost=250,892,508,072.28..528,621,095,166,284.88 rows=300,117,507,196 width=771) (actual rows= loops=)

2.          

CTE venue

3. 0.000 0.000 ↓ 0.0

Seq Scan on poi_major ma_1 (cost=0.00..22,944,269,379.23 rows=42,125,108 width=140) (actual rows= loops=)

  • Filter: ((newfeatcode <> 5990101) AND (newfeatcode > 5020000))
4.          

SubPlan (for Seq Scan)

5. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a (cost=0.57..290.38 rows=75 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma_1.oid) AND (fid = 876))
6. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_1 (cost=0.57..183.76 rows=47 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma_1.oid) AND (fid = 879))
7. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_2 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma_1.oid) AND (fid = 870))
8. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_3 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma_1.oid) AND (fid = 873))
9.          

CTE address_dup

10. 0.000 0.000 ↓ 0.0

CTE Scan on venue (cost=0.00..358,063,418.00 rows=1,758,009 width=140) (actual rows= loops=)

  • Filter: ((numberc IS NOT NULL) AND (numberc ~ '^((临时)|(新增)|(商铺)|(单号)|(双号)|(自编)|(商业)|([东西南北增付副附特第临商前后中旁单双之新旧老子内外正负])){0,1}[门排区段幢]{0,1}[0-9a-zA-Z甲乙丙丁戊己庚辛巳壬一二三四五六七八九十-]+'::text) AND (btrim(numberc) <> ''::text) AND (((streetc IS NOT NULL) AND (btrim(streetc) <> ''::text) AND ((SubPlan 6) > 0)) OR ((streetc IS NULL) AND (cunnamec IS NOT NULL) AND (btrim(cunnamec) <> ''::text)) OR ((streetc IS NULL) AND (xiangnamec IS NOT NULL) AND (btrim(xiangnamec) <> ''::text))))
11.          

SubPlan (for CTE Scan)

12. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.45..8.46 rows=1 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Only Scan using placenamecep_major_namec_pelias on placenamecep_major (cost=0.42..8.44 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (namec = venue.streetc)
14.          

CTE xiangcun_id

15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=35,270.06..35,356.00 rows=8,595 width=108) (actual rows= loops=)

  • Group Key: ad_1.owner, ad_1.numberc, ad_1.xiangnamec, ad_1.cunnamec
16. 0.000 0.000 ↓ 0.0

CTE Scan on address_dup ad_1 (cost=0.00..35,160.18 rows=8,790 width=108) (actual rows= loops=)

  • Filter: (streetc IS NULL)
17.          

CTE xiangcun_address

18. 0.000 0.000 ↓ 0.0

Merge Join (cost=470,618.71..654,175,212.85 rows=75,550,437 width=172) (actual rows= loops=)

  • Merge Cond: (xcid.oid = ad_2.oid)
19. 0.000 0.000 ↓ 0.0

Sort (cost=733.55..755.04 rows=8,595 width=8) (actual rows= loops=)

  • Sort Key: xcid.oid
20. 0.000 0.000 ↓ 0.0

CTE Scan on xiangcun_id xcid (cost=0.00..171.90 rows=8,595 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Materialize (cost=469,885.16..478,675.20 rows=1,758,009 width=140) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Sort (cost=469,885.16..474,280.18 rows=1,758,009 width=140) (actual rows= loops=)

  • Sort Key: ad_2.oid
23. 0.000 0.000 ↓ 0.0

CTE Scan on address_dup ad_2 (cost=0.00..35,160.18 rows=1,758,009 width=140) (actual rows= loops=)

24.          

SubPlan (for Merge Join)

25. 0.000 0.000 ↓ 0.0

Index Scan using poi_shape_oid_pelias on poi_shape a_4 (cost=0.56..8.64 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (oid = xcid.oid)
26.          

CTE street_id

27. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=371,988.14..395,611.39 rows=175,801 width=76) (actual rows= loops=)

  • Group Key: ad_3.owner, ad_3.streetc, ad_3.numberc
28. 0.000 0.000 ↓ 0.0

Sort (cost=371,988.14..376,361.19 rows=1,749,219 width=76) (actual rows= loops=)

  • Sort Key: ad_3.owner, ad_3.streetc, ad_3.numberc
29. 0.000 0.000 ↓ 0.0

CTE Scan on address_dup ad_3 (cost=0.00..35,160.18 rows=1,749,219 width=76) (actual rows= loops=)

  • Filter: (streetc IS NOT NULL)
30.          

CTE street_address

31. 0.000 0.000 ↓ 0.0

Merge Join (cost=491,121.09..13,371,193,405.52 rows=1,545,298,701 width=172) (actual rows= loops=)

  • Merge Cond: (stid.oid = ad_4.oid)
32. 0.000 0.000 ↓ 0.0

Sort (cost=21,235.94..21,675.44 rows=175,801 width=8) (actual rows= loops=)

  • Sort Key: stid.oid
33. 0.000 0.000 ↓ 0.0

CTE Scan on street_id stid (cost=0.00..3,516.02 rows=175,801 width=8) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Materialize (cost=469,885.16..478,675.20 rows=1,758,009 width=140) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Sort (cost=469,885.16..474,280.18 rows=1,758,009 width=140) (actual rows= loops=)

  • Sort Key: ad_4.oid
36. 0.000 0.000 ↓ 0.0

CTE Scan on address_dup ad_4 (cost=0.00..35,160.18 rows=1,758,009 width=140) (actual rows= loops=)

37.          

SubPlan (for Merge Join)

38. 0.000 0.000 ↓ 0.0

Index Scan using poi_shape_oid_pelias on poi_shape a_5 (cost=0.56..8.64 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (oid = stid.oid)
39.          

CTE street_xiangcun_id

40. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=467,650.14..500,019.49 rows=175,801 width=140) (actual rows= loops=)

  • Group Key: ad_5.owner, ad_5.streetc, ad_5.numberc, ad_5.xiangnamec, ad_5.cunnamec
41. 0.000 0.000 ↓ 0.0

Sort (cost=467,650.14..472,023.19 rows=1,749,219 width=140) (actual rows= loops=)

  • Sort Key: ad_5.owner, ad_5.streetc, ad_5.numberc, ad_5.xiangnamec, ad_5.cunnamec
42. 0.000 0.000 ↓ 0.0

CTE Scan on address_dup ad_5 (cost=0.00..35,160.18 rows=1,749,219 width=140) (actual rows= loops=)

  • Filter: (streetc IS NOT NULL)
43.          

CTE addition_id

44. 0.000 0.000 ↓ 0.0

HashSetOp Except All (cost=0.00..11,427.06 rows=175,801 width=12) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Append (cost=0.00..10,548.06 rows=351,602 width=12) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.00..5,274.03 rows=175,801 width=12) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

CTE Scan on street_xiangcun_id (cost=0.00..3,516.02 rows=175,801 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..5,274.03 rows=175,801 width=12) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

CTE Scan on street_id (cost=0.00..3,516.02 rows=175,801 width=8) (actual rows= loops=)

50.          

CTE addition_address

51. 0.000 0.000 ↓ 0.0

Merge Join (cost=491,121.09..13,371,193,405.52 rows=1,545,298,701 width=172) (actual rows= loops=)

  • Merge Cond: (adid.oid = ad_6.oid)
52. 0.000 0.000 ↓ 0.0

Sort (cost=21,235.94..21,675.44 rows=175,801 width=8) (actual rows= loops=)

  • Sort Key: adid.oid
53. 0.000 0.000 ↓ 0.0

CTE Scan on addition_id adid (cost=0.00..3,516.02 rows=175,801 width=8) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Materialize (cost=469,885.16..478,675.20 rows=1,758,009 width=140) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Sort (cost=469,885.16..474,280.18 rows=1,758,009 width=140) (actual rows= loops=)

  • Sort Key: ad_6.oid
56. 0.000 0.000 ↓ 0.0

CTE Scan on address_dup ad_6 (cost=0.00..35,160.18 rows=1,758,009 width=140) (actual rows= loops=)

57.          

SubPlan (for Merge Join)

58. 0.000 0.000 ↓ 0.0

Index Scan using poi_shape_oid_pelias on poi_shape a_6 (cost=0.56..8.64 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (oid = adid.oid)
59.          

CTE addition_address_dedup

60. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,642,692,440.90..194,190,322,246.70 rows=298,493,410,414 width=172) (actual rows= loops=)

  • Merge Cond: ((adad.owner = stad.owner) AND (adad.streetc = stad.streetc) AND (adad.numberc = stad.numberc))
  • Filter: ((stad.oid IS NULL) OR (NOT ((st_point((split_part(adad.dprp, ' '::text, 1))::double precision, (split_part(adad.dprp, ' '::text, 2))::double precision))::geography && _st_expand((st_point((split_part(stad.dprp, ' '::text, 1))::double precision, (split_part(stad.dprp, ' '::text, 2))::double precision))::geography, '1500'::double precision))) OR (NOT ((st_point((split_part(stad.dprp, ' '::text, 1))::double precision, (split_part(stad.dprp, ' '::text, 2))::double precision))::geography && _st_expand((st_point((split_part(adad.dprp, ' '::text, 1))::double precision, (split_part(adad.dprp, ' '::text, 2))::double precision))::geography, '1500'::double precision))) OR (NOT _st_dwithin((st_point((split_part(adad.dprp, ' '::text, 1))::double precision, (split_part(adad.dprp, ' '::text, 2))::double precision))::geography, (st_point((split_part(stad.dprp, ' '::text, 1))::double precision, (split_part(stad.dprp, ' '::text, 2))::double precision))::geography, '1500'::double precision, true)))
61. 0.000 0.000 ↓ 0.0

Sort (cost=926,981,881.70..930,845,128.45 rows=1,545,298,701 width=172) (actual rows= loops=)

  • Sort Key: adad.owner, adad.streetc, adad.numberc
62. 0.000 0.000 ↓ 0.0

CTE Scan on addition_address adad (cost=0.00..30,905,974.02 rows=1,545,298,701 width=172) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Materialize (cost=715,710,559.20..723,437,052.71 rows=1,545,298,701 width=108) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Sort (cost=715,710,559.20..719,573,805.95 rows=1,545,298,701 width=108) (actual rows= loops=)

  • Sort Key: stad.owner, stad.streetc, stad.numberc
65. 0.000 0.000 ↓ 0.0

CTE Scan on street_address stad (cost=0.00..30,905,974.02 rows=1,545,298,701 width=108) (actual rows= loops=)

66.          

CTE venue2address

67. 0.000 0.000 ↓ 0.0

Append (cost=0.00..6,002,285,191.04 rows=300,114,259,552 width=172) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

CTE Scan on street_address (cost=0.00..30,905,974.02 rows=1,545,298,701 width=172) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

CTE Scan on xiangcun_address (cost=0.00..1,511,008.74 rows=75,550,437 width=172) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

CTE Scan on addition_address_dedup (cost=0.00..5,969,868,208.28 rows=298,493,410,414 width=172) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash Join (cost=63,399.48..7,858,533,503.34 rows=3,247,644 width=771) (actual rows= loops=)

  • Hash Cond: (ma.owner = aid.adminid)
72. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on poi_major ma (cost=63,145.81..751,558.36 rows=3,247,644 width=12) (actual rows= loops=)

  • Recheck Cond: (newfeatcode = 5,990,101)
73. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on poi_major_newfeatcode_pelias (cost=0.00..62,333.90 rows=3,247,644 width=0) (actual rows= loops=)

  • Index Cond: (newfeatcode = 5,990,101)
74. 0.000 0.000 ↓ 0.0

Hash (cost=213.30..213.30 rows=3,230 width=219) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Seq Scan on dependence_pelias aid (cost=0.00..213.30 rows=3,230 width=219) (actual rows= loops=)

76.          

SubPlan (for Hash Join)

77. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_7 (cost=0.57..290.38 rows=75 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 876))
78. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_8 (cost=0.57..297.99 rows=77 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 877))
79. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_9 (cost=0.57..297.99 rows=77 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 878))
80. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_10 (cost=0.57..183.76 rows=47 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 879))
81. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_11 (cost=0.57..187.57 rows=48 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 880))
82. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_12 (cost=0.57..187.57 rows=48 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 881))
83. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_13 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 882))
84. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_14 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 883))
85. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_15 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 884))
86. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_16 (cost=0.57..648.37 rows=169 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 922))
87. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_17 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 870))
88. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_18 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 871))
89. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_19 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 872))
90. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_20 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 873))
91. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_21 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 874))
92. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_22 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ma.oid) AND (fid = 875))
93. 0.000 0.000 ↓ 0.0

Index Scan using poi_shape_oid_pelias on poi_shape a_23 (cost=0.56..8.64 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (oid = ma.oid)
94. 0.000 0.000 ↓ 0.0

Hash Join (cost=253.71..528,359,343,013,036.75 rows=300,114,259,552 width=771) (actual rows= loops=)

  • Hash Cond: (ad.owner = aid_1.adminid)
95.          

Initplan (for Hash Join)

96. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=32) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=32) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=32) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

CTE Scan on venue2address ad (cost=0.00..6,002,285,191.04 rows=300,114,259,552 width=172) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Hash (cost=213.30..213.30 rows=3,230 width=219) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Seq Scan on dependence_pelias aid_1 (cost=0.00..213.30 rows=3,230 width=219) (actual rows= loops=)

102.          

SubPlan (for Hash Join)

103. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_24 (cost=0.57..297.99 rows=77 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 877))
104. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_25 (cost=0.57..297.99 rows=77 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 878))
105. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_26 (cost=0.57..187.57 rows=48 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 880))
106. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_27 (cost=0.57..187.57 rows=48 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 881))
107. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_28 (cost=0.57..648.37 rows=169 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 922))
108. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_29 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 871))
109. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_30 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 872))
110. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_31 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 874))
111. 0.000 0.000 ↓ 0.0

Index Scan using poi_minor_pkey on poi_minor a_32 (cost=0.57..35.25 rows=8 width=15) (actual rows= loops=)

  • Index Cond: ((oid = ad.oid) AND (fid = 875))