| -- |
| -- BOOLEAN |
| -- |
| create table gpd1 (c1 char(1), c2 numeric, c3 numeric) distributed by (c1); |
| insert into gpd1 values ('a', 1, 1); |
| analyze gpd1; |
| insert into gpd1 values ('a', 1, 2); |
| insert into gpd1 values ('b', 2, 1); |
| insert into gpd1 values ('b', 1, 2); |
| insert into gpd1 values ('c', 3, 2); |
| insert into gpd1 values ('c', 2, 3); |
| insert into gpd1 values ('d', 4, 4); |
| insert into gpd1 values ('d', 4, 3); |
| -- |
| -- ignore |
| -- |
| select c1 from gpd1; |
| c1 |
| ---- |
| a |
| a |
| c |
| c |
| b |
| b |
| d |
| d |
| (8 rows) |
| |
| -- |
| -- order 1, 2 |
| -- |
| select c1, c1, c2, c3 from gpd1 order by 1,2; |
| c1 | c1 | c2 | c3 |
| ----+----+----+---- |
| a | a | 1 | 1 |
| a | a | 1 | 2 |
| b | b | 2 | 1 |
| b | b | 1 | 2 |
| c | c | 3 | 2 |
| c | c | 2 | 3 |
| d | d | 4 | 4 |
| d | d | 4 | 3 |
| (8 rows) |
| |
| -- |
| -- |
| select c1, c1, c2, c3 from gpd1 order by 1,2,3,4; -- order 1, 2 , 3 , 4 |
| c1 | c1 | c2 | c3 |
| ----+----+----+---- |
| a | a | 1 | 1 |
| a | a | 1 | 2 |
| b | b | 1 | 2 |
| b | b | 2 | 1 |
| c | c | 2 | 3 |
| c | c | 3 | 2 |
| d | d | 4 | 3 |
| d | d | 4 | 4 |
| (8 rows) |
| |
| -- |
| -- ignore |
| -- order 1, 2 |
| -- |
| select c1, c1, c2, c3 from gpd1 order by 1,2; |
| c1 | c1 | c2 | c3 |
| ----+----+----+---- |
| a | a | 1 | 1 |
| a | a | 1 | 2 |
| b | b | 2 | 1 |
| b | b | 1 | 2 |
| c | c | 3 | 2 |
| c | c | 2 | 3 |
| d | d | 4 | 4 |
| d | d | 4 | 3 |
| (8 rows) |
| |
| -- |
| -- mvd 2,3->1 ; 2,3->4,5 |
| -- order 4 |
| -- |
| select c1, c2, c3, c1, c1, c2 from gpd1 order by 4; |
| c1 | c2 | c3 | c1 | c1 | c2 |
| ----+----+----+----+----+---- |
| a | 1 | 1 | a | a | 1 |
| a | 1 | 2 | a | a | 1 |
| b | 2 | 1 | b | b | 2 |
| b | 1 | 2 | b | b | 1 |
| c | 3 | 2 | c | c | 3 |
| c | 2 | 3 | c | c | 2 |
| d | 4 | 4 | d | d | 4 |
| d | 4 | 3 | d | d | 4 |
| (8 rows) |
| |
| -- Brian: the contents of column 1 are not determined by any other |
| -- column -- the column "specifies itself" |
| -- |
| -- mvd 1->1 |
| -- |
| select row_number() over (), c1, c2, c3 from gpd1; |
| row_number | c1 | c2 | c3 |
| ------------+----+----+---- |
| 1 | a | 1 | 1 |
| 2 | a | 1 | 2 |
| 3 | c | 3 | 2 |
| 4 | c | 2 | 3 |
| 5 | b | 2 | 1 |
| 6 | b | 1 | 2 |
| 7 | d | 4 | 4 |
| 8 | d | 4 | 3 |
| (8 rows) |
| |
| -- Brian: 1 specifies 2 |
| -- |
| -- |
| select -- mvd 1 -> 2 |
| x, |
| row_number() over (partition by x) as y, |
| z |
| from (values (1,'A'),(1,'B'),(2,'C'),(2,'D')) r(x,z); |
| x | y | z |
| ---+---+--- |
| 1 | 1 | B |
| 1 | 2 | A |
| 2 | 1 | D |
| 2 | 2 | C |
| (4 rows) |
| |
| -- start_ignore |
| -- |
| -- whatever is here is ignored until we reach end_ignore |
| -- |
| -- end_ignore |
| -- |
| -- explain testing |
| -- |
| set gp_segments_for_planner=4; |
| set optimizer_segments=4; |
| set gp_cost_hashjoin_chainwalk=on; |
| set optimizer_nestloop_factor = 1.0; |
| explain analyze select a.* from gpd1 as a, gpd1 as b where b.c1 in (select max(c1) from gpd1); |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------------------------------------------- |
| Hash Join (cost=0.00..1724.00 rows=1 width=12) (actual time=12.627..13.309 rows=16 loops=1) |
| Hash Cond: (b.c1 = (max(gpd1.c1))) |
| Extra Text: Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.00 rows=1 width=14) (actual time=1.565..1.588 rows=64 loops=1) |
| -> Nested Loop (cost=0.00..1293.00 rows=1 width=14) (actual time=9.768..12.264 rows=32 loops=1) |
| Join Filter: true |
| -> Seq Scan on gpd1 a (cost=0.00..431.00 rows=1 width=12) (actual time=0.271..0.274 rows=4 loops=1) |
| -> Materialize (cost=0.00..431.00 rows=2 width=2) (actual time=1.896..2.391 rows=7 loops=5) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=2 width=2) (actual time=9.431..11.896 rows=8 loops=1) |
| -> Seq Scan on gpd1 b (cost=0.00..431.00 rows=1 width=2) (actual time=1.802..1.805 rows=4 loops=1) |
| -> Hash (cost=431.00..431.00 rows=1 width=8) (actual time=10.948..10.949 rows=1 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> Aggregate (cost=0.00..431.00 rows=1 width=8) (actual time=10.938..10.938 rows=1 loops=1) |
| -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=2) (actual time=7.642..10.908 rows=8 loops=1) |
| -> Seq Scan on gpd1 (cost=0.00..431.00 rows=1 width=2) (actual time=4.205..4.207 rows=4 loops=1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| Planning Time: 18.292 ms |
| (slice0) Executor memory: 2133K bytes. Work_mem: 2049K bytes max. |
| (slice1) Executor memory: 41K bytes avg x 3 workers, 41K bytes max (seg0). Work_mem: 17K bytes max. |
| (slice2) Executor memory: 38K bytes avg x 3 workers, 38K bytes max (seg0). |
| (slice3) Executor memory: 38K bytes avg x 3 workers, 38K bytes max (seg0). |
| Memory used: 128000kB |
| Execution Time: 95.501 ms |
| (23 rows) |
| |
| explain select a.* from gpd1 as a, gpd1 as b where b.c1 in (select max(c1) from gpd1); |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------- |
| Hash Join (cost=0.00..1724.00 rows=1 width=12) |
| Hash Cond: (b.c1 = (max(gpd1.c1))) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.00 rows=1 width=14) |
| -> Nested Loop (cost=0.00..1293.00 rows=1 width=14) |
| Join Filter: true |
| -> Seq Scan on gpd1 a (cost=0.00..431.00 rows=1 width=12) |
| -> Materialize (cost=0.00..431.00 rows=2 width=2) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=2 width=2) |
| -> Seq Scan on gpd1 b (cost=0.00..431.00 rows=1 width=2) |
| -> Hash (cost=431.00..431.00 rows=1 width=8) |
| -> Aggregate (cost=0.00..431.00 rows=1 width=8) |
| -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=2) |
| -> Seq Scan on gpd1 (cost=0.00..431.00 rows=1 width=2) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (14 rows) |
| |
| select a.* from gpd1 as a, gpd1 as b where b.c1 in (select max(c1) from gpd1); |
| c1 | c2 | c3 |
| ----+----+---- |
| a | 1 | 1 |
| a | 1 | 1 |
| a | 1 | 2 |
| a | 1 | 2 |
| c | 3 | 2 |
| c | 3 | 2 |
| c | 2 | 3 |
| c | 2 | 3 |
| b | 2 | 1 |
| b | 2 | 1 |
| b | 1 | 2 |
| b | 1 | 2 |
| d | 4 | 4 |
| d | 4 | 4 |
| d | 4 | 3 |
| d | 4 | 3 |
| (16 rows) |
| |
| set gp_segments_for_planner=40; |
| set optimizer_segments=40; |
| set optimizer_nestloop_factor = 1.0; |
| explain select a.* from gpd1 as a, gpd1 as b where b.c1 in (select max(c1) from gpd1); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1724.00 rows=1 width=12) |
| -> Hash Join (cost=0.00..1724.00 rows=1 width=12) |
| Hash Cond: ((max(gpd1.c1)) = b.c1) |
| -> Redistribute Motion 1:3 (slice2) (cost=0.00..431.00 rows=1 width=8) |
| Hash Key: (max(gpd1.c1)) |
| -> Finalize Aggregate (cost=0.00..431.00 rows=1 width=8) |
| -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=8) |
| -> Partial Aggregate (cost=0.00..431.00 rows=1 width=8) |
| -> Seq Scan on gpd1 (cost=0.00..431.00 rows=1 width=2) |
| -> Hash (cost=1293.00..1293.00 rows=1 width=14) |
| -> Nested Loop (cost=0.00..1293.00 rows=1 width=14) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=14 width=12) |
| -> Seq Scan on gpd1 a (cost=0.00..431.00 rows=1 width=12) |
| -> Seq Scan on gpd1 b (cost=0.00..431.00 rows=1 width=2) |
| Optimizer: Pivotal Optimizer (GPORCA) version 2.74.0 |
| (16 rows) |
| |
| select a.* from gpd1 as a, gpd1 as b where b.c1 in (select max(c1) from gpd1); |
| c1 | c2 | c3 |
| ----+----+---- |
| b | 2 | 1 |
| b | 2 | 1 |
| b | 1 | 2 |
| b | 1 | 2 |
| d | 4 | 4 |
| d | 4 | 4 |
| d | 4 | 3 |
| d | 4 | 3 |
| a | 1 | 1 |
| a | 1 | 1 |
| a | 1 | 2 |
| a | 1 | 2 |
| c | 3 | 2 |
| c | 3 | 2 |
| c | 2 | 3 |
| c | 2 | 3 |
| (16 rows) |
| |
| explain analyze select a.* from gpd1 as a, gpd1 as b where b.c1 in (select max(c1) from gpd1); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1724.00 rows=1 width=12) (actual time=7.550..7.820 rows=16 loops=1) |
| -> Hash Join (cost=0.00..1724.00 rows=1 width=12) (actual time=5.773..7.000 rows=16 loops=1) |
| Hash Cond: ((max(gpd1.c1)) = b.c1) |
| Extra Text: (seg0) Hash chain length 16.0 avg, 16 max, using 1 of 524288 buckets. |
| -> Redistribute Motion 1:3 (slice2) (cost=0.00..431.00 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1) |
| Hash Key: (max(gpd1.c1)) |
| -> Finalize Aggregate (cost=0.00..431.00 rows=1 width=8) (actual time=1.384..1.384 rows=1 loops=1) |
| -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=8) (actual time=0.216..1.339 rows=3 loops=1) |
| -> Partial Aggregate (cost=0.00..431.00 rows=1 width=8) (actual time=0.056..0.056 rows=1 loops=1) |
| -> Seq Scan on gpd1 (cost=0.00..431.00 rows=1 width=2) (actual time=0.038..0.045 rows=4 loops=1) |
| -> Hash (cost=1293.00..1293.00 rows=1 width=14) (actual time=0.287..0.287 rows=32 loops=1) |
| -> Nested Loop (cost=0.00..1293.00 rows=1 width=14) (actual time=0.089..0.223 rows=32 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=14 width=12) (actual time=0.945..2.269 rows=8 loops=1) |
| -> Seq Scan on gpd1 a (cost=0.00..431.00 rows=1 width=12) (actual time=1.831..1.832 rows=4 loops=1) |
| -> Seq Scan on gpd1 b (cost=0.00..431.00 rows=1 width=2) (actual time=0.122..0.130 rows=4 loops=9) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| Planning Time: 17.379 ms |
| (slice0) Executor memory: 64K bytes. |
| (slice1) Executor memory: 4158K bytes avg x 3 workers, 4158K bytes max (seg0). Work_mem: 4098K bytes max. |
| (slice2) Executor memory: 17K bytes (entry db). |
| (slice3) Executor memory: 38K bytes avg x 3 workers, 38K bytes max (seg0). |
| (slice4) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| Memory used: 128000kB |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0 |
| Execution time: 16.006 ms |
| (25 rows) |
| |
| -- |
| -- Clean up |
| -- |
| DROP TABLE gpd1; |
| -- start_matchsubs |
| -- |
| -- # create a match/subs expression to handle a value which always changes |
| -- |
| -- # use zero-width negative look-behind assertion to match "gpmatchsubs1" |
| -- # that does not follow substring |
| -- |
| -- m/(?<!substring..)gpmatchsubs1/ |
| -- s/gpmatchsubs1.*/happy sub1/ |
| -- |
| -- m/(?<!substring..)gpmatchsubs2/ |
| -- s/gpmatchsubs2.*/happy sub2/ |
| -- |
| -- end_matchsubs |
| -- substitute constant values for results |
| -- use substring because length of time string varies which changes output |
| select substring('gpmatchsubs1' || now(), 1, 25); |
| substring |
| --------------------------- |
| gpmatchsubs1Fri Aug 15 16 |
| (1 row) |
| |
| select substring('gpmatchsubs2' || now(), 1, 25); |
| substring |
| --------------------------- |
| gpmatchsubs2Fri Aug 15 16 |
| (1 row) |
| |
| -- start_matchignore |
| -- |
| -- # create a match expression to handle a value which always changes |
| -- |
| -- m/(?<!substring..)gpmatchignore1/ |
| -- m/(?<!substring..)gpmatchignore2/ |
| -- |
| -- end_matchignore |
| -- just ignore the results |
| select substring('gpmatchignore1' || now(), 1, 25); |
| substring |
| --------------------------- |
| gpmatchignore1Fri Aug 15 |
| (1 row) |
| |
| select substring('gpmatchignore2' || now(), 1, 25); |
| substring |
| --------------------------- |
| gpmatchignore2Fri Aug 15 |
| (1 row) |
| |
| reset optimizer_nestloop_factor; |
| -- |
