Greenplum 7.5에서 Analyze 기능개선

1. analyze 개선 사항

- Analyze 수행 후 테이블에 변경이 없을 경우, Analyze를 자동 skip

- 개선 버전: Greenplum 7.5+

2. Greenplum 버전별 analyzedb, analyze 의 skip 적용 범위

구분 | analyzedb | analyze

----------------------------------------------------

AO/CO 테이블 | O | O

Heap 테이블 | X | O

Materialized View(MV) | X | O

파티션 테이블 | O | O

Root 통계 skip | O | O

3. 테스트 결과

1) Greenplum 7.4.1

gpadmin=# select version();

version

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

PostgreSQL 12.12 (Greenplum Database 7.4.1 build commit:2999e4235c947b8339a7351af0d760a405821a9b) on x86_64-pc-li

nux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit compiled on Apr 10 2025 01:41:02 Bhuvnes

h C.

(1 row)

Time: 6.032 ms

gpadmin=# \dt+ public.order_log*

List of relations

Schema | Name | Type | Owner | Storage | Size | Description

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

public | order_log | partitioned table | gpadmin | ao_row | 0 bytes |

public | order_log_1_prt_p2001 | table | gpadmin | ao_row | 152 MB |

public | order_log_1_prt_p2002 | table | gpadmin | ao_row | 152 MB |

public | order_log_1_prt_p2003 | table | gpadmin | ao_row | 152 MB |

public | order_log_1_prt_p2004 | table | gpadmin | ao_row | 448 kB |

public | order_log_1_prt_p2005 | table | gpadmin | ao_row | 448 kB |

public | order_log_1_prt_p2006 | table | gpadmin | ao_row | 448 kB |

public | order_log_1_prt_p2007 | table | gpadmin | ao_row | 448 kB |

public | order_log_1_prt_p2008 | table | gpadmin | ao_row | 448 kB |

public | order_log_1_prt_p2009 | table | gpadmin | ao_row | 448 kB |

gpadmin=# analyze public.order_log;

ANALYZE

Time: 2596.419 ms (00:02.596)

gpadmin=# analyze public.order_log; --<<<<<<< 테이블 변경없음에도 불구하고 anlyze 를 수행하면, 실제 다시 재수행 됨.

ANALYZE

Time: 2479.870 ms (00:02.480)

2) Greenplum 7.5

gpadmin=# select version();

version

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

PostgreSQL 12.22 (Greenplum Database 7.5.1 build commit:9d50a8112ee52cab602caa48562c4045fc66c86a) on x86_64-pc-li

nux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit compiled on Jun 10 2025 07:31:20 Bhuvnes

h C.

(1 row)

Time: 31.014 ms

gpadmin=# analyze public.order_log; --<< DB 업그레이드 이전에 통계 정보가 있고, 테이블이 변경되지 않았기 때문에, 내부적으로 anlyze skip

ANALYZE

Time: 78.057 ms

--특정 파티션에만 데이터 적재시, 해당 파티션만 anlyze 재수행

gpadmin=# insert into public.order_log_1_prt_p2009 (order_id, cust_id, prod_nm, order_date) values (1, 1, 'P1', '2009-01-11');

INSERT 0 1

Time: 20.505 ms

gpadmin=# analyze public.order_log;

ANALYZE

Time: 33.510 ms

gpadmin=# analyze public.order_log;

ANALYZE

Time: 26.783 ms

gpadmin=# analyze public.order_log;

ANALYZE

Time: 26.707 ms

gpadmin=#

--파티션이 아닌 테이블일 경우, 해당 테이블에 대해서는 anlyze 재수행 됨.

gpadmin=# insert into public.order_log_idx select * from public.order_log_idx limit 1;

INSERT 0 1

Time: 35.709 ms

gpadmin=# analyze public.order_log_idx;

ANALYZE

Time: 5287.658 ms (00:05.288)