Ruilong Huo created HAWQ-852:
--------------------------------
Summary: Quey hang when retrieve data from view defined using CTE
when filter using like is used
Key: HAWQ-852
URL: https://issues.apache.org/jira/browse/HAWQ-852
Project: Apache HAWQ
Issue Type: Bug
Components: Core, Optimizer, Query Execution
Reporter: Ruilong Huo
Assignee: Amr El-Helw
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
{noformat}
psql -a -d postgres -f world.sql > world.out 2>&1
{noformat}
Step 2: create view defined using CTE by running attached
{noformat}
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
);
{noformat}
Step 3: retrieve all data from the view succeed
{noformat}
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)
{noformat}
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
{noformat}
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
{noformat}
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
{noformat}
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)
{noformat}
Step 6: plan for retrieving data from the view with filter using like when
gp_cte_sharing = on
{noformat}
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)
{noformat}
Step 7: plan for retrieving data from the view with filter using like when
gp_cte_sharing = off
{noformat}
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)
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
