Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
2.0.0.0-incubating
-
None
Description
It fails to retrieve data from view defined using CTE when filter is used. Here are the steps to reproduce:
Step 1: prepare schema and data by running attached world.sql
psql -a -d postgres -f world.sql > world.out 2>&1
Step 2: create view defined using CTE by running attached
create view view_with_shared_scans as
(
with longlivingregions as
(
select FOO.*,count(distinct language) as "lang_count"
from(
select
sum(population) as "REGION_POP",
sum(gnp) as "REGION_GNP",
avg(lifeexpectancy) as "REGION_LIFETIME",region
from
country
group by region
) FOO,countrylanguage,country
where
country.code = countrylanguage.countrycode
and FOO.region = country.region
group by
FOO.region,foo."REGION_POP",foo."REGION_GNP",foo."REGION_LIFETIME"),
denseregions as
(
select FOO.*,count(distinct language) as "lang_count",
sum(surfacearea) as "REGION_SURFACE_AREA"
from(
select
sum(population) as "REGION_POP",
sum(gnp) as "REGION_GNP",
region
from
country
group by region
) FOO,countrylanguage,country
where
country.code = countrylanguage.countrycode
and FOO.region = country.region
and FOO."REGION_POP" != 0
group by
FOO.region,foo."REGION_POP",foo."REGION_GNP"
order by sum(surfacearea)/foo."REGION_POP" desc),
allcountrystats as
( select country.code,country.name,count(distinct city.id) CITY_CNT,
count(distinct countrylanguage.language) LANG_CNT
from country,city,countrylanguage
where country.code = city.countrycode
and country.code = countrylanguage.countrycode
group by country.code,country.name
)
select allcountrystats.CITY_CNT,allcountrystats.LANG_CNT,allcountrystats.name,
"REGION_SURFACE_AREA","REGION_LIFETIME",longlivingregions."REGION_POP",longlivingregions.lang_count,longlivingregions."REGION_GNP",longlivingregions.region
from longlivingregions,denseregions,allcountrystats,country
where longlivingregions.region = denseregions.region and allcountrystats.code = country.code and country.region = longlivingregions.region
and country.indepyear between 1800 and 1850
UNION ALL
select allcountrystats.CITY_CNT,allcountrystats.LANG_CNT,allcountrystats.name,
"REGION_SURFACE_AREA","REGION_LIFETIME",longlivingregions."REGION_POP",longlivingregions.lang_count,longlivingregions."REGION_GNP",longlivingregions.region
from longlivingregions,denseregions,allcountrystats,country
where longlivingregions.region = denseregions.region and allcountrystats.code = country.code and country.region = longlivingregions.region
and country.indepyear between 1850 and 1900
UNION ALL
select allcountrystats.CITY_CNT,allcountrystats.LANG_CNT,allcountrystats.name,
"REGION_SURFACE_AREA","REGION_LIFETIME",longlivingregions."REGION_POP",longlivingregions.lang_count,longlivingregions."REGION_GNP",longlivingregions.region
from longlivingregions,denseregions,allcountrystats,country
where longlivingregions.region = denseregions.region and allcountrystats.code = country.code and country.region = longlivingregions.region
and country.indepyear > 1900
);
Step 3: retrieve all data from the view succeed
select * from view_with_shared_scans;
city_cnt | lang_cnt | name | REGION_SURFACE_AREA | REGION_LIFETIME | REGION_POP | lang_count | REGION_GNP | region
----------+----------+---------------------------------------+---------------------+------------------+------------+------------+------------+---------------------------
49 | 12 | Canada | 2.36342e+08 | 75.8199996948242 | 309632000 | 18 | 9111890.00 | North America
58 | 8 | Italy | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
29 | 6 | Romania | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
5 | 2 | United Arab Emirates | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
1 | 2 | Bahrain | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
14 | 3 | Israel | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
2 | 3 | Lebanon | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
5 | 2 | Oman | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
4 | 4 | Azerbaijan | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
15 | 5 | Iraq | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
3 | 2 | Kuwait | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
1 | 2 | Qatar | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
11 | 2 | Syria | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
3 | 2 | Armenia | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
2 | 2 | Cyprus | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
5 | 6 | Georgia | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
24 | 1 | Saudi Arabia | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
6 | 2 | Yemen | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
5 | 3 | Jordan | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
62 | 3 | Turkey | 1.01537e+07 | 70.5666671329074 | 188380700 | 21 | 677260.00 | Middle East
1 | 4 | Costa Rica | 1.40524e+07 | 71.0249996185303 | 135221000 | 23 | 473151.00 | Central America
3 | 4 | Honduras | 1.40524e+07 | 71.0249996185303 | 135221000 | 23 | 473151.00 | Central America
4 | 5 | Guatemala | 1.40524e+07 | 71.0249996185303 | 135221000 | 23 | 473151.00 | Central America
4 | 4 | Nicaragua | 1.40524e+07 | 71.0249996185303 | 135221000 | 23 | 473151.00 | Central America
173 | 6 | Mexico | 1.40524e+07 | 71.0249996185303 | 135221000 | 23 | 473151.00 | Central America
7 | 2 | El Salvador | 1.40524e+07 | 71.0249996185303 | 135221000 | 23 | 473151.00 | Central America
2 | 4 | Monaco | 6.64977e+06 | 78.2555567423503 | 183247600 | 21 | 4673272.00 | Western Europe
5 | 5 | Norway | 6.64519e+06 | 78.3333333333333 | 24166400 | 15 | 676655.00 | Nordic Countries
2 | 4 | Belize | 1.40524e+07 | 71.0249996185303 | 135221000 | 23 | 473151.00 | Central America
2 | 5 | Estonia | 940174 | 69 | 7561900 | 8 | 22418.00 | Baltic Countries
1 | 2 | Iceland | 6.64519e+06 | 78.3333333333333 | 24166400 | 15 | 676655.00 | Nordic Countries
7 | 5 | Finland | 6.64519e+06 | 78.3333333333333 | 24166400 | 15 | 676655.00 | Nordic Countries
5 | 5 | Lithuania | 940174 | 69 | 7561900 | 8 | 22418.00 | Baltic Countries
2 | 6 | Panama | 1.40524e+07 | 71.0249996185303 | 135221000 | 23 | 473151.00 | Central America
3 | 6 | Latvia | 940174 | 69 | 7561900 | 8 | 22418.00 | Baltic Countries
1 | 8 | Liberia | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
37 | 2 | Egypt | 3.69935e+07 | 65.3857127598354 | 173266000 | 14 | 243870.00 | Northern Africa
1 | 6 | Guinea-Bissau | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
85 | 9 | Indonesia | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
1 | 2 | Maldives | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
2 | 6 | Mauritania | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
1 | 4 | Palau | 13496 | 68.0857140677316 | 543000 | 18 | 1848.70 | Micronesia
22 | 9 | Vietnam | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
1 | 4 | Brunei | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
2 | 6 | Micronesia, Federated States of | 13496 | 68.0857140677316 | 543000 | 18 | 1848.70 | Micronesia
5 | 6 | Ghana | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
2 | 5 | Gambia | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
21 | 6 | Kazakstan | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
22 | 2 | Morocco | 3.69935e+07 | 65.3857127598354 | 173266000 | 14 | 243870.00 | Northern Africa
1 | 6 | Mali | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
64 | 10 | Nigeria | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
9 | 2 | New Zealand | 6.24712e+07 | 78.8000030517578 | 22753100 | 11 | 405851.00 | Australia and New Zealand
1 | 3 | Bhutan | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
1 | 7 | Guinea | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
2 | 7 | Kyrgyzstan | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
3 | 4 | Cambodia | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
2 | 2 | Kiribati | 13496 | 68.0857140677316 | 543000 | 18 | 1848.70 | Micronesia
2 | 4 | Laos | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
7 | 3 | Sri Lanka | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
59 | 8 | Pakistan | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
4 | 5 | Afghanistan | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
5 | 5 | Cote deIvoire | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
16 | 8 | Myanmar | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
136 | 10 | Philippines | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
9 | 6 | Senegal | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
1 | 3 | Singapore | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
1 | 8 | Sierra Leone | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
1 | 8 | Togo | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
4 | 4 | Turkmenistan | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
14 | 8 | Australia | 6.24712e+07 | 78.8000030517578 | 22753100 | 11 | 405851.00 | Australia and New Zealand
3 | 6 | Burkina Faso | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
24 | 7 | Bangladesh | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
18 | 2 | Algeria | 3.69935e+07 | 65.3857127598354 | 173266000 | 14 | 243870.00 | Northern Africa
341 | 12 | India | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
1 | 2 | Marshall Islands | 13496 | 68.0857140677316 | 543000 | 18 | 1848.70 | Micronesia
3 | 5 | Niger | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
12 | 10 | Sudan | 3.69935e+07 | 65.3857127598354 | 173266000 | 14 | 243870.00 | Northern Africa
2 | 3 | Tajikistan | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
8 | 3 | Tunisia | 3.69935e+07 | 65.3857127598354 | 173266000 | 14 | 243870.00 | Northern Africa
4 | 7 | Benin | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
1 | 2 | Cape Verde | 3.97449e+07 | 52.7411768296186 | 221672000 | 65 | 106711.00 | Western Africa
67 | 10 | Iran | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
4 | 2 | Libyan Arab Jamahiriya | 3.69935e+07 | 65.3857127598354 | 173266000 | 14 | 243870.00 | Northern Africa
18 | 6 | Malaysia | 3.53222e+07 | 64.4000001387163 | 518541000 | 47 | 642643.00 | Southeast Asia
2 | 5 | Nauru | 13496 | 68.0857140677316 | 543000 | 18 | 1848.70 | Micronesia
17 | 6 | Uzbekistan | 9.07498e+07 | 61.3500003814697 | 1490776000 | 54 | 810604.00 | Southern and Central Asia
2 | 2 | Ireland | 869246 | 77.25 | 63398500 | 4 | 1454251.00 | British Islands
8 | 2 | Greece | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
2 | 3 | Liechtenstein | 6.64977e+06 | 78.2555567423503 | 183247600 | 21 | 4673272.00 | Western Europe
9 | 6 | Belgium | 6.64977e+06 | 78.2555567423503 | 183247600 | 21 | 4673272.00 | Western Europe
1 | 3 | Albania | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
10 | 4 | Bulgaria | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
1 | 3 | Djibouti | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
3 | 2 | Somalia | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
10 | 11 | Tanzania | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
1 | 1 | Holy See (Vatican City State) | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
44 | 11 | South Africa | 2.30602e+07 | 44.8199996948242 | 46886000 | 21 | 126931.00 | Southern Africa
16 | 4 | Belarus | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
93 | 6 | Germany | 6.64977e+06 | 78.2555567423503 | 183247600 | 21 | 4673272.00 | Western Europe
4 | 2 | Croatia | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
8 | 10 | Kenya | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
12 | 10 | Mozambique | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
1 | 2 | Rwanda | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
3 | 5 | Slovakia | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
57 | 7 | Ukraine | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
6 | 4 | Zimbabwe | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
10 | 8 | Czech Republic | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
1 | 6 | Eritrea | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
70 | 2 | South Korea | 1.27203e+08 | 75.25 | 1507328000 | 32 | 5524885.00 | Eastern Asia
1 | 3 | Lesotho | 2.30602e+07 | 44.8199996948242 | 46886000 | 21 | 126931.00 | Southern Africa
6 | 8 | Austria | 6.64977e+06 | 78.2555567423503 | 183247600 | 21 | 4673272.00 | Western Europe
2 | 5 | Botswana | 2.30602e+07 | 44.8199996948242 | 46886000 | 21 | 126931.00 | Southern Africa
4 | 5 | Moldova | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
5 | 2 | Madagascar | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
1 | 5 | Macedonia | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
1 | 6 | Mongolia | 1.27203e+08 | 75.25 | 1507328000 | 32 | 5524885.00 | Eastern Asia
1 | 8 | Namibia | 2.30602e+07 | 44.8199996948242 | 46886000 | 21 | 126931.00 | Southern Africa
189 | 12 | Russian Federation | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
1 | 2 | Swaziland | 2.30602e+07 | 44.8199996948242 | 46886000 | 21 | 126931.00 | Southern Africa
44 | 4 | Poland | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
13 | 2 | North Korea | 1.27203e+08 | 75.25 | 1507328000 | 32 | 5524885.00 | Eastern Asia
1 | 3 | Seychelles | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
1 | 2 | Tonga | 23933 | 70.7333335876465 | 633050 | 15 | 1545.00 | Polynesia
1 | 3 | Tuvalu | 23933 | 70.7333335876465 | 633050 | 15 | 1545.00 | Polynesia
42 | 6 | Taiwan | 1.27203e+08 | 75.25 | 1507328000 | 32 | 5524885.00 | Eastern Asia
8 | 6 | Yugoslavia | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
7 | 6 | Zambia | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
1 | 3 | Burundi | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
3 | 1 | Bosnia and Herzegovina | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
1 | 5 | Comoros | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
9 | 6 | Hungary | 2.14732e+08 | 69.9299995422363 | 307026000 | 28 | 659980.00 | Eastern Europe
2 | 2 | Malta | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
3 | 6 | Mauritius | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
2 | 4 | Malawi | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
2 | 3 | Slovenia | 5.8452e+06 | 76.5285720825195 | 144674200 | 22 | 2012289.00 | Southern Europe
1 | 10 | Uganda | 4.36313e+07 | 50.8105261953254 | 246999000 | 80 | 69925.00 | Eastern Africa
1 | 3 | Samoa | 23933 | 70.7333335876465 | 633050 | 15 | 1545.00 | Polynesia
6 | 2 | Dominican Republic | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
4 | 2 | Haiti | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
57 | 3 | Argentina | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
38 | 5 | Colombia | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
1 | 1 | Uruguay | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
8 | 4 | Bolivia | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
29 | 4 | Chile | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
15 | 2 | Ecuador | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
22 | 3 | Peru | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
5 | 4 | Paraguay | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
250 | 5 | Brazil | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
41 | 3 | Venezuela | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
1 | 5 | Luxembourg | 6.64977e+06 | 78.2555567423503 | 183247600 | 21 | 4673272.00 | Western Europe
1 | 2 | Bahamas | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
1 | 3 | Vanuatu | 1.14121e+06 | 67.1400009155273 | 6472000 | 8 | 10530.00 | Melanesia
1 | 2 | Barbados | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
18 | 10 | Congo, The Democratic Republic of the | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
1 | 2 | Saint Lucia | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
1 | 6 | Central African Republic | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
7 | 8 | Cameroon | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
14 | 1 | Cuba | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
1 | 4 | Gabon | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
3 | 2 | Jamaica | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
1 | 2 | Saint Kitts and Nevis | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
1 | 2 | Suriname | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
1 | 2 | Antigua and Barbuda | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
1 | 2 | Dominica | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
1 | 2 | Equatorial Guinea | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
1 | 2 | Fiji Islands | 1.14121e+06 | 67.1400009155273 | 6472000 | 8 | 10530.00 | Melanesia
1 | 1 | Grenada | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
1 | 2 | Papua New Guinea | 1.14121e+06 | 67.1400009155273 | 6472000 | 8 | 10530.00 | Melanesia
1 | 2 | Sao Tome and Principe | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
2 | 3 | Trinidad and Tobago | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
5 | 9 | Angola | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
2 | 6 | Congo | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
1 | 3 | Guyana | 7.43182e+07 | 70.9461532005897 | 345780000 | 21 | 1511874.00 | South America
1 | 3 | Solomon Islands | 1.14121e+06 | 67.1400009155273 | 6472000 | 8 | 10530.00 | Melanesia
2 | 8 | Chad | 5.5663e+07 | 50.3111110263401 | 95652000 | 47 | 32938.00 | Central Africa
1 | 2 | Saint Vincent and the Grenadines | 363261 | 73.0583332379659 | 38140000 | 10 | 103586.20 | Caribbean
(176 rows)
Step 4: retrieve data from the view with filter fail when gp_cte_sharing = on by running attached cte_query.cte_share_on.sql, see cte_query.cte_share_on.out for details
select city_cnt,lang_cnt,name,"REGION_POP","REGION_GNP",region from view_with_shared_scans where region = 'Eastern Europe'; city_cnt | lang_cnt | name | REGION_POP | REGION_GNP | region ----------+----------+------+------------+------------+-------- (0 rows)
Step 5: retrieve data from the view with filter succeed when gp_cte_sharing = off by running attached cte_query.cte_share_off.sql, see cte_query.cte_share_off.out for details
select city_cnt,lang_cnt,name,"REGION_POP","REGION_GNP",region from view_with_shared_scans where region = 'Eastern Europe';
city_cnt | lang_cnt | name | REGION_POP | REGION_GNP | region
----------+----------+--------------------+------------+------------+----------------
10 | 8 | Czech Republic | 307026000 | 659980.00 | Eastern Europe
10 | 4 | Bulgaria | 307026000 | 659980.00 | Eastern Europe
4 | 5 | Moldova | 307026000 | 659980.00 | Eastern Europe
189 | 12 | Russian Federation | 307026000 | 659980.00 | Eastern Europe
9 | 6 | Hungary | 307026000 | 659980.00 | Eastern Europe
44 | 4 | Poland | 307026000 | 659980.00 | Eastern Europe
29 | 6 | Romania | 307026000 | 659980.00 | Eastern Europe
16 | 4 | Belarus | 307026000 | 659980.00 | Eastern Europe
3 | 5 | Slovakia | 307026000 | 659980.00 | Eastern Europe
57 | 7 | Ukraine | 307026000 | 659980.00 | Eastern Europe
(10 rows)
Step 6: plan for retrieving data from the view with filter when gp_cte_sharing = on
explain analyze select city_cnt,lang_cnt,name,"REGION_POP","REGION_GNP",region from view_with_shared_scans where region = 'Eastern Europe';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 6:1 (slice21; segments: 6) (cost=2139.96..6428.82 rows=24 width=120)
Rows out: Avg 0.0 rows x 0 workers at destination. Max/Last(seg-1:rhuo-mbp/seg-1:rhuo-mbp) 0/0 rows with 248/248 ms to end.
-> Subquery Scan view_with_shared_scans (cost=2139.96..6428.82 rows=4 width=120)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 0/0 rows with 238/238 ms to end.
-> Append (cost=2139.96..6428.58 rows=4 width=141)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 0/0 rows with 238/238 ms to end.
-> Nested Loop (cost=2139.96..2142.98 rows=2 width=140)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 0/0 rows with 210/210 ms to end.
-> Nested Loop (cost=1979.12..1981.18 rows=2 width=98)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 0/0 rows with 43/43 ms to end.
-> Hash Join (cost=1814.94..1816.06 rows=2 width=62)
Hash Cond: allcountrystats.code = public.country.code
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 0/0 rows with 24/24 ms to end.
-> Shared Scan (share slice:id 21:2) (cost=1808.15..1808.59 rows=40 width=64)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Materialize (cost=1805.76..1808.15 rows=40 width=64)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 12/12 ms to end.
-> GroupAggregate (cost=1592.01..1805.52 rows=40 width=64)
Group By: public.country.code, public.country.name
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 12/12 ms to end.
-> Sort (cost=1592.01..1634.00 rows=2799 width=27)
Sort Key: public.country.code, public.country.name
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 12/12 ms to end.
Executor memory: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp).
Work_mem used: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Hash Join (cost=64.96..413.45 rows=2799 width=27)
Hash Cond: public.city.countrycode = public.country.code
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 11/11 ms to end.
-> Redistribute Motion 6:6 (slice1; segments: 6) (cost=0.00..128.37 rows=680 width=8)
Hash Key: public.city.countrycode
Rows out: Avg 0.0 rows x 0 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Append-only Scan on city (cost=0.00..46.79 rows=680 width=8)
Rows out: Avg 679.8 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 696/652 rows with 19/125 ms to end, start offset by 144/13 ms.
-> Hash (cost=52.66..52.66 rows=164 width=27)
Rows in: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 11/11 ms to end.
-> Hash Join (cost=7.38..52.66 rows=164 width=27)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 11/11 ms to end.
Executor memory: 1041K bytes avg, 1041K bytes max (seg5:rhuo-mbp).
Work_mem used: 2K bytes avg, 2K bytes max (seg4:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg4) Hash chain length 1.0 avg, 1 max, using 45 of 65539 buckets.
-> Redistribute Motion 6:6 (slice2; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
Rows out: Avg 0.0 rows x 0 workers at destination. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 0/0 rows with 0.002/0.002 ms to end.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 171/150 rows with 20/22 ms to end, start offset by 24/52 ms.
-> Hash (cost=4.39..4.39 rows=40 width=15)
Rows in: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 45/42 rows with 9.125/10 ms to end, start offset by 239/241 ms.
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=15)
Rows out: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 45/42 rows with 8.977/10 ms to first row, 9.055/10 ms to end, start offset by 239/241 ms.
-> Hash (cost=6.78..6.78 rows=1 width=18)
Rows in: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 0/0 rows with 24/24 ms to end.
-> Append-only Scan on country (cost=0.00..6.78 rows=1 width=18)
Filter: indepyear >= 1800 AND indepyear <= 1850 AND 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 0/0 rows with 24/24 ms to end.
-> Materialize (cost=164.17..164.23 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 19/19 ms to end, start offset by 179/179 ms.
-> Broadcast Motion 6:6 (slice9; segments: 6) (cost=163.87..164.17 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 18/18 ms to end, start offset by 179/179 ms.
-> Subquery Scan denseregions (cost=163.87..164.10 rows=1 width=36)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 1/0 rows with 40/57 ms to end, start offset by 157/140 ms.
-> Shared Scan (share slice:id 9:1) (cost=163.87..164.10 rows=5 width=92)
Rows out: Avg 3.8 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg3:rhuo-mbp) 7/2 rows with 36/57 ms to end, start offset by 161/140 ms.
-> Sort (cost=163.81..163.87 rows=5 width=92)
Sort Key: "?column6?"
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 0/0 rows with 54/54 ms to end.
Executor memory: 91K bytes avg, 129K bytes max (seg1:rhuo-mbp).
Work_mem used: 91K bytes avg, 129K bytes max (seg1:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> GroupAggregate (cost=161.98..163.23 rows=5 width=92)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 3.8 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg3:rhuo-mbp) 7/2 rows with 33/53 ms to end, start offset by 161/140 ms.
-> Sort (cost=161.98..162.04 rows=5 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 20.2 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg3:rhuo-mbp) 38/11 rows with 33/53 ms to end, start offset by 161/140 ms.
Executor memory: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp).
Work_mem used: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Redistribute Motion 6:6 (slice8; segments: 6) (cost=143.24..161.40 rows=5 width=84)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 20.2 rows x 6 workers at destination. Max/Last(seg1:rhuo-mbp/seg3:rhuo-mbp) 38/11 rows with 31/52 ms to first row, 32/53 ms to end, start offset by 161/140 ms.
-> GroupAggregate (cost=143.24..160.90 rows=5 width=84)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 20.2 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 23/23 rows with 137/137 ms to first row, 139/139 ms to end, start offset by 54/54 ms.
Executor memory: 647K bytes avg, 738K bytes max (seg1:rhuo-mbp).
Work_mem used: 33K bytes avg, 33K bytes max (seg5:rhuo-mbp).
-> Sort (cost=143.24..145.70 rows=164 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 163.8 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 227/227 rows with 137/137 ms to end, start offset by 54/54 ms.
Executor memory: 113K bytes avg, 145K bytes max (seg4:rhuo-mbp).
Work_mem used: 113K bytes avg, 145K bytes max (seg4:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Redistribute Motion 6:6 (slice7; segments: 6) (cost=29.37..94.33 rows=164 width=84)
Hash Key: public.countrylanguage.language
Rows out: Avg 163.8 rows x 6 workers at destination. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 227/227 rows with 136/136 ms to end, start offset by 54/54 ms.
-> Hash Join (cost=29.37..74.65 rows=164 width=84)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 163.8 rows x 6 workers. Max/Last(seg2:rhuo-mbp/seg4:rhuo-mbp) 178/177 rows with 119/157 ms to first row, 123/161 ms to end, start offset by 66/29 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 3K bytes avg, 4K bytes max (seg4:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg2) Hash chain length 1.0 avg, 1 max, using 42 of 32779 buckets.
(seg4) Hash chain length 1.0 avg, 1 max, using 43 of 32779 buckets.
-> Redistribute Motion 6:6 (slice3; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
Rows out: Avg 164.0 rows x 6 workers at destination. Max/Last(seg4:rhuo-mbp/seg0:rhuo-mbp) 178/143 rows with 59/60 ms to first row, 63/63 ms to end, start offset by 126/126 ms.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 171/150 rows with 6.798/9.929 ms to first row, 6.884/10 ms to end, start offset by 182/176 ms.
-> Hash (cost=26.38..26.38 rows=40 width=80)
Rows in: Avg 38.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg4:rhuo-mbp) 43/43 rows with 97/97 ms to end, start offset by 29/29 ms.
-> Redistribute Motion 6:6 (slice6; segments: 6) (cost=8.84..26.38 rows=40 width=80)
Hash Key: public.country.code
Rows out: Avg 38.8 rows x 6 workers at destination. Max/Last(seg4:rhuo-mbp/seg4:rhuo-mbp) 43/43 rows with 97/97 ms to end, start offset by 29/29 ms.
-> Hash Join (cost=8.84..21.60 rows=40 width=80)
Hash Cond: public.country.region = foo.region
Rows out: Avg 38.8 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 77/77 rows with 93/93 ms to end, start offset by 33/33 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg5) Hash chain length 1.0 avg, 1 max, using 7 of 32779 buckets.
-> Redistribute Motion 6:6 (slice4; segments: 6) (cost=0.00..9.17 rows=40 width=22)
Hash Key: public.country.region
Rows out: Avg 39.8 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 77/77 rows with 0.011/0.011 ms to first row, 0.074/0.074 ms to end, start offset by 125/125 ms.
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=22)
Rows out: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 45/43 rows with 9.843/12 ms to first row, 9.966/12 ms to end, start offset by 81/82 ms.
-> Hash (cost=8.53..8.53 rows=5 width=72)
Rows in: Avg 3.8 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 7/7 rows with 92/92 ms to end, start offset by 33/33 ms.
-> HashAggregate (cost=7.78..8.28 rows=5 width=72)
Filter: pg_catalog.sum(partial_aggregation.unnamed_attr_2) <> 0
Group By: public.country.region
Rows out: Avg 3.8 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 7/7 rows with 92/92 ms to end, start offset by 33/33 ms.
Executor memory: 601K bytes avg, 601K bytes max (seg5:rhuo-mbp).
-> Redistribute Motion 6:6 (slice5; segments: 6) (cost=6.78..7.28 rows=5 width=72)
Hash Key: public.country.region
Rows out: Avg 19.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 36/36 rows with 56/56 ms to first row, 91/91 ms to end, start offset by 34/34 ms.
-> HashAggregate (cost=6.78..6.78 rows=5 width=72)
Group By: public.country.region
Rows out: Avg 19.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg0:rhuo-mbp) 23/19 rows with 12/15 ms to end, start offset by 79/107 ms.
Executor memory: 601K bytes avg, 601K bytes max (seg5:rhuo-mbp).
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=25)
Rows out: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg0:rhuo-mbp) 45/36 rows with 12/14 ms to first row, 12/15 ms to end, start offset by 79/107 ms.
-> Materialize (cost=160.85..160.91 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 1/1 rows with 133/167 ms to end, start offset by 46/11 ms.
-> Broadcast Motion 6:6 (slice16; segments: 6) (cost=160.55..160.84 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 1/1 rows with 132/166 ms to first row, 133/167 ms to end, start offset by 46/12 ms.
-> Subquery Scan longlivingregions (cost=160.55..160.77 rows=1 width=88)
Filter: region = 'Eastern Europe'::text AND 'Eastern Europe'::text = region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg2:rhuo-mbp/seg2:rhuo-mbp) 1/1 rows with 105/105 ms to end, start offset by 73/73 ms.
-> Shared Scan (share slice:id 16:0) (cost=160.55..160.77 rows=5 width=88)
Rows out: Avg 4.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 6/4 rows with 34/105 ms to end, start offset by 143/73 ms.
-> Materialize (cost=160.30..160.55 rows=5 width=88)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 103/103 ms to end.
-> GroupAggregate (cost=159.33..160.27 rows=5 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 4.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 6/4 rows with 32/102 ms to end, start offset by 144/73 ms.
-> Sort (cost=159.33..159.40 rows=5 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 20.3 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 28/24 rows with 32/102 ms to end, start offset by 144/73 ms.
Executor memory: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp).
Work_mem used: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Redistribute Motion 6:6 (slice15; segments: 6) (cost=143.18..158.75 rows=5 width=88)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 20.3 rows x 6 workers at destination. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 28/24 rows with 30/100 ms to first row, 31/102 ms to end, start offset by 144/73 ms.
-> GroupAggregate (cost=143.18..158.25 rows=5 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 20.3 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 24/20 rows with 127/131 ms to first row, 129/133 ms to end, start offset by 46/41 ms.
Executor memory: 652K bytes avg, 770K bytes max (seg1:rhuo-mbp).
Work_mem used: 33K bytes avg, 33K bytes max (seg5:rhuo-mbp).
-> Sort (cost=143.18..145.64 rows=164 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 228/165 rows with 127/131 ms to end, start offset by 46/41 ms.
Executor memory: 113K bytes avg, 145K bytes max (seg4:rhuo-mbp).
Work_mem used: 113K bytes avg, 145K bytes max (seg4:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Redistribute Motion 6:6 (slice14; segments: 6) (cost=29.30..94.26 rows=164 width=88)
Hash Key: public.countrylanguage.language
Rows out: Avg 164.0 rows x 6 workers at destination. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 228/165 rows with 28/130 ms to end, start offset by 143/42 ms.
-> Hash Join (cost=29.30..74.58 rows=164 width=88)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg5:rhuo-mbp) 178/151 rows with 112/117 ms to first row, 113/117 ms to end, start offset by 58/54 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 4K bytes avg, 4K bytes max (seg4:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg4) Hash chain length 1.0 avg, 1 max, using 45 of 32779 buckets.
-> Redistribute Motion 6:6 (slice10; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
Rows out: Avg 164.0 rows x 6 workers at destination. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 178/178 rows with 0.020/0.018 ms to first row, 0.158/0.180 ms to end, start offset by 170/170 ms.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg1:rhuo-mbp) 171/166 rows with 6.623/15 ms to first row, 6.735/15 ms to end, start offset by 132/102 ms.
-> Hash (cost=26.32..26.32 rows=40 width=84)
Rows in: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg5:rhuo-mbp) 45/36 rows with 112/116 ms to end, start offset by 58/54 ms.
-> Redistribute Motion 6:6 (slice13; segments: 6) (cost=8.78..26.32 rows=40 width=84)
Hash Key: public.country.code
Rows out: Avg 39.8 rows x 6 workers at destination. Max/Last(seg4:rhuo-mbp/seg5:rhuo-mbp) 45/36 rows with 112/116 ms to end, start offset by 58/54 ms.
-> Hash Join (cost=8.78..21.54 rows=40 width=84)
Hash Cond: public.country.region = foo.region
Rows out: Avg 39.8 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 77/57 rows with 109/152 ms to first row, 110/152 ms to end, start offset by 61/18 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg3:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg3) Hash chain length 1.0 avg, 1 max, using 7 of 32779 buckets.
(seg5) Hash chain length 1.0 avg, 1 max, using 7 of 32779 buckets.
-> Redistribute Motion 6:6 (slice11; segments: 6) (cost=0.00..9.17 rows=40 width=18)
Hash Key: public.country.region
Rows out: Avg 39.8 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 77/77 rows with 0.010/0.010 ms to first row, 0.142/0.142 ms to end, start offset by 170/170 ms.
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=18)
Rows out: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 45/42 rows with 12/12 ms to end, start offset by 104/105 ms.
-> Hash (cost=8.47..8.47 rows=5 width=80)
Rows in: Avg 4.2 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 7/5 rows with 105/151 ms to end, start offset by 64/19 ms.
-> HashAggregate (cost=7.78..8.22 rows=5 width=80)
Group By: public.country.region
Rows out: Avg 4.2 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 7/5 rows with 105/151 ms to end, start offset by 64/19 ms.
Executor memory: 601K bytes avg, 601K bytes max (seg5:rhuo-mbp).
-> Redistribute Motion 6:6 (slice12; segments: 6) (cost=6.78..7.28 rows=5 width=104)
Hash Key: public.country.region
Rows out: Avg 19.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 36/22 rows with 90/136 ms to first row, 105/150 ms to end, start offset by 65/19 ms.
-> HashAggregate (cost=6.78..6.78 rows=5 width=104)
Group By: public.country.region
Rows out: Avg 19.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg5:rhuo-mbp) 23/16 rows with 7.970/12 ms to first row, 8.176/12 ms to end, start offset by 160/147 ms.
Executor memory: 601K bytes avg, 601K bytes max (seg5:rhuo-mbp).
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=29)
Rows out: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg5:rhuo-mbp) 45/36 rows with 7.609/11 ms to first row, 7.693/11 ms to end, start offset by 161/147 ms.
-> Nested Loop (cost=2139.96..2142.98 rows=2 width=140)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 0/0 rows with 20/20 ms to end.
-> Nested Loop (cost=1979.12..1981.18 rows=2 width=98)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 0/0 rows with 20/20 ms to end.
-> Hash Join (cost=1814.94..1816.06 rows=2 width=62)
Hash Cond: allcountrystats.code = public.country.code
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 0/0 rows with 20/20 ms to end.
Executor memory: 1K bytes.
Work_mem used: 1K bytes. Workfile: (0 spilling, 0 reused)
-> Shared Scan (share slice:id 21:2) (cost=1808.15..1808.59 rows=40 width=64)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 0/0 rows with 11/11 ms to end.
-> Hash (cost=6.78..6.78 rows=1 width=18)
Rows in: Avg 1.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg0:rhuo-mbp) 1/0 rows with 8.655/8.892 ms to end, start offset by 223/222 ms.
-> Append-only Scan on country (cost=0.00..6.78 rows=1 width=18)
Filter: indepyear >= 1850 AND indepyear <= 1900 AND 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg0:rhuo-mbp) 1/0 rows with 8.550/8.891 ms to first row, 8.607/8.891 ms to end, start offset by 223/222 ms.
-> Materialize (cost=164.17..164.23 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/1 rows with 0.057/0.089 ms to end, start offset by 220/222 ms.
-> Broadcast Motion 6:6 (slice17; segments: 6) (cost=163.87..164.17 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/1 rows with 0.007/0.013 ms to first row, 0.008/0.014 ms to end, start offset by 220/222 ms.
-> Subquery Scan denseregions (cost=163.87..164.10 rows=1 width=36)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg4:rhuo-mbp/seg4:rhuo-mbp) 1/1 rows with 2.713/2.713 ms to first row, 2.717/2.717 ms to end, start offset by 194/194 ms.
-> Shared Scan (share slice:id 17:1) (cost=163.87..164.10 rows=5 width=92)
Rows out: Avg 3.8 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 7/4 rows with 1.889/2.702 ms to first row, 1.892/2.705 ms to end, start offset by 194/194 ms.
-> Materialize (cost=160.85..160.91 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 1/1 rows with 0.086/0.113 ms to end, start offset by 220/222 ms.
-> Broadcast Motion 6:6 (slice18; segments: 6) (cost=160.55..160.84 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 1/1 rows with 0.011/0.012 ms to first row, 0.014/0.015 ms to end, start offset by 220/222 ms.
-> Subquery Scan longlivingregions (cost=160.55..160.77 rows=1 width=88)
Filter: region = 'Eastern Europe'::text AND 'Eastern Europe'::text = region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg2:rhuo-mbp/seg5:rhuo-mbp) 1/0 rows with 2.988/3.300 ms to first row, 2.993/3.300 ms to end, start offset by 175/175 ms.
-> Shared Scan (share slice:id 18:0) (cost=160.55..160.77 rows=5 width=88)
Rows out: Avg 4.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg5:rhuo-mbp) 6/2 rows with 1.632/3.287 ms to first row, 1.864/3.292 ms to end, start offset by 176/175 ms.
-> Nested Loop (cost=2139.38..2142.39 rows=2 width=140)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 21/21 ms to end.
-> Nested Loop (cost=1978.53..1980.60 rows=2 width=98)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 21/21 ms to end.
-> Hash Join (cost=1814.36..1815.48 rows=2 width=62)
Hash Cond: allcountrystats.code = public.country.code
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 20/20 ms to end.
Executor memory: 1K bytes avg, 1K bytes max (seg1:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg1:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg1) Hash chain length 1.0 avg, 1 max, using 3 of 32779 buckets.
-> Shared Scan (share slice:id 21:2) (cost=1808.15..1808.59 rows=40 width=64)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 0/0 rows with 12/12 ms to end.
-> Hash (cost=6.18..6.18 rows=1 width=18)
Rows in: Avg 1.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 3/1 rows with 8.382/8.687 ms to end, start offset by 243/232 ms.
-> Append-only Scan on country (cost=0.00..6.18 rows=1 width=18)
Filter: indepyear > 1900 AND 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 3/1 rows with 8.340/8.625 ms to first row, 8.376/8.675 ms to end, start offset by 243/232 ms.
-> Materialize (cost=164.17..164.23 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/1 rows with 0.062/0.087 ms to end, start offset by 229/230 ms.
-> Broadcast Motion 6:6 (slice19; segments: 6) (cost=163.87..164.17 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 0.008/0.008 ms to first row, 0.009/0.009 ms to end, start offset by 229/229 ms.
-> Subquery Scan denseregions (cost=163.87..164.10 rows=1 width=36)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg4:rhuo-mbp/seg1:rhuo-mbp) 1/0 rows with 2.083/2.878 ms to first row, 2.088/2.878 ms to end, start offset by 194/194 ms.
-> Shared Scan (share slice:id 19:1) (cost=163.87..164.10 rows=5 width=92)
Rows out: Avg 3.8 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 7/7 rows with 2.756/2.756 ms to first row, 2.761/2.761 ms to end, start offset by 194/194 ms.
-> Materialize (cost=160.85..160.91 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 0.078/0.078 ms to end, start offset by 229/229 ms.
-> Broadcast Motion 6:6 (slice20; segments: 6) (cost=160.55..160.84 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 0.011/0.011 ms to first row, 0.014/0.014 ms to end, start offset by 229/229 ms.
-> Subquery Scan longlivingregions (cost=160.55..160.77 rows=1 width=88)
Filter: region = 'Eastern Europe'::text AND 'Eastern Europe'::text = region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg2:rhuo-mbp/seg0:rhuo-mbp) 1/0 rows with 3.321/3.375 ms to first row, 3.330/3.375 ms to end, start offset by 175/175 ms.
-> Shared Scan (share slice:id 20:0) (cost=160.55..160.77 rows=5 width=88)
Rows out: Avg 4.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg0:rhuo-mbp) 6/4 rows with 2.026/3.350 ms to first row, 2.030/3.367 ms to end, start offset by 175/175 ms.
Slice statistics:
(slice0) Executor memory: 1340K bytes.
(slice1) Executor memory: 1364K bytes avg x 6 workers, 1364K bytes max (seg5:rhuo-mbp).
(slice2) Executor memory: 1364K bytes avg x 6 workers, 1364K bytes max (seg5:rhuo-mbp).
(slice3) Executor memory: 1364K bytes avg x 6 workers, 1364K bytes max (seg5:rhuo-mbp).
(slice4) Executor memory: 1364K bytes avg x 6 workers, 1364K bytes max (seg5:rhuo-mbp).
(slice5) Executor memory: 1972K bytes avg x 6 workers, 1972K bytes max (seg5:rhuo-mbp).
(slice6) Executor memory: 2381K bytes avg x 6 workers, 2381K bytes max (seg5:rhuo-mbp). Work_mem: 1K bytes max.
(slice7) Executor memory: 1764K bytes avg x 6 workers, 1764K bytes max (seg5:rhuo-mbp). Work_mem: 4K bytes max.
(slice8) Executor memory: 1396K bytes avg x 6 workers, 1428K bytes max (seg4:rhuo-mbp). Work_mem: 145K bytes max.
(slice9) Executor memory: 1482K bytes avg x 6 workers, 1525K bytes max (seg1:rhuo-mbp). Work_mem: 129K bytes max.
(slice10) Executor memory: 1364K bytes avg x 6 workers, 1364K bytes max (seg5:rhuo-mbp).
(slice11) Executor memory: 1372K bytes avg x 6 workers, 1372K bytes max (seg5:rhuo-mbp).
(slice12) Executor memory: 1989K bytes avg x 6 workers, 1989K bytes max (seg5:rhuo-mbp).
(slice13) Executor memory: 2405K bytes avg x 6 workers, 2405K bytes max (seg5:rhuo-mbp). Work_mem: 1K bytes max.
(slice14) Executor memory: 1797K bytes avg x 6 workers, 1797K bytes max (seg5:rhuo-mbp). Work_mem: 4K bytes max.
(slice15) Executor memory: 1437K bytes avg x 6 workers, 1469K bytes max (seg4:rhuo-mbp). Work_mem: 145K bytes max.
(slice16) Executor memory: 1343K bytes avg x 6 workers, 1343K bytes max (seg5:rhuo-mbp). Work_mem: 58K bytes max.
(slice17) Executor memory: 1397K bytes avg x 6 workers, 1397K bytes max (seg5:rhuo-mbp).
(slice18) Executor memory: 1301K bytes avg x 6 workers, 1301K bytes max (seg5:rhuo-mbp).
(slice19) Executor memory: 1413K bytes avg x 6 workers, 1413K bytes max (seg5:rhuo-mbp).
(slice20) Executor memory: 1317K bytes avg x 6 workers, 1317K bytes max (seg5:rhuo-mbp).
(slice21) Executor memory: 3542K bytes avg x 6 workers, 3542K bytes max (seg5:rhuo-mbp). Work_mem: 58K bytes max.
Statement statistics:
Memory used: 262144K bytes
Settings: default_hash_table_bucket_number=6; gp_cte_sharing=on
Dispatcher statistics:
executors used(total/cached/new connection): (126/126/0); dispatcher time(total/connection/dispatch data): (1.356 ms/0.000 ms/0.734 ms).
dispatch data time(max/min/avg): (0.029 ms/0.006 ms/0.010 ms); consume executor data time(max/min/avg): (0.024 ms/0.005 ms/0.011 ms); free executor time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
Data locality statistics:
data locality ratio: 1.000; virtual segment number: 6; different host number: 1; virtual segment number per host(avg/min/max): (6/6/6); segment size(avg/min/max): (42846.667 B/40992 B/44728 B); segment size with penalty(avg/min/max): (0.000 B/0 B/0 B); continuity(avg/min/max): (1.000/1.000/1.000); DFS metadatacache: 0.139 ms; resource allocation: 0.578 ms; datalocality calculation: 0.122 ms.
Total runtime: 253.570 ms
(316 rows)
Step 7: plan for retrieving data from the view with filter when gp_cte_sharing = off
explain analyze select city_cnt,lang_cnt,name,"REGION_POP","REGION_GNP",region from view_with_shared_scans where region = 'Eastern Europe';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 6:1 (slice31; segments: 6) (cost=1650.62..5600.00 rows=24 width=120)
Rows out: Avg 10.0 rows x 1 workers at destination. Max/Last(seg-1:rhuo-mbp/seg-1:rhuo-mbp) 10/10 rows with 637/637 ms to first row, 731/731 ms to end, start offset by 5.331/5.331 ms.
-> Subquery Scan view_with_shared_scans (cost=1650.62..5600.00 rows=4 width=120)
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 4/1 rows with 647/714 ms to first row, 714/719 ms to end, start offset by 22/16 ms.
-> Append (cost=1650.62..5599.76 rows=4 width=141)
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 4/1 rows with 647/714 ms to first row, 714/719 ms to end, start offset by 22/16 ms.
-> Nested Loop (cost=1650.62..1866.70 rows=2 width=140)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 584/584 ms to end.
-> Nested Loop (cost=1624.74..1839.87 rows=2 width=98)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 33/33 ms to end.
-> Hash Join (cost=1598.81..1812.99 rows=2 width=62)
Hash Cond: allcountrystats.code = public.country.code
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 31/31 ms to end.
-> GroupAggregate (cost=1592.01..1805.52 rows=40 width=64)
Group By: public.country.code, public.country.name
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Sort (cost=1592.01..1634.00 rows=2799 width=27)
Sort Key: public.country.code, public.country.name
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Hash Join (cost=64.96..413.45 rows=2799 width=27)
Hash Cond: public.city.countrycode = public.country.code
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Redistribute Motion 6:6 (slice1; segments: 6) (cost=0.00..128.37 rows=680 width=8)
Hash Key: public.city.countrycode
Rows out: Avg 0.0 rows x 0 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Append-only Scan on city (cost=0.00..46.79 rows=680 width=8)
Rows out: Avg 679.8 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 696/696 rows with 124/124 ms to first row, 125/125 ms to end, start offset by 462/462 ms.
-> Hash (cost=52.66..52.66 rows=164 width=27)
Rows in: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Hash Join (cost=7.38..52.66 rows=164 width=27)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Redistribute Motion 6:6 (slice2; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
Rows out: Avg 0.0 rows x 0 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg1:rhuo-mbp) 171/166 rows with 40/291 ms to end, start offset by 454/56 ms.
-> Hash (cost=4.39..4.39 rows=40 width=15)
Rows in: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=15)
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 0/0 ms to end.
-> Hash (cost=6.78..6.78 rows=1 width=18)
Rows in: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 31/31 ms to end.
-> Append-only Scan on country (cost=0.00..6.78 rows=1 width=18)
Filter: indepyear >= 1800 AND indepyear <= 1850 AND 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 0.0 rows x 0 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 0/0 rows with 31/31 ms to end.
-> Materialize (cost=25.93..25.99 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 1.722/1.722 ms to end, start offset by 566/566 ms.
-> Broadcast Motion 6:6 (slice6; segments: 6) (cost=25.85..25.93 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 0.012/0.012 ms to end, start offset by 567/567 ms.
-> Subquery Scan denseregions (cost=25.85..25.86 rows=1 width=36)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/0 rows with 374/474 ms to end, start offset by 185/83 ms.
-> Sort (cost=25.85..25.86 rows=1 width=92)
Sort Key: "?column6?"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/0 rows with 374/474 ms to end, start offset by 185/83 ms.
Executor memory: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp).
Work_mem used: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> GroupAggregate (cost=25.65..25.84 rows=1 width=92)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/0 rows with 373/474 ms to end, start offset by 185/83 ms.
Executor memory: 33K bytes.
Work_mem used: 33K bytes.
-> Sort (cost=25.65..25.67 rows=2 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 61/0 rows with 373/474 ms to end, start offset by 185/83 ms.
Executor memory: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp).
Work_mem used: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Nested Loop (cost=11.56..25.48 rows=2 width=84)
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 61/0 rows with 372/474 ms to end, start offset by 185/83 ms.
-> Redistribute Motion 6:6 (slice4; segments: 6) (cost=5.88..19.61 rows=2 width=26)
Hash Key: 'Eastern Europe'::text
Rows out: Avg 61.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 61/61 rows with 0.011/0.011 ms to first row, 0.072/0.072 ms to end, start offset by 558/558 ms.
-> Hash Join (cost=5.88..19.41 rows=2 width=26)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 10.2 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 12/10 rows with 350/479 ms to end, start offset by 29/35 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg4) Hash chain length 1.0 avg, 1 max, using 10 of 32779 buckets.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg4:rhuo-mbp) 171/171 rows with 8.800/8.800 ms to first row, 8.914/8.914 ms to end, start offset by 370/370 ms.
-> Hash (cost=5.73..5.73 rows=3 width=22)
Rows in: Avg 10.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 10/10 rows with 0.030/340 ms to end, start offset by 413/30 ms.
-> Broadcast Motion 6:6 (slice3; segments: 6) (cost=0.00..5.73 rows=3 width=22)
Rows out: Avg 10.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 10/10 rows with 0.008/205 ms to first row, 0.022/340 ms to end, start offset by 413/30 ms.
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=22)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 4/1 rows with 249/280 ms to end, start offset by 121/90 ms.
-> Materialize (cost=5.68..5.69 rows=1 width=72)
Rows out: Avg 62.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 62/0 rows with 372/474 ms to end of 62 scans, start offset by 558/83 ms.
-> Subquery Scan foo (cost=5.65..5.68 rows=1 width=72)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/0 rows with 372/474 ms to end, start offset by 186/83 ms.
-> HashAggregate (cost=5.65..5.67 rows=1 width=72)
Filter: pg_catalog.sum(partial_aggregation.unnamed_attr_2) <> 0
Group By: public.country.region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/0 rows with 372/474 ms to end, start offset by 186/83 ms.
Executor memory: 285K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Redistribute Motion 6:6 (slice5; segments: 6) (cost=5.61..5.63 rows=1 width=72)
Hash Key: public.country.region
Rows out: Avg 6.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 6/0 rows with 29/473 ms to first row, 372/473 ms to end, start offset by 186/83 ms.
-> HashAggregate (cost=5.61..5.61 rows=1 width=72)
Group By: public.country.region
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/1 rows with 55/275 ms to end, start offset by 178/119 ms.
Executor memory: 345K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=25)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 4/4 rows with 275/275 ms to end, start offset by 119/119 ms.
-> Materialize (cost=25.88..25.94 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 551/551 ms to end, start offset by 15/15 ms.
-> Broadcast Motion 6:6 (slice10; segments: 6) (cost=25.64..25.88 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 551/551 ms to end, start offset by 15/15 ms.
-> GroupAggregate (cost=25.64..25.81 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/0 rows with 448/450 ms to end, start offset by 118/113 ms.
Executor memory: 33K bytes.
Work_mem used: 33K bytes.
-> Sort (cost=25.64..25.67 rows=2 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 61/0 rows with 447/450 ms to end, start offset by 118/113 ms.
Executor memory: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp).
Work_mem used: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Nested Loop (cost=11.56..25.48 rows=2 width=88)
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 61/0 rows with 309/450 ms to end, start offset by 256/114 ms.
-> Redistribute Motion 6:6 (slice8; segments: 6) (cost=5.88..19.61 rows=2 width=22)
Hash Key: 'Eastern Europe'::text
Rows out: Avg 61.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 61/61 rows with 0.014/0.014 ms to first row, 0.072/0.072 ms to end, start offset by 564/564 ms.
-> Hash Join (cost=5.88..19.41 rows=2 width=22)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 10.2 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 12/10 rows with 28/400 ms to first row, 29/400 ms to end, start offset by 411/49 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg4) Hash chain length 1.0 avg, 1 max, using 10 of 32779 buckets.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg4:rhuo-mbp) 171/171 rows with 8.670/8.670 ms to first row, 8.783/8.783 ms to end, start offset by 431/431 ms.
-> Hash (cost=5.73..5.73 rows=3 width=18)
Rows in: Avg 10.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 10/10 rows with 373/391 ms to end, start offset by 59/50 ms.
-> Broadcast Motion 6:6 (slice7; segments: 6) (cost=0.00..5.73 rows=3 width=18)
Rows out: Avg 10.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 10/10 rows with 170/184 ms to first row, 373/391 ms to end, start offset by 59/50 ms.
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=18)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg0:rhuo-mbp) 4/1 rows with 251/346 ms to end, start offset by 120/23 ms.
-> Materialize (cost=5.67..5.68 rows=1 width=80)
Rows out: Avg 62.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 62/0 rows with 309/450 ms to end of 62 scans, start offset by 565/114 ms.
-> Subquery Scan foo (cost=5.65..5.67 rows=1 width=80)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/0 rows with 309/450 ms to end, start offset by 256/114 ms.
-> HashAggregate (cost=5.65..5.66 rows=1 width=80)
Group By: public.country.region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/0 rows with 309/450 ms to end, start offset by 256/114 ms.
Executor memory: 285K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Redistribute Motion 6:6 (slice9; segments: 6) (cost=5.61..5.63 rows=1 width=104)
Hash Key: public.country.region
Rows out: Avg 6.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 6/0 rows with 0.010/450 ms to first row, 308/450 ms to end, start offset by 256/114 ms.
-> HashAggregate (cost=5.61..5.61 rows=1 width=104)
Group By: public.country.region
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 1/1 rows with 298/366 ms to first row, 299/366 ms to end, start offset by 50/39 ms.
Executor memory: 345K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=29)
Filter: region = 'Eastern Europe'::text AND 'Eastern Europe'::text = region
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 4/1 rows with 95/366 ms to first row, 98/366 ms to end, start offset by 462/39 ms.
-> Nested Loop (cost=1650.62..1866.70 rows=2 width=140)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 1/1 rows with 72/72 ms to first row, 74/74 ms to end, start offset by 597/597 ms.
-> Nested Loop (cost=1624.74..1839.87 rows=2 width=98)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 1/1 rows with 72/72 ms to first row, 74/74 ms to end, start offset by 597/597 ms.
-> Hash Join (cost=1598.81..1812.99 rows=2 width=62)
Hash Cond: allcountrystats.code = public.country.code
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 1/1 rows with 72/72 ms to first row, 74/74 ms to end, start offset by 597/597 ms.
Executor memory: 1K bytes.
Work_mem used: 1K bytes. Workfile: (0 spilling, 0 reused)
(seg1) Hash chain length 1.0 avg, 1 max, using 1 of 32779 buckets.
-> GroupAggregate (cost=1592.01..1805.52 rows=40 width=64)
Group By: public.country.code, public.country.name
Rows out: Avg 37.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 37/37 rows with 32/32 ms to first row, 64/64 ms to end, start offset by 606/606 ms.
Executor memory: 3078K bytes.
Work_mem used: 593K bytes.
Work_mem wanted: 117K bytes avg, 117K bytes max (seg1:rhuo-mbp) to lessen workfile I/O affecting 1 workers.
-> Sort (cost=1592.01..1634.00 rows=2799 width=27)
Sort Key: public.country.code, public.country.name
Rows out: Avg 5433.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 5433/5433 rows with 32/32 ms to end, start offset by 606/606 ms.
Executor memory: 1209K bytes.
Work_mem used: 1209K bytes. Workfile: (0 spilling, 0 reused)
-> Hash Join (cost=64.96..413.45 rows=2799 width=27)
Hash Cond: public.city.countrycode = public.country.code
Rows out: Avg 5433.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 5433/5433 rows with 11/11 ms to first row, 14/14 ms to end, start offset by 606/606 ms.
Executor memory: 11K bytes.
Work_mem used: 11K bytes. Workfile: (0 spilling, 0 reused)
(seg1) Hash chain length 4.8 avg, 12 max, using 37 of 32779 buckets.
-> Redistribute Motion 6:6 (slice11; segments: 6) (cost=0.00..128.37 rows=680 width=8)
Hash Key: public.city.countrycode
Rows out: Avg 836.0 rows x 1 workers at destination. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 836/836 rows with 0.053/0.053 ms to first row, 0.580/0.580 ms to end, start offset by 617/617 ms.
-> Append-only Scan on city (cost=0.00..46.79 rows=680 width=8)
Rows out: Avg 679.8 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 696/695 rows with 161/288 ms to end, start offset by 244/82 ms.
-> Hash (cost=52.66..52.66 rows=164 width=27)
Rows in: Avg 178.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 178/178 rows with 11/11 ms to end, start offset by 607/607 ms.
-> Hash Join (cost=7.38..52.66 rows=164 width=27)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 178.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 178/178 rows with 9.887/9.887 ms to first row, 10/10 ms to end, start offset by 607/607 ms.
Executor memory: 2K bytes.
Work_mem used: 2K bytes. Workfile: (0 spilling, 0 reused)
(seg1) Hash chain length 1.0 avg, 1 max, using 37 of 32779 buckets.
-> Redistribute Motion 6:6 (slice12; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
Rows out: Avg 178.0 rows x 1 workers at destination. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 178/178 rows with 0.016/0.016 ms to first row, 0.158/0.158 ms to end, start offset by 617/617 ms.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg0:rhuo-mbp) 171/166 rows with 41/122 ms to end, start offset by 454/465 ms.
-> Hash (cost=4.39..4.39 rows=40 width=15)
Rows in: Avg 37.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 37/37 rows with 9.552/9.552 ms to end, start offset by 607/607 ms.
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=15)
Rows out: Avg 37.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 37/37 rows with 9.460/9.460 ms to first row, 9.522/9.522 ms to end, start offset by 607/607 ms.
-> Hash (cost=6.78..6.78 rows=1 width=18)
Rows in: Avg 1.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg0:rhuo-mbp) 1/0 rows with 8.952/11 ms to end, start offset by 597/597 ms.
-> Append-only Scan on country (cost=0.00..6.78 rows=1 width=18)
Filter: indepyear >= 1850 AND indepyear <= 1900 AND 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg1:rhuo-mbp/seg0:rhuo-mbp) 1/0 rows with 8.842/11 ms to first row, 8.941/11 ms to end, start offset by 597/597 ms.
-> Materialize (cost=25.93..25.99 rows=1 width=36)
Rows out: Avg 1.2 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 2/2 rows with 0.119/0.119 ms to first row, 0.121/0.121 ms to end of 2 scans, start offset by 669/669 ms.
-> Broadcast Motion 6:6 (slice16; segments: 6) (cost=25.85..25.93 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/1 rows with 0.005/0.059 ms to first row, 0.005/0.060 ms to end, start offset by 599/597 ms.
-> Subquery Scan denseregions (cost=25.85..25.86 rows=1 width=36)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 1/0 rows with 286/490 ms to end, start offset by 266/62 ms.
-> Sort (cost=25.85..25.86 rows=1 width=92)
Sort Key: "?column6?"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 1/0 rows with 286/490 ms to end, start offset by 266/62 ms.
Executor memory: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp).
Work_mem used: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> GroupAggregate (cost=25.65..25.84 rows=1 width=92)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 1/0 rows with 286/489 ms to end, start offset by 266/62 ms.
Executor memory: 33K bytes.
Work_mem used: 33K bytes.
-> Sort (cost=25.65..25.67 rows=2 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 61/0 rows with 285/489 ms to end, start offset by 266/62 ms.
Executor memory: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp).
Work_mem used: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Nested Loop (cost=11.56..25.48 rows=2 width=84)
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 61/0 rows with 274/483 ms to first row, 285/483 ms to end, start offset by 266/69 ms.
-> Redistribute Motion 6:6 (slice14; segments: 6) (cost=5.88..19.61 rows=2 width=26)
Hash Key: 'Eastern Europe'::text
Rows out: Avg 61.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 61/0 rows with 133/144 ms to first row, 144/144 ms to end, start offset by 407/407 ms.
-> Hash Join (cost=5.88..19.41 rows=2 width=26)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 10.2 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 12/11 rows with 14/150 ms to first row, 15/150 ms to end, start offset by 527/389 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg4) Hash chain length 1.0 avg, 1 max, using 10 of 32779 buckets.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 171/150 rows with 9.096/18 ms to first row, 9.518/18 ms to end, start offset by 532/532 ms.
-> Hash (cost=5.73..5.73 rows=3 width=22)
Rows in: Avg 10.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 10/10 rows with 6.131/142 ms to end, start offset by 526/389 ms.
-> Broadcast Motion 6:6 (slice13; segments: 6) (cost=0.00..5.73 rows=3 width=22)
Rows out: Avg 10.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 10/10 rows with 0.007/0.007 ms to first row, 6.119/142 ms to end, start offset by 526/389 ms.
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=22)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 4/1 rows with 22/285 ms to end, start offset by 509/110 ms.
-> Materialize (cost=5.68..5.69 rows=1 width=72)
Rows out: Avg 62.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 62/0 rows with 141/339 ms to end of 62 scans, start offset by 551/69 ms.
-> Subquery Scan foo (cost=5.65..5.68 rows=1 width=72)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 1/0 rows with 141/338 ms to end, start offset by 267/69 ms.
-> HashAggregate (cost=5.65..5.67 rows=1 width=72)
Filter: pg_catalog.sum(partial_aggregation.unnamed_attr_2) <> 0
Group By: public.country.region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 1/0 rows with 141/338 ms to end, start offset by 267/69 ms.
Executor memory: 285K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Redistribute Motion 6:6 (slice15; segments: 6) (cost=5.61..5.63 rows=1 width=72)
Hash Key: public.country.region
Rows out: Avg 6.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 6/0 rows with 0.018/338 ms to first row, 140/338 ms to end, start offset by 267/69 ms.
-> HashAggregate (cost=5.61..5.61 rows=1 width=72)
Group By: public.country.region
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/1 rows with 27/270 ms to end, start offset by 146/137 ms.
Executor memory: 345K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=25)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 4/1 rows with 266/269 ms to end, start offset by 129/137 ms.
-> Materialize (cost=25.88..25.94 rows=1 width=88)
Rows out: Avg 1.2 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 2/1 rows with 0.088/0.125 ms to first row, 0.089/0.125 ms to end of 2 scans, start offset by 669/596 ms.
-> Broadcast Motion 6:6 (slice20; segments: 6) (cost=25.64..25.88 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 1/1 rows with 0.008/0.012 ms to end, start offset by 599/596 ms.
-> GroupAggregate (cost=25.64..25.81 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 1/0 rows with 396/423 ms to end, start offset by 155/127 ms.
Executor memory: 33K bytes.
Work_mem used: 33K bytes.
-> Sort (cost=25.64..25.67 rows=2 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 61/0 rows with 395/423 ms to end, start offset by 155/127 ms.
Executor memory: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp).
Work_mem used: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Nested Loop (cost=11.56..25.48 rows=2 width=88)
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 61/0 rows with 394/422 ms to end, start offset by 155/127 ms.
-> Redistribute Motion 6:6 (slice18; segments: 6) (cost=5.88..19.61 rows=2 width=22)
Hash Key: 'Eastern Europe'::text
Rows out: Avg 61.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 61/0 rows with 16/16 ms to end, start offset by 533/533 ms.
-> Hash Join (cost=5.88..19.41 rows=2 width=22)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 10.2 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 12/10 rows with 380/529 ms to first row, 381/529 ms to end, start offset by 168/19 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg4) Hash chain length 1.0 avg, 1 max, using 10 of 32779 buckets.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg4:rhuo-mbp) 171/171 rows with 7.538/7.538 ms to first row, 7.754/7.754 ms to end, start offset by 541/541 ms.
-> Hash (cost=5.73..5.73 rows=3 width=18)
Rows in: Avg 10.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 10/10 rows with 516/516 ms to end, start offset by 25/25 ms.
-> Broadcast Motion 6:6 (slice17; segments: 6) (cost=0.00..5.73 rows=3 width=18)
Rows out: Avg 10.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 10/10 rows with 319/319 ms to first row, 516/516 ms to end, start offset by 25/25 ms.
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=18)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 4/4 rows with 320/320 ms to end, start offset by 50/50 ms.
-> Materialize (cost=5.67..5.68 rows=1 width=80)
Rows out: Avg 62.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 62/0 rows with 378/406 ms to end of 62 scans, start offset by 549/127 ms.
-> Subquery Scan foo (cost=5.65..5.67 rows=1 width=80)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 1/0 rows with 378/406 ms to end, start offset by 155/127 ms.
-> HashAggregate (cost=5.65..5.66 rows=1 width=80)
Group By: public.country.region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 1/0 rows with 378/406 ms to end, start offset by 155/127 ms.
Executor memory: 285K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Redistribute Motion 6:6 (slice19; segments: 6) (cost=5.61..5.63 rows=1 width=104)
Hash Key: public.country.region
Rows out: Avg 6.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 6/0 rows with 79/405 ms to first row, 366/405 ms to end, start offset by 155/127 ms.
-> HashAggregate (cost=5.61..5.61 rows=1 width=104)
Group By: public.country.region
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/1 rows with 288/337 ms to end, start offset by 81/69 ms.
Executor memory: 345K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=29)
Filter: region = 'Eastern Europe'::text AND 'Eastern Europe'::text = region
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 4/4 rows with 337/337 ms to end, start offset by 69/69 ms.
-> Nested Loop (cost=1650.03..1866.12 rows=2 width=140)
Rows out: Avg 1.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 3/1 rows with 32/123 ms to first row, 65/129 ms to end, start offset by 671/606 ms.
-> Nested Loop (cost=1624.15..1839.29 rows=2 width=98)
Rows out: Avg 1.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 3/1 rows with 31/123 ms to first row, 65/129 ms to end, start offset by 671/606 ms.
-> Hash Join (cost=1598.22..1812.41 rows=2 width=62)
Hash Cond: allcountrystats.code = public.country.code
Rows out: Avg 1.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg2:rhuo-mbp) 3/1 rows with 31/123 ms to first row, 65/128 ms to end, start offset by 671/607 ms.
Executor memory: 1K bytes avg, 1K bytes max (seg1:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg1:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg1) Hash chain length 1.0 avg, 1 max, using 3 of 32779 buckets.
(seg2) Hash chain length 1.0 avg, 1 max, using 1 of 32779 buckets.
-> GroupAggregate (cost=1592.01..1805.52 rows=40 width=64)
Group By: public.country.code, public.country.name
Rows out: Avg 38.7 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg2:rhuo-mbp) 43/42 rows with 34/44 ms to first row, 105/120 ms to end, start offset by 613/615 ms.
Executor memory: 3382K bytes avg, 4103K bytes max (seg2:rhuo-mbp).
Work_mem used: 524K bytes avg, 633K bytes max (seg4:rhuo-mbp).
Work_mem wanted: 242K bytes avg, 339K bytes max (seg2:rhuo-mbp) to lessen workfile I/O affecting 5 workers.
-> Sort (cost=1592.01..1634.00 rows=2799 width=27)
Sort Key: public.country.code, public.country.name
Rows out: Avg 5111.7 rows x 6 workers. Max/Last(seg2:rhuo-mbp/seg2:rhuo-mbp) 7374/7374 rows with 44/44 ms to first row, 45/45 ms to end, start offset by 615/615 ms.
Executor memory: 1611K bytes avg, 2233K bytes max (seg5:rhuo-mbp).
Work_mem used: 1611K bytes avg, 2233K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Hash Join (cost=64.96..413.45 rows=2799 width=27)
Hash Cond: public.city.countrycode = public.country.code
Rows out: Avg 5111.7 rows x 6 workers. Max/Last(seg2:rhuo-mbp/seg2:rhuo-mbp) 7374/7374 rows with 11/11 ms to first row, 18/18 ms to end, start offset by 615/615 ms.
Executor memory: 10K bytes avg, 11K bytes max (seg1:rhuo-mbp).
Work_mem used: 10K bytes avg, 11K bytes max (seg1:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg1) Hash chain length 4.8 avg, 12 max, using 37 of 32779 buckets.
(seg2) Hash chain length 4.2 avg, 12 max, using 42 of 32779 buckets.
-> Redistribute Motion 6:6 (slice21; segments: 6) (cost=0.00..128.37 rows=680 width=8)
Hash Key: public.city.countrycode
Rows out: Avg 679.8 rows x 6 workers at destination. Max/Last(seg2:rhuo-mbp/seg2:rhuo-mbp) 943/943 rows with 0.074/0.074 ms to first row, 0.832/0.832 ms to end, start offset by 626/626 ms.
-> Append-only Scan on city (cost=0.00..46.79 rows=680 width=8)
Rows out: Avg 679.8 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg0:rhuo-mbp) 696/651 rows with 22/263 ms to end, start offset by 426/94 ms.
-> Hash (cost=52.66..52.66 rows=164 width=27)
Rows in: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 178/156 rows with 11/11 ms to end, start offset by 614/612 ms.
-> Hash Join (cost=7.38..52.66 rows=164 width=27)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 178/156 rows with 10/10 ms to first row, 11/11 ms to end, start offset by 614/612 ms.
Executor memory: 2K bytes avg, 2K bytes max (seg4:rhuo-mbp).
Work_mem used: 2K bytes avg, 2K bytes max (seg4:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg2) Hash chain length 1.0 avg, 1 max, using 42 of 32779 buckets.
(seg4) Hash chain length 1.0 avg, 1 max, using 45 of 32779 buckets.
-> Redistribute Motion 6:6 (slice22; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
Rows out: Avg 164.0 rows x 6 workers at destination. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 178/156 rows with 0.023/0.019 ms to first row, 0.158/0.208 ms to end, start offset by 624/623 ms.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 171/167 rows with 28/263 ms to first row, 29/263 ms to end, start offset by 144/130 ms.
-> Hash (cost=4.39..4.39 rows=40 width=15)
Rows in: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 45/43 rows with 9.993/10 ms to end, start offset by 614/613 ms.
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=15)
Rows out: Avg 39.8 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg3:rhuo-mbp) 45/43 rows with 9.879/9.922 ms to first row, 9.955/9.999 ms to end, start offset by 614/613 ms.
-> Hash (cost=6.18..6.18 rows=1 width=18)
Rows in: Avg 1.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 3/1 rows with 7.016/9.103 ms to end, start offset by 671/604 ms.
-> Append-only Scan on country (cost=0.00..6.18 rows=1 width=18)
Filter: indepyear > 1900 AND 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 3/1 rows with 6.964/9.033 ms to first row, 7.010/9.095 ms to end, start offset by 671/604 ms.
-> Materialize (cost=25.93..25.99 rows=1 width=36)
Rows out: Avg 2.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg3:rhuo-mbp) 4/2 rows with 0.050/0.097 ms to first row, 0.053/0.100 ms to end of 4 scans, start offset by 735/635 ms.
-> Broadcast Motion 6:6 (slice26; segments: 6) (cost=25.85..25.93 rows=1 width=36)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 0.008/0.008 ms to first row, 0.009/0.009 ms to end, start offset by 606/606 ms.
-> Subquery Scan denseregions (cost=25.85..25.86 rows=1 width=36)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 564/564 ms to end, start offset by 19/19 ms.
-> Sort (cost=25.85..25.86 rows=1 width=92)
Sort Key: "?column6?"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 564/564 ms to end, start offset by 19/19 ms.
Executor memory: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp).
Work_mem used: 58K bytes avg, 58K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> GroupAggregate (cost=25.65..25.84 rows=1 width=92)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 564/564 ms to end, start offset by 19/19 ms.
Executor memory: 33K bytes.
Work_mem used: 33K bytes.
-> Sort (cost=25.65..25.67 rows=2 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 61/61 rows with 564/564 ms to end, start offset by 19/19 ms.
Executor memory: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp).
Work_mem used: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Nested Loop (cost=11.56..25.48 rows=2 width=84)
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 61/61 rows with 561/561 ms to first row, 563/563 ms to end, start offset by 19/19 ms.
-> Redistribute Motion 6:6 (slice24; segments: 6) (cost=5.88..19.61 rows=2 width=26)
Hash Key: 'Eastern Europe'::text
Rows out: Avg 61.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg2:rhuo-mbp) 61/0 rows with 146/148 ms to first row, 148/148 ms to end, start offset by 433/433 ms.
-> Hash Join (cost=5.88..19.41 rows=2 width=26)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 10.2 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg1:rhuo-mbp) 12/9 rows with 320/440 ms to end, start offset by 262/139 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg4) Hash chain length 1.0 avg, 1 max, using 10 of 32779 buckets.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg4:rhuo-mbp) 171/171 rows with 29/29 ms to end, start offset by 553/553 ms.
-> Hash (cost=5.73..5.73 rows=3 width=22)
Rows in: Avg 10.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 10/10 rows with 288/413 ms to end, start offset by 265/140 ms.
-> Broadcast Motion 6:6 (slice23; segments: 6) (cost=0.00..5.73 rows=3 width=22)
Rows out: Avg 10.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 10/10 rows with 104/229 ms to first row, 288/413 ms to end, start offset by 265/140 ms.
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=22)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 4/4 rows with 260/260 ms to end, start offset by 110/110 ms.
-> Materialize (cost=5.68..5.69 rows=1 width=72)
Rows out: Avg 62.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 62/62 rows with 415/415 ms to end of 62 scans, start offset by 582/582 ms.
-> Subquery Scan foo (cost=5.65..5.68 rows=1 width=72)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 414/414 ms to end, start offset by 19/19 ms.
-> HashAggregate (cost=5.65..5.67 rows=1 width=72)
Filter: pg_catalog.sum(partial_aggregation.unnamed_attr_2) <> 0
Group By: public.country.region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 414/414 ms to end, start offset by 19/19 ms.
Executor memory: 285K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Redistribute Motion 6:6 (slice25; segments: 6) (cost=5.61..5.63 rows=1 width=72)
Hash Key: public.country.region
Rows out: Avg 6.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 6/6 rows with 332/332 ms to first row, 414/414 ms to end, start offset by 19/19 ms.
-> HashAggregate (cost=5.61..5.61 rows=1 width=72)
Group By: public.country.region
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg1:rhuo-mbp) 1/1 rows with 353/369 ms to end, start offset by 45/37 ms.
Executor memory: 345K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=25)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg1:rhuo-mbp) 4/4 rows with 369/369 ms to end, start offset by 37/37 ms.
-> Materialize (cost=25.88..25.94 rows=1 width=88)
Rows out: Avg 2.5 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg5:rhuo-mbp) 4/3 rows with 0.051/0.081 ms to first row, 0.053/0.081 ms to end of 4 scans, start offset by 735/713 ms.
-> Broadcast Motion 6:6 (slice30; segments: 6) (cost=25.64..25.88 rows=1 width=88)
Rows out: Avg 1.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 1/1 rows with 0.012/0.012 ms to first row, 0.013/0.013 ms to end, start offset by 606/606 ms.
-> GroupAggregate (cost=25.64..25.81 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 1/0 rows with 402/415 ms to end, start offset by 153/139 ms.
Executor memory: 33K bytes.
Work_mem used: 33K bytes.
-> Sort (cost=25.64..25.67 rows=2 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg3:rhuo-mbp) 61/0 rows with 402/415 ms to end, start offset by 153/139 ms.
Executor memory: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp).
Work_mem used: 62K bytes avg, 81K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
-> Nested Loop (cost=11.56..25.48 rows=2 width=88)
Rows out: Avg 61.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 61/0 rows with 399/403 ms to first row, 401/403 ms to end, start offset by 154/151 ms.
-> Redistribute Motion 6:6 (slice28; segments: 6) (cost=5.88..19.61 rows=2 width=22)
Hash Key: 'Eastern Europe'::text
Rows out: Avg 61.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 61/61 rows with 0.010/0.010 ms to first row, 2.152/2.152 ms to end, start offset by 553/553 ms.
-> Hash Join (cost=5.88..19.41 rows=2 width=22)
Hash Cond: public.countrylanguage.countrycode = public.country.code
Rows out: Avg 10.2 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg5:rhuo-mbp) 12/8 rows with 24/421 ms to end, start offset by 424/101 ms.
Executor memory: 529K bytes avg, 529K bytes max (seg5:rhuo-mbp).
Work_mem used: 1K bytes avg, 1K bytes max (seg5:rhuo-mbp). Workfile: (0 spilling, 0 reused)
(seg4) Hash chain length 1.0 avg, 1 max, using 10 of 32779 buckets.
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
Rows out: Avg 164.0 rows x 6 workers. Max/Last(seg4:rhuo-mbp/seg5:rhuo-mbp) 171/164 rows with 23/153 ms to first row, 24/153 ms to end, start offset by 424/368 ms.
-> Hash (cost=5.73..5.73 rows=3 width=18)
Rows in: Avg 10.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 10/10 rows with 267/267 ms to end, start offset by 101/101 ms.
-> Broadcast Motion 6:6 (slice27; segments: 6) (cost=0.00..5.73 rows=3 width=18)
Rows out: Avg 10.0 rows x 6 workers at destination. Max/Last(seg5:rhuo-mbp/seg5:rhuo-mbp) 10/10 rows with 136/136 ms to first row, 267/267 ms to end, start offset by 101/101 ms.
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=18)
Filter: 'Eastern Europe'::text = region AND region = 'Eastern Europe'::text
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg0:rhuo-mbp) 4/1 rows with 260/332 ms to end, start offset by 108/18 ms.
-> Materialize (cost=5.67..5.68 rows=1 width=80)
Rows out: Avg 62.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 62/0 rows with 399/401 ms to end of 62 scans, start offset by 555/151 ms.
-> Subquery Scan foo (cost=5.65..5.67 rows=1 width=80)
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/0 rows with 399/401 ms to end, start offset by 154/151 ms.
-> HashAggregate (cost=5.65..5.66 rows=1 width=80)
Group By: public.country.region
Rows out: Avg 1.0 rows x 1 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/0 rows with 399/401 ms to end, start offset by 154/151 ms.
Executor memory: 285K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Redistribute Motion 6:6 (slice29; segments: 6) (cost=5.61..5.63 rows=1 width=104)
Hash Key: public.country.region
Rows out: Avg 6.0 rows x 1 workers at destination. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 6/0 rows with 27/401 ms to first row, 398/401 ms to end, start offset by 154/151 ms.
-> HashAggregate (cost=5.61..5.61 rows=1 width=104)
Group By: public.country.region
Rows out: Avg 1.0 rows x 6 workers. Max/Last(seg5:rhuo-mbp/seg4:rhuo-mbp) 1/1 rows with 42/266 ms to end, start offset by 455/92 ms.
Executor memory: 345K bytes avg, 345K bytes max (seg5:rhuo-mbp).
-> Append-only Scan on country (cost=0.00..5.58 rows=1 width=29)
Filter: region = 'Eastern Europe'::text AND 'Eastern Europe'::text = region
Rows out: Avg 1.7 rows x 6 workers. Max/Last(seg1:rhuo-mbp/seg4:rhuo-mbp) 4/1 rows with 124/265 ms to end, start offset by 427/92 ms.
Slice statistics:
(slice0) Executor memory: 2538K bytes.
(slice1) Executor memory: 2400K bytes avg x 6 workers, 2400K bytes max (seg5:rhuo-mbp).
(slice2) Executor memory: 2400K bytes avg x 6 workers, 2400K bytes max (seg5:rhuo-mbp).
(slice3) Executor memory: 2400K bytes avg x 6 workers, 2400K bytes max (seg5:rhuo-mbp).
(slice4) Executor memory: 2929K bytes avg x 6 workers, 2929K bytes max (seg5:rhuo-mbp). Work_mem: 1K bytes max.
(slice5) Executor memory: 2747K bytes avg x 6 workers, 2753K bytes max (seg5:rhuo-mbp).
(slice6) Executor memory: 2623K bytes avg x 6 workers, 2697K bytes max (seg5:rhuo-mbp). Work_mem: 81K bytes max.
(slice7) Executor memory: 2400K bytes avg x 6 workers, 2400K bytes max (seg5:rhuo-mbp).
(slice8) Executor memory: 2929K bytes avg x 6 workers, 2929K bytes max (seg5:rhuo-mbp). Work_mem: 1K bytes max.
(slice9) Executor memory: 2753K bytes avg x 6 workers, 2753K bytes max (seg5:rhuo-mbp).
(slice10) Executor memory: 2591K bytes avg x 6 workers, 2665K bytes max (seg5:rhuo-mbp). Work_mem: 81K bytes max.
(slice11) Executor memory: 2409K bytes avg x 6 workers, 2409K bytes max (seg5:rhuo-mbp).
(slice12) Executor memory: 2417K bytes avg x 6 workers, 2417K bytes max (seg5:rhuo-mbp).
(slice13) Executor memory: 2425K bytes avg x 6 workers, 2425K bytes max (seg5:rhuo-mbp).
(slice14) Executor memory: 2961K bytes avg x 6 workers, 2961K bytes max (seg5:rhuo-mbp). Work_mem: 1K bytes max.
(slice15) Executor memory: 2788K bytes avg x 6 workers, 2793K bytes max (seg5:rhuo-mbp).
(slice16) Executor memory: 2672K bytes avg x 6 workers, 2745K bytes max (seg5:rhuo-mbp). Work_mem: 81K bytes max.
(slice17) Executor memory: 2457K bytes avg x 6 workers, 2457K bytes max (seg5:rhuo-mbp).
(slice18) Executor memory: 2994K bytes avg x 6 workers, 2994K bytes max (seg5:rhuo-mbp). Work_mem: 1K bytes max.
(slice19) Executor memory: 2826K bytes avg x 6 workers, 2826K bytes max (seg5:rhuo-mbp).
(slice20) Executor memory: 2672K bytes avg x 6 workers, 2746K bytes max (seg5:rhuo-mbp). Work_mem: 81K bytes max.
(slice21) Executor memory: 2490K bytes avg x 6 workers, 2490K bytes max (seg5:rhuo-mbp).
(slice22) Executor memory: 2498K bytes avg x 6 workers, 2498K bytes max (seg5:rhuo-mbp).
(slice23) Executor memory: 2506K bytes avg x 6 workers, 2506K bytes max (seg5:rhuo-mbp).
(slice24) Executor memory: 3042K bytes avg x 6 workers, 3042K bytes max (seg5:rhuo-mbp). Work_mem: 1K bytes max.
(slice25) Executor memory: 2869K bytes avg x 6 workers, 2874K bytes max (seg5:rhuo-mbp).
(slice26) Executor memory: 2753K bytes avg x 6 workers, 2826K bytes max (seg5:rhuo-mbp). Work_mem: 81K bytes max.
(slice27) Executor memory: 2538K bytes avg x 6 workers, 2538K bytes max (seg5:rhuo-mbp).
(slice28) Executor memory: 3075K bytes avg x 6 workers, 3075K bytes max (seg5:rhuo-mbp). Work_mem: 1K bytes max.
(slice29) Executor memory: 2907K bytes avg x 6 workers, 2907K bytes max (seg5:rhuo-mbp).
(slice30) Executor memory: 2754K bytes avg x 6 workers, 2827K bytes max (seg5:rhuo-mbp). Work_mem: 81K bytes max.
(slice31) * Executor memory: 6301K bytes avg x 6 workers, 7132K bytes max (seg2:rhuo-mbp). Work_mem: 2233K bytes max, 339K bytes wanted.
Statement statistics:
Memory used: 262144K bytes
Memory wanted: 30180K bytes
Settings: default_hash_table_bucket_number=6; gp_cte_sharing=off
Dispatcher statistics:
executors used(total/cached/new connection): (186/186/0); dispatcher time(total/connection/dispatch data): (1.681 ms/0.000 ms/0.811 ms).
dispatch data time(max/min/avg): (0.035 ms/0.007 ms/0.011 ms); consume executor data time(max/min/avg): (0.036 ms/0.005 ms/0.012 ms); free executor time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
Data locality statistics:
data locality ratio: 1.000; virtual segment number: 6; different host number: 1; virtual segment number per host(avg/min/max): (6/6/6); segment size(avg/min/max): (42846.667 B/40992 B/44728 B); segment size with penalty(avg/min/max): (0.000 B/0 B/0 B); continuity(avg/min/max): (1.000/1.000/1.000); DFS metadatacache: 0.171 ms; resource allocation: 0.616 ms; datalocality calculation: 0.129 ms.
Total runtime: 737.672 ms
(536 rows)
Attachments
Attachments
Issue Links
- is part of
-
-
- Open
-