[ https://issues.apache.org/jira/browse/HAWQ-852?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ruilong Huo updated HAWQ-852: ----------------------------- Attachment: cte_query_like.cte_share_off.sql cte_query_like.cte_share_off.out cte_query_like.cte_share_on.sql cte_query_like.cte_share_on.out world.sql > 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 > 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)