You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hawq.apache.org by "Goden Yao (JIRA)" <ji...@apache.org> on 2016/07/14 22:14:20 UTC
[jira] [Updated] (HAWQ-852) Quey hang when retrieve data from view
defined using CTE when filter using like is used
[ https://issues.apache.org/jira/browse/HAWQ-852?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Goden Yao updated HAWQ-852:
---------------------------
Fix Version/s: backlog
> 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
> Affects Versions: 2.0.0.0-incubating
> Reporter: Ruilong Huo
> Assignee: Ruilong Huo
> Fix For: backlog
>
> Attachments: cte_query_like.cte_share_off.out, cte_query_like.cte_share_off.sql, cte_query_like.cte_share_on.out, cte_query_like.cte_share_on.sql, world.sql
>
>
> 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)