ERROR: operator does not exist: text <= unknown
오류: 지정된 이름 및 인자 형식과 일치하는 연산자가 없습니다. <=
PostgreSQL 은 암묵적 형변환을 8.3 부터 크게 줄였다. 다른 엔진에서는 문자열 컬럼과 날짜 리터럴을 비교해도 알아서 맞춰 주지만 PostgreSQL 은 거부한다. 원인은 대개 적재할 때 날짜 컬럼을 text 로 만든 것이다.
임시로는 캐스팅한다.
WHERE l_shipdate::date <= DATE '1998-09-02'
다만 컬럼에 함수나 캐스팅을 씌우면 그 컬럼의 일반 인덱스를 쓰지 못한다. 근본 해결은 컬럼 타입을 고치는 것이다.
ALTER TABLE lineitem
ALTER COLUMN l_shipdate TYPE date USING l_shipdate::date;
당장 바꿀 수 없다면 표현식 인덱스를 만든다.
CREATE INDEX idx_lineitem_shipdate ON lineitem ((l_shipdate::date));
TPC-H 스키마를 만들 때 l_shipdate · o_orderdate 는 date, 금액 컬럼은 numeric(15,2) 로 정의하는 것이 원 명세에 맞다. double precision 으로 만들면 합계가 반올림 오차로 기준 결과와 어긋난다.
-- 다른 엔진의 date_add / dateadd 대신
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90 day'
-- YEAR() 함수는 없다
SELECT EXTRACT(YEAR FROM o_orderdate) AS o_year FROM orders;
SELECT DATE_PART('year', o_orderdate) FROM orders;
EXTRACT 는 PostgreSQL 14 부터 numeric 을 돌려준다. 정수로 쓰려면 EXTRACT(YEAR FROM d)::int 로 캐스팅한다.
연 단위로 묶을 때는 함수를 씌우는 대신 범위 조건으로 쓰면 인덱스를 탄다.
WHERE o_orderdate >= DATE '1995-01-01' AND o_orderdate < DATE '1996-01-01'
| 다른 엔진 | PostgreSQL |
|---|---|
NVL(a, b) |
COALESCE(a, b) |
DECODE(a, 1, 'x', 'y') |
CASE WHEN a = 1 THEN 'x' ELSE 'y' END |
SUBSTR(s, 1, 2) |
SUBSTRING(s FROM 1 FOR 2) 또는 SUBSTR 그대로 |
\|\| 로 숫자 연결 |
한쪽을 ::text 로 캐스팅해야 한다 |
LIMIT n 뒤 FETCH FIRST |
둘 다 된다 |
결과가 화면 단위로 끊겨 넘어가는 것은 pager 때문이다.
\pset pager off
행 수 제한은 psql 에 없다. 쿼리의 LIMIT 이 없으면 전부 받아 온다. 큰 결과를 확인만 할 때는 파일로 보낸다.
\o /tmp/result.txt
\timing on
셸에서는 다음과 같이 쓴다.
psql -U appuser -d tpch -P pager=off -f q01.sql -o /tmp/q01.out
PostgreSQL 에는 MySQL 의 RESET QUERY CACHE 같은 명령이 없다. 쿼리 결과를 캐시하지 않기 때문이다. 벤치마크에서 비워야 하는 것은 shared buffers 와 OS 페이지 캐시다.
systemctl stop postgresql-17
sync
echo 3 > /proc/sys/vm/drop_caches
systemctl start postgresql-17
drop_caches 는 다른 서비스의 캐시까지 버리므로 전용 장비에서만 쓴다. 세션 단위 상태만 지우려면 다음을 쓴다.
DISCARD ALL;
반대로 캐시를 데워 두고 재려면 pg_prewarm 확장을 쓴다.
CREATE EXTENSION IF NOT EXISTS pg_prewarm;
SELECT pg_prewarm('lineitem');
실행 계획과 실제 소요를 함께 보려면 다음을 쓴다.
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;