-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathexplain_analyze_buffers.txt
161 lines (161 loc) · 13 KB
/
explain_analyze_buffers.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
Append (cost=1044052.29..5308738.66 rows=13885003 width=32) (actual time=0.001..4746.672 rows=48343 loops=1)
Buffers: shared hit=572799 read=1504
CTE params
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)
Output: '27.6164960861206'::double precision, '53.853792295637'::double precision, '27.6719856262207'::double precision, '53.8864592938131'::double precision
CTE direct_nodes
-> CTE Scan on params (cost=0.00..5.27 rows=1000 width=32) (actual time=333.908..372.562 rows=37274 loops=1)
Output: get_nodes_by_box(params.minlon, params.minlat, params.maxlon, params.maxlat)
Buffers: shared hit=231144
CTE all_request_ways
-> Unique (cost=9126.68..9135.21 rows=1706 width=8) (actual time=500.243..515.542 rows=7725 loops=1)
Output: c.way_id
Buffers: shared hit=385718
-> Sort (cost=9126.68..9130.95 rows=1706 width=8) (actual time=500.243..508.098 rows=48344 loops=1)
Output: c.way_id
Sort Key: c.way_id
Sort Method: quicksort Memory: 3803kB
Buffers: shared hit=385718
-> Nested Loop (cost=0.42..9035.10 rows=1706 width=8) (actual time=333.924..487.237 rows=48344 loops=1)
Output: c.way_id
Buffers: shared hit=385718
-> CTE Scan on direct_nodes n_1 (cost=0.00..20.00 rows=1000 width=32) (actual time=333.909..385.562 rows=37274 loops=1)
Output: n_1.node
Buffers: shared hit=231144
-> Index Scan using current_way_nodes_node_idx on public.current_way_nodes c (cost=0.42..9.00 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=37274)
Output: c.way_id, c.node_id, c.sequence_id
Index Cond: (c.node_id = (n_1.node).id)
Buffers: shared hit=154574
CTE all_request_nodes
-> Append (cost=27510.27..180920.27 rows=10227000 width=32) (actual time=1426.841..1451.311 rows=40472 loops=1)
Buffers: shared hit=436888 read=1504
-> Subquery Scan on "*SELECT* 1" (cost=27510.27..180900.27 rows=10226000 width=32) (actual time=1426.841..1430.122 rows=3198 loops=1)
Output: "*SELECT* 1".node
Buffers: shared hit=436888 read=1504
-> Unique (cost=27510.27..78640.27 rows=10226000 width=40) (actual time=1426.840..1429.456 rows=3198 loops=1)
Output: (get_node_by_id(c_1.node_id)), c_1.node_id
Buffers: shared hit=436888 read=1504
-> Sort (cost=27510.27..27535.84 rows=10226 width=40) (actual time=1426.840..1428.171 rows=3596 loops=1)
Output: (get_node_by_id(c_1.node_id)), c_1.node_id
Sort Key: c_1.node_id
Sort Method: quicksort Memory: 785kB
Buffers: shared hit=436888 read=1504
-> Hash Join (cost=21959.06..26829.22 rows=10226 width=40) (actual time=1170.925..1422.265 rows=3596 loops=1)
Output: get_node_by_id(c_1.node_id), c_1.node_id
Hash Cond: (w_1.id = c_1.way_id)
Buffers: shared hit=436888 read=1504
-> CTE Scan on all_request_ways w_1 (cost=0.00..34.12 rows=1706 width=8) (actual time=500.246..517.569 rows=7725 loops=1)
Output: w_1.id
Buffers: shared hit=385718
-> Hash (cost=16501.21..16501.21 rows=436628 width=16) (actual time=667.646..667.646 rows=824913 loops=1)
Output: c_1.node_id, c_1.way_id
Buckets: 1048576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 46860kB
Buffers: shared hit=4059 read=1504
-> Seq Scan on public.current_way_nodes c_1 (cost=22.50..16501.21 rows=436628 width=16) (actual time=30.028..439.532 rows=824913 loops=1)
Output: c_1.node_id, c_1.way_id
Filter: (NOT (hashed SubPlan 4))
Rows Removed by Filter: 48344
Buffers: shared hit=4059 read=1504
SubPlan 4
-> CTE Scan on direct_nodes n_2 (cost=0.00..20.00 rows=1000 width=8) (actual time=0.003..13.127 rows=37274 loops=1)
Output: (n_2.node).id
-> CTE Scan on direct_nodes n_3 (cost=0.00..20.00 rows=1000 width=32) (actual time=0.001..11.971 rows=37274 loops=1)
Output: n_3.node
CTE relations_from_ways_and_nodes
-> Unique (cost=853026.08..853452.01 rows=1901 width=8) (actual time=46.325..46.524 rows=139 loops=1)
Output: m.relation_id
Buffers: shared hit=325
-> Sort (cost=853026.08..853239.04 rows=85186 width=8) (actual time=46.324..46.439 rows=1219 loops=1)
Output: m.relation_id
Sort Key: m.relation_id
Sort Method: quicksort Memory: 106kB
Buffers: shared hit=325
-> Hash Join (cost=1329.95..846050.05 rows=85186 width=8) (actual time=18.826..46.018 rows=1219 loops=1)
Output: m.relation_id
Hash Cond: ((all_request_ways.id = m.member_id) AND (('Way'::nwr_enum) = m.member_type))
Buffers: shared hit=325
-> Append (cost=0.00..204574.12 rows=10228706 width=12) (actual time=0.001..14.299 rows=48197 loops=1)
-> CTE Scan on all_request_ways (cost=0.00..34.12 rows=1706 width=12) (actual time=0.001..1.396 rows=7725 loops=1)
Output: all_request_ways.id, 'Way'::nwr_enum
-> CTE Scan on all_request_nodes n_4 (cost=0.00..204540.00 rows=10227000 width=12) (actual time=0.004..9.183 rows=40472 loops=1)
Output: (n_4.node).id, 'Node'::nwr_enum
-> Hash (cost=725.18..725.18 rows=40318 width=20) (actual time=18.611..18.611 rows=40318 loops=1)
Output: m.relation_id, m.member_id, m.member_type
Buckets: 65536 Batches: 1 Memory Usage: 2560kB
Buffers: shared hit=322
-> Seq Scan on public.current_relation_members m (cost=0.00..725.18 rows=40318 width=20) (actual time=0.007..7.793 rows=40318 loops=1)
Output: m.relation_id, m.member_id, m.member_type
Buffers: shared hit=322
CTE all_request_relations
-> HashAggregate (cost=520.00..539.52 rows=1952 width=8) (actual time=46.926..46.942 rows=143 loops=1)
Output: r_1.id
Group Key: r_1.id
Buffers: shared hit=346
-> Append (cost=0.00..515.12 rows=1952 width=8) (actual time=46.327..46.882 rows=191 loops=1)
Buffers: shared hit=346
-> CTE Scan on relations_from_ways_and_nodes r_1 (cost=0.00..38.02 rows=1901 width=8) (actual time=46.326..46.541 rows=139 loops=1)
Output: r_1.id
Buffers: shared hit=325
-> Hash Join (cost=359.64..457.58 rows=51 width=8) (actual time=0.296..0.335 rows=52 loops=1)
Output: rm.relation_id
Hash Cond: (r2.id = rm.member_id)
Buffers: shared hit=21
-> CTE Scan on relations_from_ways_and_nodes r2 (cost=0.00..38.02 rows=1901 width=8) (actual time=0.000..0.005 rows=139 loops=1)
Output: r2.id
-> Hash (cost=351.49..351.49 rows=652 width=16) (actual time=0.274..0.274 rows=649 loops=1)
Output: rm.relation_id, rm.member_id
Buckets: 1024 Batches: 1 Memory Usage: 39kB
Buffers: shared hit=21
-> Bitmap Heap Scan on public.current_relation_members rm (cost=21.34..351.49 rows=652 width=16) (actual time=0.060..0.188 rows=649 loops=1)
Output: rm.relation_id, rm.member_id
Recheck Cond: (rm.member_type = 'Relation'::nwr_enum)
Heap Blocks: exact=16
Buffers: shared hit=21
-> Bitmap Index Scan on current_relation_members_member_idx (cost=0.00..21.18 rows=652 width=0) (actual time=0.054..0.054 rows=649 loops=1)
Index Cond: (rm.member_type = 'Relation'::nwr_enum)
Buffers: shared hit=5
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)
Output: '<?xml version="1.0" encoding="UTF-8"?><osm version="0.6" generator="FastMAP" copyright="OpenStreetMap and contributors" attribution="http://www.openstreetmap.org/copyright" license="http://opendatacommons.org/licenses/odbl/1-0/">'::text
-> CTE Scan on params p (cost=0.00..0.02 rows=1 width=32) (actual time=0.092..0.093 rows=1 loops=1)
Output: (XMLELEMENT(NAME bounds, XMLATTRIBUTES(p.minlat AS minlat, p.minlon AS minlon, p.maxlat AS maxlat, p.maxlon AS maxlon)))::text
-> Subquery Scan on nodes (cost=4079850.96..4207688.46 rows=10227000 width=32) (actual time=2761.825..2771.231 rows=40472 loops=1)
Output: (nodes.line)::text
Buffers: shared hit=436894 read=1504
-> Sort (cost=4079850.96..4105418.46 rows=10227000 width=40) (actual time=2761.824..2765.697 rows=40472 loops=1)
Output: (XMLELEMENT(NAME node, XMLATTRIBUTES((n.node).id AS id, (n.node).visible AS visible, (n.node).version AS version, (n.node).changeset AS changeset, to_char((n.node)."timestamp", 'YYYY-MM-DD"T"HH24:MI:SS"Z"'::text) AS "timestamp", (n.node)."user" AS "user", (n.node).uid AS uid, ((n.node).lat)::numeric(10,7) AS lat, ((n.node).lon)::numeric(10,7) AS lon), ((n.node).tags)::xml)), ((n.node).id)
Sort Key: ((n.node).id)
Sort Method: quicksort Memory: 12698kB
Buffers: shared hit=436894 read=1504
-> CTE Scan on all_request_nodes n (cost=0.00..2889127.50 rows=10227000 width=40) (actual time=1427.179..2733.890 rows=40472 loops=1)
Output: XMLELEMENT(NAME node, XMLATTRIBUTES((n.node).id AS id, (n.node).visible AS visible, (n.node).version AS version, (n.node).changeset AS changeset, to_char((n.node)."timestamp", 'YYYY-MM-DD"T"HH24:MI:SS"Z"'::text) AS "timestamp", (n.node)."user" AS "user", (n.node).uid AS uid, ((n.node).lat)::numeric(10,7) AS lat, ((n.node).lon)::numeric(10,7) AS lon), ((n.node).tags)::xml), (n.node).id
Buffers: shared hit=436891 read=1504
-> Subquery Scan on ways (cost=125.70..26581.50 rows=1706000 width=32) (actual time=3.067..1873.317 rows=7725 loops=1)
Output: (ways.line)::text
Buffers: shared hit=132284
-> Result (cost=125.70..9521.50 rows=1706000 width=40) (actual time=3.067..1869.148 rows=7725 loops=1)
Output: (get_way_by_id(w.id))::xml, w.id
Buffers: shared hit=132284
-> Sort (cost=125.70..129.97 rows=1706 width=8) (actual time=1.251..5.399 rows=7725 loops=1)
Output: w.id
Sort Key: w.id
Sort Method: quicksort Memory: 555kB
-> CTE Scan on all_request_ways w (cost=0.00..34.12 rows=1706 width=8) (actual time=0.006..0.648 rows=7725 loops=1)
Output: w.id
-> Subquery Scan on relations (cost=145.72..30416.36 rows=1952000 width=32) (actual time=49.082..96.838 rows=143 loops=1)
Output: (relations.line)::text
Buffers: shared hit=3621
-> Result (cost=145.72..10896.36 rows=1952000 width=40) (actual time=49.080..96.784 rows=143 loops=1)
Output: (get_relation_by_id(r.id))::xml, r.id
Buffers: shared hit=3621
-> Sort (cost=145.72..150.60 rows=1952 width=8) (actual time=46.998..47.044 rows=143 loops=1)
Output: r.id
Sort Key: r.id
Sort Method: quicksort Memory: 31kB
Buffers: shared hit=346
-> CTE Scan on all_request_relations r (cost=0.00..39.04 rows=1952 width=8) (actual time=46.928..46.971 rows=143 loops=1)
Output: r.id
Buffers: shared hit=346
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)
Output: '</osm>'::text
Planning time: 2.170 ms
Execution time: 4753.492 ms