[
https://issues.apache.org/jira/browse/HAWQ-852?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruilong Huo reassigned HAWQ-852:
--------------------------------
Assignee: Ruilong Huo (was: Amr El-Helw)
> 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: Ruilong Huo
>
> 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)
