모든 상황에 대해 다 던져주고 최적화(튜닝)를 해달라거나 하는 범위가 넓은 질문과 범위가 넓은 답변을 원하면 chatgpt로 빠르게 답을 구할 수가 없다.
인덱스가 걸린 varchar 컬럼으로 공백이 아닌 것을 <> ''과 > '' 차이에 대해 알고 있는 건 확인해서, 얘가 어느 정도 할 수 있다는 건 알았으니 어떤 프롬프트를 써야 실무에 사용하기 편할 지 알아보려고 이래저래 해본 걸 정리했다.
일단 chatgpt가 테이블 생성문에서 인덱스 생성한 컬럼이 뭔지 알아낼 수 있나를 확인해봤다.
질문
CREATE TABLE t_datasource_tag (
tag_no bigint DEFAULT nextval('seq_datasource_tag'::regclass) NOT NULL,
table_name character varying(100) NOT NULL,
database character varying(100) DEFAULT ''::character varying NOT NULL,
server_type character varying(50) DEFAULT ''::character varying NOT NULL,
collect bigint DEFAULT 0 NOT NULL,
tag character varying(100) NOT NULL,
user_id character varying(50) DEFAULT ''::character varying NOT NULL
);
ALTER TABLE ONLY t_datasource_tag
ADD CONSTRAINT pk_datasource_tag PRIMARY KEY (tag_no);
ALTER TABLE ONLY t_datasource_tag
ADD CONSTRAINT uniq_datasource_tag__1 UNIQUE (table_name, database, server_type, collect, tag);
CREATE INDEX ixnn_t_datasource_tag__concatenated1 ON t_datasource_tag USING btree (table_name, database, server_type);
CREATE INDEX ixnn_t_datasource_tag__concatenated2 ON t_datasource_tag USING btree (collect);
CREATE INDEX ixnn_t_datasource_tag__concatenated3 ON t_datasource_tag USING btree (tag);
위에는 postgresql을 사용해서 만든 테이블이야. 이 중에 내가 인덱스를 생성한 컬럼이 뭔지 알려줘.답변
t_datasource_tag 테이블에서 생성된 인덱스의 컬럼은 다음과 같습니다:
ixnn_t_datasource_tag__concatenated1: table_name, database, server_type
ixnn_t_datasource_tag__concatenated2: collect
ixnn_t_datasource_tag__concatenated3: tag
테이블 생성문을 보면 pk와 uk가 있지만, 나는 '인덱스'라고 물어봤기에 chatgpt는 인덱스만 알려줬다. pk, uk, fk도 인덱스로 활용할 수 있기에 이 부분도 물어봤다.
질문
CREATE TABLE t_datasource_tag (
tag_no bigint DEFAULT nextval('seq_datasource_tag'::regclass) NOT NULL,
table_name character varying(100) NOT NULL,
database character varying(100) DEFAULT ''::character varying NOT NULL,
server_type character varying(50) DEFAULT ''::character varying NOT NULL,
collect bigint DEFAULT 0 NOT NULL,
tag character varying(100) NOT NULL,
user_id character varying(50) DEFAULT ''::character varying NOT NULL
);
ALTER TABLE ONLY t_datasource_tag
ADD CONSTRAINT pk_datasource_tag PRIMARY KEY (tag_no);
ALTER TABLE ONLY t_datasource_tag
ADD CONSTRAINT uniq_datasource_tag__1 UNIQUE (table_name, database, server_type, collect, tag);
CREATE INDEX ixnn_t_datasource_tag__concatenated1 ON t_datasource_tag USING btree (table_name, database, server_type);
CREATE INDEX ixnn_t_datasource_tag__concatenated2 ON t_datasource_tag USING btree (collect);
CREATE INDEX ixnn_t_datasource_tag__concatenated3 ON t_datasource_tag USING btree (tag);
위에는 postgresql을 사용해서 만든 테이블이야. 이 중에 인덱스를 사용하는 컬럼과 pk, uk, fk에 해당하는 컬럼을 알려줘.답변
인덱스를 사용하는 컬럼:
table_name
database
server_type
collect
tag
기본키(PK)에 해당하는 컬럼:
tag_no
고유제약조건(Unique constraint, UK)에 해당하는 컬럼:
table_name
database
server_type
collect
tag
외래키(Foreign key, FK)는 이 테이블에서 정의되지 않았습니다.
처음에는 '인덱스로 활용 가능한 컬럼을 알려줘'라고 했는데, 기존과 똑같이 인덱스가 걸린 것들만 반환을 해주었다. 그래서 pk, uk, fk라고 명확하게 지정을 해주었다.
근데 얘가 몰라서 이렇게 대답을 했을까?
질문
rdbms에서 pk, uk, fk를 인덱스로 쓸 수 있어?답변
RDBMS에서 PK, UK 및 FK를 인덱스로 사용할 수 있습니다. 사실, 이러한 제약조건은 일반적으로 인덱스를 생성하여 검색 및 조인 성능을 향상시키기 위해 사용됩니다.
기본키(PK) 및 고유 제약조건(Unique constraint, UK)은 RDBMS에서 내부적으로 유일한 값으로 인덱스를 생성합니다. 이 인덱스는 해당 테이블의 모든 행에 대해 생성되며, 검색 및 연결 작업에서 매우 빠르게 사용할 수 있습니다.
외래키(FK)는 다른 테이블의 기본키(PK) 또는 고유 제약조건(UK)과 관련되어 있습니다. 따라서 FK는 다른 테이블의 기본키(PK) 또는 고유 제약조건(UK)과 매칭되는 인덱스를 생성하여 검색 및 연결 작업에서 빠르게 사용할 수 있습니다.
하지만 FK 인덱스는 FK 제약 조건에 의해 자동으로 생성되지는 않으며, 따라서 FK 인덱스를 수동으로 생성해야 할 수도 있습니다.
아니다. 매우 잘 알고 있다. 오히려 fk를 인덱스와 동일하게 여기고 쓰면 안 되는 것도 잘 알고 있다.
막상 질문을 하려니 모든 테이블 생성문과 질문을 같이 하면 제한 길이 때문인지 오류가 났고, 나눠서 해서 그런 건지 인덱스를 생성한 컬럼에 인덱스를 생성하라고 해서 테이블별로 나눠서 질문을 했다.
허나 인덱스가 생성된 컬럼임에도 생성을 하라던가 인덱스를 쓸 수 있는 조건으로 변경을 하지 않는다던가... 여튼 원하는 답을 받을 수는 없었다.
질문한 질의문은 아래와 같고, 프롬프트는
'위에는 postgresql을 사용해서 만든 테이블이야. 위 테이블에 인덱스를 사용하는 컬럼과 pk, uk, fk에 해당하는 컬럼들을 참고해서 아래 질의문에 인덱스를 사용할 수 있음에도 사용하지 않은 부분을 수정해줘.'
이라고 질문을 했다.
SELECT
c.collect_no AS key,
cd.value AS approval_process,
cd.key AS approval_process_key,
COALESCE(array_agg(tag.tag), '{}') AS category,
CASE WHEN c.grade IS NULL OR c.grade = 0 THEN ''
ELSE c.grade::text
end AS grade,
c.title AS title,
COALESCE(c.index, '') AS index,
COALESCE(c.uuid, '') AS uuid,
c.target_host_count AS target_host_count,
u.user_id AS user_id,
u.user_name||'('||u.user_id||')' AS user,
TO_CHAR(c.created_at, 'YYYY.MM.DD HH24:mi:ss') AS created_at,
COALESCE(TO_CHAR(c.updated_at, 'YYYY.MM.DD HH24:mi:ss'), '') AS updated_at,
COALESCE(c.cluster_list, '{}') AS cluster_list,
c.use_daily_index AS use_daily_index,
c.storage AS storage,
c.log_type AS log_type,
c.org_ids AS org_ids
FROM
t_collect_detail c
INNER JOIN t_code cd ON (c.ap_key = cd.key)
INNER JOIN t_user u ON (c.user_id = u.user_id)
LEFT JOIN t_datasource_tag tag ON (c.collect_no = tag.collect)
WHERE
cd.key <> ''
AND c.user_id <> ''
AND tag.collect <> 0
AND u.user_id > ''
GROUP BY
c.collect_no,
cd.value,
cd.key,
c.grade,
c.title,
c.index,
c.uuid,
c.target_host_count,
u.user_id,
u.user_name,
c.created_at,
c.updated_at,
c.cluster_list,
c.use_daily_index,
c.storage,
c.log_type,
c.org_ids보면 WHERE에 있는 u.user_id > ''을 제외하곤 전부 인덱스 사용하지 않는 조건이다.
전에도 <> '' 에 대한 테스트를 했지만, 이건 그다지 의미가 없는지 chatgpt가 그냥 냅뒀는데, 이번에는 좀 이상한 답변이 있었다.
tag.collect <> 0바로 이 조건인데, collect bigint DEFAULT 0 NOT NULL 인 걸 인식을 못 했는지 인덱스를 생성하고
tag.collect IS NOT NULLIS NOT NULL로 변경해서 인덱스를 사용하라는 것이었다.
rdbms나 엔진마다 다르겠으나 나는 일단 postgresql이라고 정의를 해줬기 때문에 NULL이 인덱스로 쓸 수 있다는 걸 인식을 한 거 같았는데, 이걸 추천해주는 건 이해가 안 됐다.
0이 아닌 것과 NULL이 아닌 건 전혀 다른 조건이니까. 게다가 postgresql은 NULL이 인덱스 순서 중 마지막이라 인덱스를 생성해야 하는 컬럼에 굳이 NULL을 넣어야 하는 컬럼이 아니면 안 넣는 게 낫다.
아마 내가 질문을 '인덱스를 사용할 수 있음에도 사용하지 않은 부분을 수정해줘'라고 해서 인덱스에 대한 것만 답변을 한 것 같았다.
그래서 얘가 뭘 알긴 아는 거 같은데 싶어서 이래저래 물어보다가 아래와 같은 답을 받았다.
PostgreSQL은 NULL 값을 인덱스 키로 사용할 수 있습니다. 인덱스는 NULL 값도 처리할 수 있으며, 일반적으로 인덱스 키에 대한 NULL 값을 갖는 행도 인덱스에 저장됩니다.
그러나 NULL 값을 인덱스 키로 사용하는 경우, 일부 쿼리에서 예기치 않은 결과를 초래할 수 있습니다. 예를 들어, 다음과 같은 쿼리를 고려해 봅시다.
SELECT * FROM my_table WHERE my_column = NULL;
위의 쿼리는 인덱스를 사용하여 검색할 수 없습니다. 이는 NULL 값을 포함한 행이 모두 인덱스에 저장되기 때문입니다. 따라서 이 쿼리는 테이블의 전체 행을 검색하여 결과를 반환합니다.
대신, 다음과 같이 IS NULL을 사용하여 NULL 값을 검색하는 것이 좋습니다.
SELECT * FROM my_table WHERE my_column IS NULL;
위의 쿼리는 인덱스를 사용하여 검색할 수 있으며, NULL 값을 갖는 행만 반환됩니다.
결론적으로, PostgreSQL에서 NULL 값을 인덱스 키로 사용할 수 있지만, 해당 인덱스를 사용하는 쿼리를 작성할 때는 NULL 값을 검색하는 방법에 유의해야 합니다.많이 알긴 안다. 문제는 이걸 어떻게 결과물처럼 사용할 수 있냐 같다. 결국 이것도 삽질만 하고 끝....
쿼리 튜닝은 무리 같아서 질의문에 대한 것도 해보려다가 마땅히 할만한 게 없어서 검색해보니
https://towardsdatascience.com/can-chatgpt-write-better-sql-than-a-data-analyst-f079518efab2
역시 사람 생각은 다 비슷한 것 같다. 모르는 사람이 도움 받아서 하겠다면 아주 좋은 수단인 것 맞지만, 이걸 그대로 실무에 쓰기엔 무리가 있다.