5-4-2. 부분 인덱스

-- [SQL-5-4-2-a]

SELECT  t1.*
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_st = 'PREP'
AND     t1.shop_id = 'S100'
ORDER BY t1.ord_dtm ASC;
SQL
복사

-- [SQL-5-4-2-b]

SELECT  t1.ord_st, count(*) cnt
FROM    startdbpg.tr_ord_big t1
GROUP BY t1.ord_st
ORDER BY 2 DESC;
SQL
복사

-- [SQL-5-4-2-c]: 인덱스 생성 후 [SQL-5-4-2-a]를 재실행
CREATE INDEX tr_ord_big_x07 ON startdbpg.tr_ord_big(shop_id) WHERE ord_st = 'PREP';


-- [SQL-5-4-2-a]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.*
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_st = 'PREP'
AND     t1.shop_id = 'S100'
ORDER BY t1.ord_dtm ASC;
SQL
복사

-- [SQL-5-4-2-d]

SELECT  t1.*
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_st = 'MFGC' -- > ord_st가 PREP가 아니므로 x07 인덱스 사용 불가
AND     t1.shop_id = 'S100'
ORDER BY t1.ord_dtm ASC;
SQL
복사

-- [SQL-5-4-2-e]

SELECT  REPLACE(t1.indexname,t1.tablename||'_' ,'') ix
        ,pg_size_pretty(pg_relation_size((t1.schemaname || '.' || t1.indexname)::regclass)) ix_size
        ,t1.indexdef
FROM    pg_indexes t1
WHERE   t1.tablename = 'tr_ord_big'
ORDER BY t1.indexname;
SQL
복사