Greenplum 6 마스터 Port 변경

Greenplum 마스터 노드 Port 변경

- 이미 설치된 Greenplum 클러스터에서 마스터 port 변경 절차

- https://knowledge.broadcom.com/external/article?articleNumber=296803

1. 테스트 환경

- Greenplum Version: Greenplum 6.x

- 2 Master nodes, 4 Segment nodes

2. 변경할 설정

1) DB 설정 파일 변경 ( Master node/ Standby Master node)

$ vi $MASTER_DATA_DIRECTORY/postgresql.conf

- port=5432 ##port 5432 ==> port=5433

2) DB 환경 변경

=# select * from gp_segment_configuration ;

- gp_segment_configuration.port 5432 => 5433

3) DB 접속 환경 변경

$ vi ~/.bashrc

export PGPORT=5433

3. Greenplum Master Port 변경

1) 현재 DB 환경 확인

[gpadmin@mdw gpseg-1]$ psql

Timing is on.

psql (9.4.26)

Type "help" for help.

gpadmin=# select * from gp_segment_configuration order by 1;

dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir

------+---------+------+----------------+------+--------+------+----------+---------+----------------------

1 | -1 | p | p | n | u | 5432 | mdw | mdw | /data/master/gpseg-1

2 | 0 | p | p | n | u | 6000 | sdw1 | sdw1 | /data/primary/gpseg0

3 | 1 | p | p | n | u | 6000 | sdw2 | sdw2 | /data/primary/gpseg1

4 | 2 | p | p | n | u | 6000 | sdw3 | sdw3 | /data/primary/gpseg2

5 | 3 | p | p | n | u | 6000 | sdw4 | sdw4 | /data/primary/gpseg3

6 | 0 | m | m | n | u | 7000 | sdw2 | sdw2 | /data/mirror/gpseg0

7 | 1 | m | m | n | u | 7000 | sdw3 | sdw3 | /data/mirror/gpseg1

8 | 2 | m | m | n | u | 7000 | sdw4 | sdw4 | /data/mirror/gpseg2

9 | 3 | m | m | n | u | 7000 | sdw1 | sdw1 | /data/mirror/gpseg3

10 | -1 | m | m | s | u | 5432 | smdw | smdw | /data/master/gpseg-1

(10 rows)

Time: 3.192 ms

gpadmin=#

2) DB stop

[gpadmin@mdw gpseg-1]$ gpstop -af

3) Greenplum 환경 설정 파일 port 변경 ($MASTER_DATA_DIRECTORY/postgresql.conf )

[gpadmin@mdw gpseg-1]$ grep 5432 $MASTER_DATA_DIRECTORY/postgresql.conf

port=5432 ##port 5432 # sets the database listener port for

[gpadmin@mdw gpseg-1]$ vi $MASTER_DATA_DIRECTORY/postgresql.conf ## change port from 5432 to 5433

[gpadmin@mdw gpseg-1]$ grep 5433 $MASTER_DATA_DIRECTORY/postgresql.conf

port=5433 ##port 5433 # sets the database listener port for

[gpadmin@mdw gpseg-1]$ ssh smdw

[gpadmin@smdw ~]$ vi $MASTER_DATA_DIRECTORY/postgresql.conf ## change port from 5432 to 5433

[gpadmin@smdw ~]$ grep 5433 $MASTER_DATA_DIRECTORY/postgresql.conf

port=5433 ##port 5433 # sets the database listener port for

[gpadmin@smdw ~]$

4)DB start

[gpadmin@mdw gpseg-1]$ gpstart -a

5) DB 설정 테이블의 포트 변경 (gp_segment_configuration)

[gpadmin@mdw gpseg-1]$ psql -p 5433

Timing is on.

psql (9.4.26)

Type "help" for help.

gpadmin=# begin;

BEGIN

Time: 0.686 ms

gpadmin=# set allow_system_table_mods=true;

SET

Time: 13.477 ms

gpadmin=# UPDATE gp_segment_configuration SET port=5433 WHERE port=5432 and content = -1;

UPDATE 2

Time: 10.805 ms

gpadmin=# select * from gp_segment_configuration where content=-1;

dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir

------+---------+------+----------------+------+--------+------+----------+---------+----------------------

1 | -1 | p | p | n | u | 5433 | mdw | mdw | /data/master/gpseg-1

10 | -1 | m | m | s | u | 5433 | smdw | smdw | /data/master/gpseg-1

(2 rows)

Time: 2.368 ms

gpadmin=# commit;

COMMIT

Time: 1.953 ms

gpadmin=#

gpadmin=# create table public.test ( a int);

NOTICE: Table does not have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.

HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

CREATE TABLE

Time: 31.949 ms

gpadmin=# insert into public.test select i from generate_series(1, 100) i;

INSERT 0 100

Time: 17.722 ms

gpadmin=# select count(*) from public.test;

count

-------

100

(1 row)

Time: 5.914 ms

gpadmin=# \q

5) Greenplum 접속 port 설정 변경 (~/.bashrc)

[gpadmin@mdw gpseg-1]$ psql

psql: could not connect to server: 그런 파일이나 디렉터리가 없습니다 ## port 변경되었기 때문에, 새로운 port로 접속 필요

Is the server running locally and accepting

connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[gpadmin@mdw gpseg-1]$ psql -p 5433

Timing is on.

psql (9.4.26)

Type "help" for help.

gpadmin=# \q

[gpadmin@mdw gpseg-1]$

[gpadmin@mdw gpseg-1]$ vi ~/.bashrc

export PGPORT=5433 ## port 설정이 없으면 5432접속, port 번호 변경시 PGPORT 환경 설정

[gpadmin@mdw gpseg-1]$ source ~/.bashrc

[gpadmin@mdw gpseg-1]$ psql

Timing is on.

psql (9.4.26)

Type "help" for help.

gpadmin=# show port;

port

------

5433

(1 row)

Time: 0.885 ms

gpadmin=# select count(*) From public.test;

count

-------

100

(1 row)

Time: 30.654 ms

gpadmin=# \q

6) Greenplum 상태 확인

[gpadmin@mdw gpseg-1]$ gpstate -f

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -f

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.3 build commit:0eb759d759987e82ba3bf910b89ed3057bad0416'

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.3 build commit:0eb759d759987e82ba3bf910b89ed3057bad0416) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Mar 3 2023 21:50:52'

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-Standby master details

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-----------------------

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:- Standby address = smdw

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:- Standby data directory = /data/master/gpseg-1

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:- Standby port = 5433

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:- Standby PID = 110197

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:- Standby status = Standby host passive

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:--------------------------------------------------------------

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:--pg_stat_replication

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:--------------------------------------------------------------

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-No entries found.

20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:--------------------------------------------------------------

[gpadmin@mdw gpseg-1]$ gpstate -e

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.3 build commit:0eb759d759987e82ba3bf910b89ed3057bad0416'

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.3 build commit:0eb759d759987e82ba3bf910b89ed3057bad0416) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Mar 3 2023 21:50:52'

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments...

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------

20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-All segments are running normally

[gpadmin@mdw gpseg-1]$

4. 기타 사항

1) 세그먼트 port 변경시 (broadcom knowledge 참조)

- https://knowledge.broadcom.com/external/article?articleNumber=382981

일반 유저로 pl/python 함수생성하기

Greenplum은 DB로서의 역활 뿐만 아니라 유용한 확장 패키지들이 있습니다.

이중 pl/python으로 함수를 만들면, In-DB 분석을 할 수 있을 뿐만 아니라 DB 안에서 다양한 유틸리티를 수행할 수 있습니다.

다만, pl/python으로 사용하는 경우, OS 커멘드까지 수행할 수 있어서, 슈퍼유저의 권한이 필요로 합니다.

슈퍼유저 권한 없이 일반 유저가 pl/python 을 생성/수정할 수 있도록 설정하는 방법은 아래와 같습니다.

1. 일반 유저가 pl/python 수행시 발생하는 에러

[gpadmin@r8g6single mngdb]$ psql -U udba

Timing is on.

psql (9.4.26)

Type "help" for help.

gpkrtpch=> CREATE FUNCTION pymax (a integer, b integer)

  RETURNS integer

AS $$

  if a > b:

    return a

  return b

$$ LANGUAGE plpythonu;

ERROR:  permission denied for language plpythonu

Time: 0.492 ms

gpkrtpch=>

2. pl/python 권한 주기 위한 내용 요약

    1) Greenplum에서는 plpythonu를 생성하면, lanpltrusted 필드가 false로 기본 셋팅 됩니다.

    이를 일반 유저가 사용할 수 있도록 true로 설정하면, 모든 유저가 일반 프로시저를 생성하는 것과 같이 pl/python을 사용할 수 있습니다.

 

     2) 해당 시스템 카탈로그를 업데이트하려면, 마스터 인스턴스 뿐만 아니라 모든 인스턴스에서 true로 업데이트가 필요합니다.

gpadmin=# create language plpythonu;

CREATE LANGUAGE

Time: 125.521 ms

gpadmin=# select * from pg_language;

  lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl

-----------+----------+---------+--------------+---------------+-----------+--------------+--------

 internal  |       10 | f       | f            |             0 |         0 |         2246 |

 c         |       10 | f       | f            |             0 |         0 |         2247 |

 sql       |       10 | f       | t            |             0 |         0 |         2248 |

 plpgsql   |       10 | t       | t            |         12332 |     12333 |        12334 |

 plpythonu |       10 | t       | f =>t 변경필요 |         26231 |     26232 |        26233 |

gpadmin=#

2. pl/python 권한 주기

    1) 사전 현재 환경 확인 (콘솔/gpadmin 계정) 

        $ psql -c "select * from pg_language;"

  lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl

-----------+----------+---------+--------------+---------------+-----------+--------------+--------

 internal  |       10 | f       | f            |             0 |         0 |         2246 |

 c         |       10 | f       | f            |             0 |         0 |         2247 |

 sql       |       10 | f       | t            |             0 |         0 |         2248 |

 plpgsql   |       10 | t       | t            |         12332 |     12333 |        12334 |

 plpythonu |       10 | t       | f            |         26231 |     26232 |        26233 |

    2) pl/python 권한  

         - 첨부 파일은 https://github.com/gpdbkr/gpkrutil/blob/main/mngdb/trusting_plpython.sh 에도 올려 놓았습니다.

         - trusting_plpython.sh 내용 : 모든 세그먼트에 plpythonu의 lanpltrusted 필드를 false에서 true로 업데이트

         $ su - gpadmin 

         $ export PGDATABASE=databasename          ## 현재 사용하는 databasename     

         $ sh trusting_plpython.sh

$ cat trusting_plpython.sh ## 소스 

#!/bin/bash

### This script for GPDB 6.x

### Get segments host and port

sql_segments="select hostname || ' ' || port from gp_segment_configuration where preferred_role = 'p';"

### Loop over segments

psql -Atc "${sql_segments}" | while read host port;

do

    echo "PROCESSING ${host}, ${port}";

    PGOPTIONS="-c gp_session_role=utility" psql -a -h ${host} -p ${port} <<EOF

        set allow_system_table_mods=on;

        update pg_language set lanpltrusted = true where lanname = 'plpythonu';

EOF

done

$

         

    3) pl/python 업데이트 확인 

        $ psql -c "select * from pg_language;"

  lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl

-----------+----------+---------+--------------+---------------+-----------+--------------+--------

 internal  |       10 | f       | f            |             0 |         0 |         2246 |

 c         |       10 | f       | f            |             0 |         0 |         2247 |

 sql       |       10 | f       | t            |             0 |         0 |         2248 |

 plpgsql   |       10 | t       | t            |         12332 |     12333 |        12334 |

 plpythonu |       10 | t       | t            |         26231 |     26232 |        26233 |

        

3. 일반 유저로 pl/python 함수 생성  

     1) 일반 유저로 DB 접속

         $ psql -U 일반유저 

[gpadmin@r8g6single mngdb]$ psql -U udba

Timing is on.

psql (9.4.26)

Type "help" for help.

gpkrtpch=> CREATE FUNCTION pymax (a integer, b integer)

  RETURNS integer

AS $$

  if a > b:

    return a

  return b

$$ LANGUAGE plpythonu;

CREATE FUNCTION

Time: 53.240 ms

gpkrtpch=> gpkrtpch=> select pymax(1, 2);

 pymax
-------
     2
(1 row)

Time: 11.096 ms
gpkrtpch=>

Greenplum Disaster Recovery

Greenplum DR를 사용하면, 재해 발생 전 특정 복구 시점으로 복구 지원

Greenplum DR은 Full 백업/복구, Incremental 백업/복구, WAL 로그 기반으로 DR 기능 제공

Greenplum Disaster Recovery 지원 버전

- Greenplum 6.27.1+ (DR 클러스터에서 조회 기능 미제공)

- Greenplum 7.3.0+ (DR 클러스터에서 조회 기능 제공)

관련 자료

- Greenplum DR 메뉴얼 

  https://docs.vmware.com/en/VMware-Greenplum-Disaster-Recovery/index.html

- Greenplum DR 수행 Test: 

  https://github.com/gpdbkr/GreenplumDR

- Greenplum DR 데모

  https://www.youtube.com/watch?v=blD7gGcSCMk

Greenplum Disaster Recovery 소개

Greenplum 7 커버링 인덱스

 Greenplum 7에서는 Convering 인덱스를 제공

Covering 인덱스는 인덱스 컬럼 이외에 자주 Access되는 컬럼을 추가하여 INDEX ONLY Scan 되도록 수행

테스트 결과,

조회 조건이 INDEX 컬럼과 조회 컬럼을 같이 있을 경우 INDEX ONLY scan으로 플랜이 만들어지나,

필터링 조건이 추가될때 INDEX ONLY scan이 아닌 INDEX scan으로 플랜 생성

성능적인 측면에서는 테스트 데이터 사이즈가 작기 때문에 효과는 미지수이나,

대용량 데이터 처리시에는 추가 테스트가 필요 함.

1. 세션 파라미터 확인 - DEFAULT 설정일 경우

SHOW optimizer; --ON

SHOW enable_indexonlyscan; --ON

SHOW optimizer_enable_dynamicindexonlyscan; --ON

SHOW optimizer_enable_indexonlyscan; --ON

2. 마스터성 테이블일 경우 커버링 인덱스 테스트

DROP TABLE IF EXISTS public.customer;

CREATE TABLE public.customer

(

cust_no int,

cust_nm TEXT,

gender varchar(1),

status_yn varchar(1)

)

WITH (appendonly=TRUE, compresstype=zstd, compresslevel=7)

DISTRIBUTED BY (cust_no)

;

INSERT INTO public.customer

SELECT i, 'cust_nm_'||trim(to_char(i, '000'))

, CASE WHEN (i % 2) = 0 THEN 'M' ELSE 'F' END gender

, CASE WHEN (i % 50) = 0 THEN 'N' ELSE 'Y' END status_yn

FROM generate_series(1, 10000) i

;

--일반 인덱스 테스트

DROP INDEX IF EXISTS public.ixu_customer_cust_no;

CREATE UNIQUE INDEX ixu_customer_cust_no ON public.customer(cust_no);

EXPLAIN ANALYZE

SELECT cust_no, cust_nm

FROM public.customer

WHERE cust_no = 100;

QUERY PLAN |

---------------------------------------------------------------------------------------------------------------------------------+

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..387.96 rows=1 width=16) (actual time=0.857..0.858 rows=1 loops=1) |

-> Bitmap Heap Scan on customer (cost=0.00..387.96 rows=1 width=16) (actual time=0.292..0.294 rows=1 loops=1) |

Recheck Cond: (cust_no = 100) |

Heap Blocks: exact=1 |

-> Bitmap Index Scan on ixu_customer_cust_no (cost=0.00..0.00 rows=0 width=0) (actual time=0.081..0.082 rows=1 loops=1)|

Index Cond: (cust_no = 100) |

Optimizer: GPORCA |

Planning Time: 2.613 ms |

(slice0) Executor memory: 52K bytes. |

(slice1) Executor memory: 368K bytes (seg7). |

Memory used: 128000kB |

Execution Time: 1.585 ms |

--Covering 인덱스 테스트

DROP INDEX IF EXISTS public.ixu_customer_cust_no;

DROP INDEX IF EXISTS public.ixu_customer_cust_no_covering;

CREATE UNIQUE INDEX ixu_customer_cust_no_covering ON public.customer(cust_no) INCLUDE (cust_nm);

--필 조건에 인덱스 컬럼과 조회 컬럼에 including 컬럼이 추가될 때 index only scan 수행

EXPLAIN ANALYZE

SELECT cust_no, cust_nm

FROM public.customer

WHERE cust_no = 100;

QUERY PLAN |

--------------------------------------------------------------------------------------------------------------------------------------------------+

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=16) (actual time=0.794..0.796 rows=1 loops=1) |

-> Index Only Scan using ixu_customer_cust_no_covering on customer (cost=0.00..6.00 rows=1 width=16) (actual time=0.159..0.160 rows=1 loops=1)|

Index Cond: (cust_no = 100) |

Heap Fetches: 0 |

Optimizer: GPORCA |

Planning Time: 2.410 ms |

(slice0) Executor memory: 14K bytes. |

(slice1) Executor memory: 147K bytes (seg7). |

Memory used: 128000kB |

Execution Time: 1.447 ms |

--필터 조건에 커버링 인덱스이외의 컬럼이 있을 경우 index only scan이 수행되지 않음. - 일반 index 스캔 수행

EXPLAIN ANALYZE

SELECT cust_no, cust_nm

FROM public.customer

WHERE cust_no = 100

AND status_yn = 'Y' ;

QUERY PLAN |

------------------------------------------------------------------------------------------------------------------------------------------------+

Result (cost=0.00..387.96 rows=1 width=16) (actual time=0.738..0.739 rows=0 loops=1) |

-> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..387.96 rows=1 width=16) (actual time=0.738..0.738 rows=0 loops=1) |

-> Bitmap Heap Scan on customer (cost=0.00..387.96 rows=1 width=16) (actual time=0.000..0.157 rows=0 loops=1) |

Recheck Cond: (cust_no = 100) |

Filter: ((status_yn)::text = 'Y'::text) |

-> Bitmap Index Scan on ixu_customer_cust_no_covering (cost=0.00..0.00 rows=0 width=0) (actual time=0.009..0.009 rows=1 loops=1)|

Index Cond: (cust_no = 100) |

Optimizer: GPORCA |

Planning Time: 3.510 ms |

(slice0) Executor memory: 57K bytes. |

(slice1) Executor memory: 370K bytes (seg7). |

Memory used: 128000kB |

Execution Time: 1.367 ms

3. 이력성 파티션 테이블에서의 커버링 인덱스 테스트

--커버링 인덱스 컬럼 이외의 조건 추가시 일반 index 스캔 수행 (index only scan 수행되지 않음.)

DROP TABLE IF EXISTS public.order_log;

CREATE TABLE public.order_log

(

order_no int,

cust_no int,

prod_nm TEXT,

order_date varchar(8),

order_amt int

)

WITH (appendonly=TRUE, compresstype=zstd, compresslevel=7)

DISTRIBUTED BY (order_no)

PARTITION BY RANGE (order_date)

(

PARTITION p2001 start('20010101') END ('20020101'),

PARTITION p2002 start('20020101') END ('20030101'),

PARTITION p2003 start('20030101') END ('20040101'),

PARTITION p2004 start('20040101') END ('20050101'),

PARTITION p2005 start('20050101') END ('20060101'),

PARTITION p2006 start('20060101') END ('20070101'),

PARTITION p2007 start('20070101') END ('20080101'),

PARTITION p2008 start('20080101') END ('20090101'),

PARTITION p2009 start('20090101') END ('20100101'),

PARTITION p2010 start('20100101') END ('20110101')

)

;

INSERT INTO public.order_log

SELECT i*j order_no

, j%100 cust_no

, 'prod_'||trim(to_char(i%50, '00000')) prod_nm

, to_char('2001-01-01'::date + i, 'yyyymmdd') order_dt

, round(random()*100)::int * 100 sys_dt

FROM generate_series(1, 364) i

, generate_series(1, 10000) j

;

DROP INDEX IF EXISTS public.ixu_order_log_cust_no;

DROP INDEX IF EXISTS public.ixu_order_log_cust_no_convering;

CREATE INDEX ixu_order_log_cust_no ON public.order_log(cust_no);

ANALYZE public.order_log;

SET optimizer =ON;

EXPLAIN ANALYZE

SELECT sum(order_amt)

FROM public.order_log

WHERE cust_no = 1

AND order_date >= '20010101'

AND order_date < '20020101'

;

QUERY PLAN |

---------------------------------------------------------------------------------------------------------------------------------------------------------+

Finalize Aggregate (cost=0.00..418.62 rows=1 width=8) (actual time=35.349..35.351 rows=1 loops=1) |

-> Gather Motion 8:1 (slice1; segments: 8) (cost=0.00..418.62 rows=1 width=8) (actual time=29.441..35.328 rows=8 loops=1) |

-> Partial Aggregate (cost=0.00..418.62 rows=1 width=8) (actual time=35.095..35.096 rows=1 loops=1) |

-> Dynamic Bitmap Heap Scan on order_log (cost=0.00..418.62 rows=1273 width=4) (actual time=1.889..28.300 rows=4680 loops=1) |

Number of partitions to scan: 1 (out of 10) |

Recheck Cond: (cust_no = 1) |

Filter: ((cust_no = 1) AND ((order_date)::text >= '20010101'::text) AND ((order_date)::text < '20020101'::text)) |

Heap Blocks: exact=1 |

Partitions scanned: Avg 1.0 x 8 workers. Max 1 parts (seg0). |

-> Dynamic Bitmap Index Scan on ixu_order_log_cust_no (cost=0.00..0.00 rows=0 width=0) (actual time=1.274..1.275 rows=4680 loops=1)|

Index Cond: (cust_no = 1) |

Optimizer: GPORCA |

Planning Time: 20.110 ms |

(slice0) Executor memory: 34K bytes. |

(slice1) Executor memory: 2434K bytes avg x 8 workers, 2434K bytes max (seg0). |

Memory used: 128000kB |

Execution Time: 36.671 ms |

SET optimizer =OFF;

EXPLAIN ANALYZE

SELECT sum(order_amt)

FROM public.order_log

WHERE cust_no = 1

AND order_date >= '20010101'

AND order_date < '20020101'

;

QUERY PLAN |

------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Finalize Aggregate (cost=590.62..590.63 rows=1 width=8) (actual time=33.021..33.023 rows=1 loops=1) |

-> Gather Motion 8:1 (slice1; segments: 8) (cost=590.50..590.60 rows=8 width=8) (actual time=25.173..32.979 rows=8 loops=1) |

-> Partial Aggregate (cost=590.50..590.51 rows=1 width=8) (actual time=31.201..31.202 rows=1 loops=1) |

-> Bitmap Heap Scan on order_log_1_prt_p2001 (cost=371.02..579.31 rows=4474 width=4) (actual time=0.858..28.146 rows=4680 loops=1) |

Recheck Cond: (cust_no = 1) |

Filter: (((order_date)::text >= '20010101'::text) AND ((order_date)::text < '20020101'::text)) |

Heap Blocks: exact=14 |

-> Bitmap Index Scan on order_log_1_prt_p2001_cust_no_idx (cost=0.00..369.90 rows=4474 width=0) (actual time=0.439..0.439 rows=4680 loops=1)|

Index Cond: (cust_no = 1) |

Optimizer: Postgres-based planner |

Planning Time: 0.295 ms |

(slice0) Executor memory: 69K bytes. |

(slice1) Executor memory: 2430K bytes avg x 8 workers, 2430K bytes max (seg0). |

Memory used: 128000kB |

Execution Time: 34.320 ms

--------###################

DROP INDEX IF EXISTS public.ixu_order_log_cust_no;

DROP INDEX IF EXISTS public.ixu_order_log_cust_no_convering;

CREATE INDEX ixu_order_log_cust_no_convering ON public.order_log(cust_no) INCLUDE(order_amt);

ANALYZE public.order_log;

SET optimizer =ON;

EXPLAIN ANALYZE

SELECT sum(order_amt)

FROM public.order_log

WHERE cust_no = 1

AND order_date >= '20010101'

AND order_date < '20020101'

;

QUERY PLAN |

-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Finalize Aggregate (cost=0.00..415.71 rows=1 width=8) (actual time=48.383..48.386 rows=1 loops=1) |

-> Gather Motion 8:1 (slice1; segments: 8) (cost=0.00..415.71 rows=1 width=8) (actual time=43.469..48.363 rows=8 loops=1) |

-> Partial Aggregate (cost=0.00..415.71 rows=1 width=8) (actual time=47.047..47.049 rows=1 loops=1) |

-> Dynamic Bitmap Heap Scan on order_log (cost=0.00..415.71 rows=1152 width=4) (actual time=2.796..47.208 rows=4680 loops=1) |

Number of partitions to scan: 1 (out of 10) |

Recheck Cond: (cust_no = 1) |

Filter: ((cust_no = 1) AND ((order_date)::text >= '20010101'::text) AND ((order_date)::text < '20020101'::text)) |

Heap Blocks: exact=1 |

Partitions scanned: Avg 1.0 x 8 workers. Max 1 parts (seg0). |

-> Dynamic Bitmap Index Scan on ixu_order_log_cust_no_convering (cost=0.00..0.00 rows=0 width=0) (actual time=2.265..2.266 rows=4680 loops=1)|

Index Cond: (cust_no = 1) |

Optimizer: GPORCA |

Planning Time: 30.877 ms |

(slice0) Executor memory: 34K bytes. |

(slice1) Executor memory: 2434K bytes avg x 8 workers, 2434K bytes max (seg0). |

Memory used: 128000kB |

Execution Time: 49.816 ms

SET optimizer =off;

EXPLAIN ANALYZE

SELECT sum(order_amt)

FROM public.order_log

WHERE cust_no = 1

AND order_date >= '20010101'

AND order_date < '20020101'

;

QUERY PLAN |

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Finalize Aggregate (cost=547.54..547.55 rows=1 width=8) (actual time=46.434..46.435 rows=1 loops=1) |

-> Gather Motion 8:1 (slice1; segments: 8) (cost=547.42..547.52 rows=8 width=8) (actual time=31.647..46.368 rows=8 loops=1) |

-> Partial Aggregate (cost=547.42..547.43 rows=1 width=8) (actual time=34.170..34.171 rows=1 loops=1) |

-> Bitmap Heap Scan on order_log_1_prt_p2001 (cost=337.43..537.30 rows=4049 width=4) (actual time=0.875..39.432 rows=4680 loops=1) |

Recheck Cond: (cust_no = 1) |

Filter: (((order_date)::text >= '20010101'::text) AND ((order_date)::text < '20020101'::text)) |

Heap Blocks: exact=14 |

-> Bitmap Index Scan on order_log_1_prt_p2001_cust_no_order_amt_idx (cost=0.00..336.42 rows=4049 width=0) (actual time=0.526..0.526 rows=4680 loops=1)|

Index Cond: (cust_no = 1) |

Optimizer: Postgres-based planner |

Planning Time: 0.201 ms |

(slice0) Executor memory: 69K bytes. |

(slice1) Executor memory: 2430K bytes avg x 8 workers, 2430K bytes max (seg0). |

Memory used: 128000kB |

Execution Time: 48.352 ms |