Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
2.0.0.0-incubating
-
None
Description
It hangs to retrieve data from view defined using CTE when filter using like 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 using like hang when gp_cte_sharing = on by running attached cte_query.cte_share_on.sql, see cte_query_like.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'; ERROR: canceling statement due to user request
Step 5: retrieve data from the view with filter using like succeed when gp_cte_sharing = off by running attached cte_query_like.cte_share_off.sql, see cte_query_like.cte_share_off.out for details
select city_cnt,lang_cnt,name,"REGION_POP","REGION_GNP",region from view_with_shared_scans where region like '%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
44 | 4 | Poland | 307026000 | 659980.00 | Eastern Europe
9 | 6 | Hungary | 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 using like when gp_cte_sharing = on
explain select city_cnt,lang_cnt,name,"REGION_POP","REGION_GNP",region from view_with_shared_scans where region like '%Eastern Europe%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 6:1 (slice24; segments: 6) (cost=2140.79..6425.72 rows=25 width=120)
-> Subquery Scan view_with_shared_scans (cost=2140.79..6425.72 rows=5 width=120)
-> Append (cost=2140.79..6425.48 rows=5 width=140)
-> Hash Join (cost=2140.79..2141.92 rows=2 width=140)
Hash Cond: allcountrystats.code = public.country.code
-> Shared Scan (share slice:id 24:2) (cost=1808.15..1808.59 rows=40 width=64)
-> Materialize (cost=1805.76..1808.15 rows=40 width=64)
-> GroupAggregate (cost=1592.01..1805.52 rows=40 width=64)
Group By: public.country.code, public.country.name
-> Sort (cost=1592.01..1634.00 rows=2799 width=27)
Sort Key: public.country.code, public.country.name
-> Hash Join (cost=64.96..413.45 rows=2799 width=27)
Hash Cond: public.city.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice1; segments: 6) (cost=0.00..128.37 rows=680 width=8)
Hash Key: public.city.countrycode
-> Append-only Scan on city (cost=0.00..46.79 rows=680 width=8)
-> Hash (cost=52.66..52.66 rows=164 width=27)
-> Hash Join (cost=7.38..52.66 rows=164 width=27)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice2; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=4.39..4.39 rows=40 width=15)
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=15)
-> Hash (cost=332.59..332.59 rows=1 width=96)
-> Redistribute Motion 6:6 (slice17; segments: 6) (cost=325.49..332.59 rows=1 width=96)
Hash Key: public.country.code
-> Hash Join (cost=325.49..332.49 rows=1 width=96)
Hash Cond: denseregions.region = longlivingregions.region
-> Broadcast Motion 6:6 (slice10; segments: 6) (cost=164.41..171.22 rows=5 width=54)
-> Hash Join (cost=164.41..170.90 rows=1 width=54)
Hash Cond: public.country.region = denseregions.region
-> Broadcast Motion 6:6 (slice3; segments: 6) (cost=0.00..6.36 rows=3 width=18)
-> Append-only Scan on country (cost=0.00..6.18 rows=1 width=18)
Filter: indepyear >= 1800 AND indepyear <= 1850 AND region ~~ '%Eastern Europe%'::text
-> Hash (cost=164.10..164.10 rows=5 width=36)
-> Subquery Scan denseregions (cost=163.87..164.10 rows=5 width=36)
Filter: region ~~ '%Eastern Europe%'::text
-> Shared Scan (share slice:id 10:1) (cost=163.87..164.10 rows=5 width=92)
-> Sort (cost=163.81..163.87 rows=5 width=92)
Sort Key: "?column6?"
-> GroupAggregate (cost=161.98..163.23 rows=5 width=92)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Sort (cost=161.98..162.04 rows=5 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Redistribute Motion 6:6 (slice9; segments: 6) (cost=143.24..161.40 rows=5 width=84)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> GroupAggregate (cost=143.24..160.90 rows=5 width=84)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Sort (cost=143.24..145.70 rows=164 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Redistribute Motion 6:6 (slice8; segments: 6) (cost=29.37..94.33 rows=164 width=84)
Hash Key: public.countrylanguage.language
-> Hash Join (cost=29.37..74.65 rows=164 width=84)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice4; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=26.38..26.38 rows=40 width=80)
-> Redistribute Motion 6:6 (slice7; segments: 6) (cost=8.84..26.38 rows=40 width=80)
Hash Key: public.country.code
-> Hash Join (cost=8.84..21.60 rows=40 width=80)
Hash Cond: public.country.region = foo.region
-> Redistribute Motion 6:6 (slice5; segments: 6) (cost=0.00..9.17 rows=40 width=22)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=22)
-> Hash (cost=8.53..8.53 rows=5 width=72)
-> 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
-> Redistribute Motion 6:6 (slice6; segments: 6) (cost=6.78..7.28 rows=5 width=72)
Hash Key: public.country.region
-> HashAggregate (cost=6.78..6.78 rows=5 width=72)
Group By: public.country.region
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=25)
-> Hash (cost=160.77..160.77 rows=5 width=88)
-> Subquery Scan longlivingregions (cost=160.55..160.77 rows=5 width=88)
Filter: region ~~ '%Eastern Europe%'::text
-> Shared Scan (share slice:id 17:0) (cost=160.55..160.77 rows=5 width=88)
-> Materialize (cost=160.30..160.55 rows=5 width=88)
-> GroupAggregate (cost=159.33..160.27 rows=5 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Sort (cost=159.33..159.40 rows=5 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Redistribute Motion 6:6 (slice16; segments: 6) (cost=143.18..158.75 rows=5 width=88)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> GroupAggregate (cost=143.18..158.25 rows=5 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Sort (cost=143.18..145.64 rows=164 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Redistribute Motion 6:6 (slice15; segments: 6) (cost=29.30..94.26 rows=164 width=88)
Hash Key: public.countrylanguage.language
-> Hash Join (cost=29.30..74.58 rows=164 width=88)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice11; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=26.32..26.32 rows=40 width=84)
-> Redistribute Motion 6:6 (slice14; segments: 6) (cost=8.78..26.32 rows=40 width=84)
Hash Key: public.country.code
-> Hash Join (cost=8.78..21.54 rows=40 width=84)
Hash Cond: public.country.region = foo.region
-> Redistribute Motion 6:6 (slice12; segments: 6) (cost=0.00..9.17 rows=40 width=18)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=18)
-> Hash (cost=8.47..8.47 rows=5 width=80)
-> HashAggregate (cost=7.78..8.22 rows=5 width=80)
Group By: public.country.region
-> Redistribute Motion 6:6 (slice13; segments: 6) (cost=6.78..7.28 rows=5 width=104)
Hash Key: public.country.region
-> HashAggregate (cost=6.78..6.78 rows=5 width=104)
Group By: public.country.region
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=29)
-> Hash Join (cost=2140.63..2141.76 rows=2 width=140)
Hash Cond: allcountrystats.code = public.country.code
-> Shared Scan (share slice:id 24:2) (cost=1808.15..1808.59 rows=40 width=64)
-> Hash (cost=332.43..332.43 rows=1 width=96)
-> Redistribute Motion 6:6 (slice20; segments: 6) (cost=325.49..332.43 rows=1 width=96)
Hash Key: public.country.code
-> Hash Join (cost=325.49..332.33 rows=1 width=96)
Hash Cond: denseregions.region = longlivingregions.region
-> Broadcast Motion 6:6 (slice19; segments: 6) (cost=164.41..171.06 rows=5 width=54)
-> Hash Join (cost=164.41..170.74 rows=1 width=54)
Hash Cond: public.country.region = denseregions.region
-> Broadcast Motion 6:6 (slice18; segments: 6) (cost=0.00..6.25 rows=1 width=18)
-> Append-only Scan on country (cost=0.00..6.18 rows=1 width=18)
Filter: indepyear >= 1850 AND indepyear <= 1900 AND region ~~ '%Eastern Europe%'::text
-> Hash (cost=164.10..164.10 rows=5 width=36)
-> Subquery Scan denseregions (cost=163.87..164.10 rows=5 width=36)
Filter: region ~~ '%Eastern Europe%'::text
-> Shared Scan (share slice:id 19:1) (cost=163.87..164.10 rows=5 width=92)
-> Hash (cost=160.77..160.77 rows=5 width=88)
-> Subquery Scan longlivingregions (cost=160.55..160.77 rows=5 width=88)
Filter: region ~~ '%Eastern Europe%'::text
-> Shared Scan (share slice:id 20:0) (cost=160.55..160.77 rows=5 width=88)
-> Hash Join (cost=2140.66..2141.55 rows=2 width=140)
Hash Cond: denseregions.region = longlivingregions.region
-> Redistribute Motion 6:6 (slice21; segments: 6) (cost=163.87..164.60 rows=5 width=36)
Hash Key: denseregions.region
-> Subquery Scan denseregions (cost=163.87..164.10 rows=5 width=36)
Filter: region ~~ '%Eastern Europe%'::text
-> Shared Scan (share slice:id 21:1) (cost=163.87..164.10 rows=5 width=92)
-> Hash (cost=1976.69..1976.69 rows=2 width=150)
-> Hash Join (cost=1975.79..1976.69 rows=2 width=150)
Hash Cond: longlivingregions.region = public.country.region
-> Redistribute Motion 6:6 (slice22; segments: 6) (cost=160.55..161.27 rows=5 width=88)
Hash Key: longlivingregions.region
-> Subquery Scan longlivingregions (cost=160.55..160.77 rows=5 width=88)
Filter: region ~~ '%Eastern Europe%'::text
-> Shared Scan (share slice:id 22:0) (cost=160.55..160.77 rows=5 width=88)
-> Hash (cost=1815.15..1815.15 rows=2 width=62)
-> Redistribute Motion 6:6 (slice23; segments: 6) (cost=1813.84..1815.15 rows=2 width=62)
Hash Key: public.country.region
-> Hash Join (cost=1813.84..1814.98 rows=2 width=62)
Hash Cond: allcountrystats.code = public.country.code
-> Shared Scan (share slice:id 23:2) (cost=1808.15..1808.59 rows=40 width=64)
-> Hash (cost=5.58..5.58 rows=2 width=18)
-> Append-only Scan on country (cost=0.00..5.58 rows=2 width=18)
Filter: indepyear > 1900 AND region ~~ '%Eastern Europe%'::text
Settings: default_hash_table_bucket_number=6; gp_cte_sharing=on
(160 rows)
Step 7: plan for retrieving data from the view with filter using like when gp_cte_sharing = off
explain select city_cnt,lang_cnt,name,"REGION_POP","REGION_GNP",region from view_with_shared_scans where region like '%Eastern Europe%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 6:1 (slice49; segments: 6) (cost=1649.68..5591.59 rows=24 width=120)
-> Subquery Scan view_with_shared_scans (cost=1649.68..5591.59 rows=4 width=120)
-> Append (cost=1649.68..5591.35 rows=4 width=141)
-> Hash Join (cost=1649.68..1863.87 rows=2 width=140)
Hash Cond: allcountrystats.code = public.country.code
-> GroupAggregate (cost=1592.01..1805.52 rows=40 width=64)
Group By: public.country.code, public.country.name
-> Sort (cost=1592.01..1634.00 rows=2799 width=27)
Sort Key: public.country.code, public.country.name
-> Hash Join (cost=64.96..413.45 rows=2799 width=27)
Hash Cond: public.city.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice1; segments: 6) (cost=0.00..128.37 rows=680 width=8)
Hash Key: public.city.countrycode
-> Append-only Scan on city (cost=0.00..46.79 rows=680 width=8)
-> Hash (cost=52.66..52.66 rows=164 width=27)
-> Hash Join (cost=7.38..52.66 rows=164 width=27)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice2; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=4.39..4.39 rows=40 width=15)
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=15)
-> Hash (cost=57.62..57.62 rows=1 width=96)
-> Redistribute Motion 6:6 (slice16; segments: 6) (cost=51.23..57.62 rows=1 width=96)
Hash Key: public.country.code
-> Hash Join (cost=51.23..57.56 rows=1 width=96)
Hash Cond: longlivingregions.region = denseregions.region
-> Hash Join (cost=25.57..31.85 rows=1 width=106)
Hash Cond: public.country.region = longlivingregions.region
-> Redistribute Motion 6:6 (slice3; segments: 6) (cost=0.00..6.23 rows=1 width=18)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..6.18 rows=1 width=18)
Filter: indepyear >= 1800 AND indepyear <= 1850 AND region ~~ '%Eastern Europe%'::text
-> Hash (cost=25.55..25.55 rows=1 width=88)
-> Redistribute Motion 6:6 (slice9; segments: 6) (cost=25.43..25.55 rows=1 width=88)
Hash Key: longlivingregions.region
-> GroupAggregate (cost=25.43..25.51 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Sort (cost=25.43..25.44 rows=1 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Redistribute Motion 6:6 (slice8; segments: 6) (cost=25.15..25.42 rows=1 width=88)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> GroupAggregate (cost=25.15..25.38 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Sort (cost=25.15..25.19 rows=3 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Redistribute Motion 6:6 (slice7; segments: 6) (cost=10.98..24.89 rows=3 width=88)
Hash Key: public.countrylanguage.language
-> Hash Join (cost=10.98..24.62 rows=3 width=88)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=10.73..10.73 rows=4 width=84)
-> Broadcast Motion 6:6 (slice6; segments: 6) (cost=5.25..10.73 rows=4 width=84)
-> Hash Join (cost=5.25..10.50 rows=1 width=84)
Hash Cond: public.country.region = foo.region
-> Redistribute Motion 6:6 (slice4; segments: 6) (cost=0.00..5.19 rows=2 width=18)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=18)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=5.22..5.22 rows=1 width=80)
-> HashAggregate (cost=5.17..5.20 rows=1 width=80)
Group By: public.country.region
-> Redistribute Motion 6:6 (slice5; segments: 6) (cost=5.09..5.13 rows=1 width=104)
Hash Key: public.country.region
-> HashAggregate (cost=5.09..5.09 rows=1 width=104)
Group By: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=29)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=25.64..25.64 rows=1 width=36)
-> Redistribute Motion 6:6 (slice15; segments: 6) (cost=25.59..25.64 rows=1 width=36)
Hash Key: denseregions.region
-> Subquery Scan denseregions (cost=25.59..25.60 rows=1 width=36)
-> Sort (cost=25.59..25.60 rows=1 width=92)
Sort Key: "?column6?"
-> GroupAggregate (cost=25.48..25.58 rows=1 width=92)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Sort (cost=25.48..25.49 rows=1 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Redistribute Motion 6:6 (slice14; segments: 6) (cost=25.16..25.47 rows=1 width=84)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> GroupAggregate (cost=25.16..25.43 rows=1 width=84)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Sort (cost=25.16..25.19 rows=3 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Redistribute Motion 6:6 (slice13; segments: 6) (cost=10.98..24.90 rows=3 width=84)
Hash Key: public.countrylanguage.language
-> Hash Join (cost=10.98..24.62 rows=3 width=84)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=10.73..10.73 rows=4 width=80)
-> Broadcast Motion 6:6 (slice12; segments: 6) (cost=5.25..10.73 rows=4 width=80)
-> Hash Join (cost=5.25..10.50 rows=1 width=80)
Hash Cond: public.country.region = foo.region
-> Redistribute Motion 6:6 (slice10; segments: 6) (cost=0.00..5.19 rows=2 width=22)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=22)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=5.23..5.23 rows=1 width=72)
-> HashAggregate (cost=5.17..5.21 rows=1 width=72)
Filter: pg_catalog.sum(partial_aggregation.unnamed_attr_2) <> 0
Group By: public.country.region
-> Redistribute Motion 6:6 (slice11; segments: 6) (cost=5.09..5.13 rows=1 width=72)
Hash Key: public.country.region
-> HashAggregate (cost=5.09..5.09 rows=1 width=72)
Group By: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=25)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash Join (cost=1649.64..1863.84 rows=2 width=140)
Hash Cond: allcountrystats.code = public.country.code
-> GroupAggregate (cost=1592.01..1805.52 rows=40 width=64)
Group By: public.country.code, public.country.name
-> Sort (cost=1592.01..1634.00 rows=2799 width=27)
Sort Key: public.country.code, public.country.name
-> Hash Join (cost=64.96..413.45 rows=2799 width=27)
Hash Cond: public.city.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice17; segments: 6) (cost=0.00..128.37 rows=680 width=8)
Hash Key: public.city.countrycode
-> Append-only Scan on city (cost=0.00..46.79 rows=680 width=8)
-> Hash (cost=52.66..52.66 rows=164 width=27)
-> Hash Join (cost=7.38..52.66 rows=164 width=27)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice18; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=4.39..4.39 rows=40 width=15)
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=15)
-> Hash (cost=57.59..57.59 rows=1 width=96)
-> Redistribute Motion 6:6 (slice32; segments: 6) (cost=51.23..57.59 rows=1 width=96)
Hash Key: public.country.code
-> Hash Join (cost=51.23..57.52 rows=1 width=96)
Hash Cond: denseregions.region = longlivingregions.region
-> Hash Join (cost=25.66..31.90 rows=1 width=54)
Hash Cond: public.country.region = denseregions.region
-> Redistribute Motion 6:6 (slice19; segments: 6) (cost=0.00..6.20 rows=1 width=18)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..6.18 rows=1 width=18)
Filter: indepyear >= 1850 AND indepyear <= 1900 AND region ~~ '%Eastern Europe%'::text
-> Hash (cost=25.64..25.64 rows=1 width=36)
-> Redistribute Motion 6:6 (slice25; segments: 6) (cost=25.59..25.64 rows=1 width=36)
Hash Key: denseregions.region
-> Subquery Scan denseregions (cost=25.59..25.60 rows=1 width=36)
-> Sort (cost=25.59..25.60 rows=1 width=92)
Sort Key: "?column6?"
-> GroupAggregate (cost=25.48..25.58 rows=1 width=92)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Sort (cost=25.48..25.49 rows=1 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Redistribute Motion 6:6 (slice24; segments: 6) (cost=25.16..25.47 rows=1 width=84)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> GroupAggregate (cost=25.16..25.43 rows=1 width=84)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Sort (cost=25.16..25.19 rows=3 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Redistribute Motion 6:6 (slice23; segments: 6) (cost=10.98..24.90 rows=3 width=84)
Hash Key: public.countrylanguage.language
-> Hash Join (cost=10.98..24.62 rows=3 width=84)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=10.73..10.73 rows=4 width=80)
-> Broadcast Motion 6:6 (slice22; segments: 6) (cost=5.25..10.73 rows=4 width=80)
-> Hash Join (cost=5.25..10.50 rows=1 width=80)
Hash Cond: public.country.region = foo.region
-> Redistribute Motion 6:6 (slice20; segments: 6) (cost=0.00..5.19 rows=2 width=22)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=22)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=5.23..5.23 rows=1 width=72)
-> HashAggregate (cost=5.17..5.21 rows=1 width=72)
Filter: pg_catalog.sum(partial_aggregation.unnamed_attr_2) <> 0
Group By: public.country.region
-> Redistribute Motion 6:6 (slice21; segments: 6) (cost=5.09..5.13 rows=1 width=72)
Hash Key: public.country.region
-> HashAggregate (cost=5.09..5.09 rows=1 width=72)
Group By: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=25)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=25.55..25.55 rows=1 width=88)
-> Redistribute Motion 6:6 (slice31; segments: 6) (cost=25.43..25.55 rows=1 width=88)
Hash Key: longlivingregions.region
-> GroupAggregate (cost=25.43..25.51 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Sort (cost=25.43..25.44 rows=1 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Redistribute Motion 6:6 (slice30; segments: 6) (cost=25.15..25.42 rows=1 width=88)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> GroupAggregate (cost=25.15..25.38 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Sort (cost=25.15..25.19 rows=3 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Redistribute Motion 6:6 (slice29; segments: 6) (cost=10.98..24.89 rows=3 width=88)
Hash Key: public.countrylanguage.language
-> Hash Join (cost=10.98..24.62 rows=3 width=88)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=10.73..10.73 rows=4 width=84)
-> Broadcast Motion 6:6 (slice28; segments: 6) (cost=5.25..10.73 rows=4 width=84)
-> Hash Join (cost=5.25..10.50 rows=1 width=84)
Hash Cond: public.country.region = foo.region
-> Redistribute Motion 6:6 (slice26; segments: 6) (cost=0.00..5.19 rows=2 width=18)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=18)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=5.22..5.22 rows=1 width=80)
-> HashAggregate (cost=5.17..5.20 rows=1 width=80)
Group By: public.country.region
-> Redistribute Motion 6:6 (slice27; segments: 6) (cost=5.09..5.13 rows=1 width=104)
Hash Key: public.country.region
-> HashAggregate (cost=5.09..5.09 rows=1 width=104)
Group By: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=29)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash Join (cost=1649.21..1863.40 rows=2 width=140)
Hash Cond: allcountrystats.code = public.country.code
-> GroupAggregate (cost=1592.01..1805.52 rows=40 width=64)
Group By: public.country.code, public.country.name
-> Sort (cost=1592.01..1634.00 rows=2799 width=27)
Sort Key: public.country.code, public.country.name
-> Hash Join (cost=64.96..413.45 rows=2799 width=27)
Hash Cond: public.city.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice33; segments: 6) (cost=0.00..128.37 rows=680 width=8)
Hash Key: public.city.countrycode
-> Append-only Scan on city (cost=0.00..46.79 rows=680 width=8)
-> Hash (cost=52.66..52.66 rows=164 width=27)
-> Hash Join (cost=7.38..52.66 rows=164 width=27)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Redistribute Motion 6:6 (slice34; segments: 6) (cost=0.00..30.52 rows=164 width=12)
Hash Key: public.countrylanguage.countrycode
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=4.39..4.39 rows=40 width=15)
-> Append-only Scan on country (cost=0.00..4.39 rows=40 width=15)
-> Hash (cost=57.16..57.16 rows=1 width=96)
-> Redistribute Motion 6:6 (slice48; segments: 6) (cost=51.23..57.16 rows=1 width=96)
Hash Key: public.country.code
-> Hash Join (cost=51.23..57.09 rows=1 width=96)
Hash Cond: longlivingregions.region = denseregions.region
-> Hash Join (cost=25.57..31.38 rows=1 width=106)
Hash Cond: public.country.region = longlivingregions.region
-> Redistribute Motion 6:6 (slice35; segments: 6) (cost=0.00..5.75 rows=2 width=18)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..5.58 rows=2 width=18)
Filter: indepyear > 1900 AND region ~~ '%Eastern Europe%'::text
-> Hash (cost=25.55..25.55 rows=1 width=88)
-> Redistribute Motion 6:6 (slice41; segments: 6) (cost=25.43..25.55 rows=1 width=88)
Hash Key: longlivingregions.region
-> GroupAggregate (cost=25.43..25.51 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Sort (cost=25.43..25.44 rows=1 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Redistribute Motion 6:6 (slice40; segments: 6) (cost=25.15..25.42 rows=1 width=88)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> GroupAggregate (cost=25.15..25.38 rows=1 width=88)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Sort (cost=25.15..25.19 rows=3 width=88)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP", foo."REGION_LIFETIME"
-> Redistribute Motion 6:6 (slice39; segments: 6) (cost=10.98..24.89 rows=3 width=88)
Hash Key: public.countrylanguage.language
-> Hash Join (cost=10.98..24.62 rows=3 width=88)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=10.73..10.73 rows=4 width=84)
-> Broadcast Motion 6:6 (slice38; segments: 6) (cost=5.25..10.73 rows=4 width=84)
-> Hash Join (cost=5.25..10.50 rows=1 width=84)
Hash Cond: public.country.region = foo.region
-> Redistribute Motion 6:6 (slice36; segments: 6) (cost=0.00..5.19 rows=2 width=18)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=18)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=5.22..5.22 rows=1 width=80)
-> HashAggregate (cost=5.17..5.20 rows=1 width=80)
Group By: public.country.region
-> Redistribute Motion 6:6 (slice37; segments: 6) (cost=5.09..5.13 rows=1 width=104)
Hash Key: public.country.region
-> HashAggregate (cost=5.09..5.09 rows=1 width=104)
Group By: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=29)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=25.64..25.64 rows=1 width=36)
-> Redistribute Motion 6:6 (slice47; segments: 6) (cost=25.59..25.64 rows=1 width=36)
Hash Key: denseregions.region
-> Subquery Scan denseregions (cost=25.59..25.60 rows=1 width=36)
-> Sort (cost=25.59..25.60 rows=1 width=92)
Sort Key: "?column6?"
-> GroupAggregate (cost=25.48..25.58 rows=1 width=92)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Sort (cost=25.48..25.49 rows=1 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Redistribute Motion 6:6 (slice46; segments: 6) (cost=25.16..25.47 rows=1 width=84)
Hash Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> GroupAggregate (cost=25.16..25.43 rows=1 width=84)
Group By: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Sort (cost=25.16..25.19 rows=3 width=84)
Sort Key: foo.region, foo."REGION_POP", foo."REGION_GNP"
-> Redistribute Motion 6:6 (slice45; segments: 6) (cost=10.98..24.90 rows=3 width=84)
Hash Key: public.countrylanguage.language
-> Hash Join (cost=10.98..24.62 rows=3 width=84)
Hash Cond: public.countrylanguage.countrycode = public.country.code
-> Append-only Scan on countrylanguage (cost=0.00..10.84 rows=164 width=12)
-> Hash (cost=10.73..10.73 rows=4 width=80)
-> Broadcast Motion 6:6 (slice44; segments: 6) (cost=5.25..10.73 rows=4 width=80)
-> Hash Join (cost=5.25..10.50 rows=1 width=80)
Hash Cond: public.country.region = foo.region
-> Redistribute Motion 6:6 (slice42; segments: 6) (cost=0.00..5.19 rows=2 width=22)
Hash Key: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=22)
Filter: region ~~ '%Eastern Europe%'::text
-> Hash (cost=5.23..5.23 rows=1 width=72)
-> HashAggregate (cost=5.17..5.21 rows=1 width=72)
Filter: pg_catalog.sum(partial_aggregation.unnamed_attr_2) <> 0
Group By: public.country.region
-> Redistribute Motion 6:6 (slice43; segments: 6) (cost=5.09..5.13 rows=1 width=72)
Hash Key: public.country.region
-> HashAggregate (cost=5.09..5.09 rows=1 width=72)
Group By: public.country.region
-> Append-only Scan on country (cost=0.00..4.99 rows=2 width=25)
Filter: region ~~ '%Eastern Europe%'::text
Settings: default_hash_table_bucket_number=6; gp_cte_sharing=off
(316 rows)
Attachments
Attachments
Issue Links
- is part of
-
-
- Open
-