#title PostgreSQL 데이터액세스해보기 [[TableOfContents]] ==== 조회하기 ==== PostgreSQL은 ANSI-SQL(SQL92)에 맞게 설계되었다. 그러므로 ANSI-SQL에서 이야기하는 정보스키마뷰를 제공한다. {{{ c:\>psql UserDB yasi Password: Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit UserDB=> \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+------- public | barcode | table | yasi public | customer | table | yasi public | item | table | yasi public | orderinfo | table | yasi public | orderline | table | yasi public | stock | table | yasi (6 rows) UserDB=> select table_catalog, table_schema, table_name UserDB-> from information_schema.tables UserDB-> where table_type = 'BASE TABLE' UserDB-> and table_schema = 'public'; table_catalog | table_schema | table_name ---------------+--------------+------------ UserDB | public | customer UserDB | public | item UserDB | public | orderinfo UserDB | public | stock UserDB | public | orderline UserDB | public | barcode (6 rows) UserDB=> select table_catalog, table_schema, table_name UserDB-> from information_schema.tables UserDB-> where table_type = 'base table' UserDB-> and table_schema = 'public'; table_catalog | table_schema | table_name ---------------+--------------+------------ (0 rows) UserDB=> }}} 위 내용을 보면 psql명령어로써 \dt를 하면 사용자테이블에 대한 목록을 볼 수 있다. 또다른 방법으로 information_schema.tables 뷰를 이용하면 테이블에 목록을 볼 수 있다. 위 예제로 볼 때 where 절에 대한 조건으로 대소문자를 구분하는 것을 알 수 있다. 또한 문의 끝에 세미콜론(;)을 붙인 것을 볼 수 있다. 이것은 하나의 문장이 끝났다는 것을 의미한다. MSSQL Server의 Go의 의미이다. 또한 psql에서는 \g를 입력해도 세미콜론의 효과를 가질 수 있다. {{{ UserDB=> select * from item; item_id | description | cost_price | sell_price ---------+---------------+------------+------------ 1 | Wood Puzzle | 15.23 | 21.95 2 | Rubic Cube | 7.45 | 11.49 3 | Linux CD | 1.99 | 2.49 4 | Tissues | 2.11 | 3.99 5 | Picture Frame | 7.54 | 9.95 6 | Fan Small | 9.23 | 15.75 7 | Fan Large | 13.36 | 19.95 8 | Toothbrush | 0.75 | 1.45 9 | Roman Coin | 2.34 | 2.45 10 | Carrier Bag | 0.01 | 0.00 11 | Speakers | 19.73 | 25.32 (11 rows) UserDB=> select sell_price - cost_price as "이득" UserDB-> from item UserDB-> where description in ('Linux CD', 'Wood Puzzle'); 이득 ------ 6.72 0.50 (2 rows) UserDB=> }}} 위 예제는 뺄셈(-) 연산과 IN 연산자를 사용하는 것을 보여주는 예제이다. 뺄셈 연산으로 기본으로 가지고 있는 컬럼의 이름이 없어졌다. 그러므로 ALIAS를 준 것이다. {{{ UserDB=> select sell_price "판매가격" from item; ERROR: syntax error at or near ""판매가격"" at character 19 LINE 1: select sell_price "판매가격" from item; ^ UserDB=> select sell_price '판매가격' from item; ERROR: type "sell_price" does not exist UserDB=> select sell_price 판매가격 from item; ERROR: syntax error at or near "판매가격" at character 19 LINE 1: select sell_price 판매가격 from item; ^ UserDB=> }}} 조금 제약이 있는 것이 보인다. 다른 DBMS에서 되는 것이 되지 않는 것을 볼 수 있다. 뭐 그래도 이정도면 제약도 아니다. 살짝 불편할 뿐이지.. {{{ UserDB=> explain select distinct town from customer; QUERY PLAN ------------------------------------------------------------------------- Unique (cost=69.83..74.83 rows=200 width=66) -> Sort (cost=69.83..72.33 rows=1000 width=66) Sort Key: town -> Seq Scan on customer (cost=0.00..20.00 rows=1000 width=66) (4 rows) UserDB=> select distinct town from customer; town ----------- Bingham Hightown Histon Lowtown Milltown Nicetown Oahenham Oxbridge Tibsville Welltown Winersby Yuleville (12 rows) UserDB=> }}} EXPLAIN 키워드를 사용하면 실행계획을 볼 수 있다. 뭐...아직은 저 실행계획이 대충만 감이 잡히지 제대로 잡히지는 않는다. Primary Key를 만들었는데 인덱스가 자동생성되었을까? 아마도 인덱스에 대한 정보가 어디엔가는 있을 것이다. {{{ UserDB=> select table_name UserDB-> from information_schema.tables UserDB-> where table_name like '%index%'; table_name ------------------------ pg_index pg_indexes pg_stat_all_indexes pg_stat_sys_indexes pg_stat_user_indexes pg_statio_all_indexes pg_statio_sys_indexes pg_statio_user_indexes (8 rows) }}} 보인다. 아마도 오라클과 비슷하게 sys, user, all로 되는 것을 보니 대충 어디엔가 내가 원하는 정보가 있는지 알 수 있다. 이것이 메타데이터의 필요성이다. 후후.. {{{ UserDB=> select table_name, column_name, ordinal_position, data_type, is_nullable UserDB-> from information_schema.columns UserDB-> where table_name = 'customer' UserDB-> and table_schema = 'public' UserDB-> and table_catalog = 'UserDB'; table_name | column_name | ordinal_position | data_type | is_nullable ------------+-------------+------------------+-------------------+------------- customer | customer_id | 1 | integer | NO customer | zipcode | 7 | character | NO customer | title | 2 | character | YES customer | phone | 8 | character varying | YES customer | town | 6 | character varying | YES customer | addressline | 5 | character varying | YES customer | lname | 4 | character varying | NO customer | fname | 3 | character varying | YES (8 rows) UserDB=> }}} 일단 컬럼에 대한 정보를 살펴보았다. 인덱스 정보를 보자. {{{ UserDB=> select * from pg_statio_user_indexes UserDB-> ; relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit -------+------------+------------+-----------+--------------+---------------+-------------- 17245 | 17248 | public | customer | customer_pk | 0 | 0 17252 | 17255 | public | item | item_pk | 0 | 0 17259 | 17262 | public | orderinfo | orderinfo_pk | 0 | 0 17264 | 17266 | public | stock | stock_pk | 0 | 0 17268 | 17270 | public | orderline | orderline_pk | 0 | 0 17272 | 17274 | public | barcode | barcode_pk | 0 | 0 (6 rows) UserDB=> }}} 젠장할 원하는 정보가 안 나온다. 아무래도 이게 아닌갑다. {{{ UserDB=> select indexdef from pg_indexes UserDB-> where tablename = 'customer'; indexdef ----------------------------------------------------------------------- CREATE UNIQUE INDEX customer_pk ON customer USING btree (customer_id) (1 row) UserDB=> }}} 나온다. 헉!! 뭔가 다른 문장이 있다. 인덱스의 종류가 아무래도 여러가지임이 Using을 통해 전해져온다. 도움말을 보자. {{{ CREATE [ UNIQUE ] INDEX name ON table [ USING method ] ( { column | ( expression ) } [ opclass ] [, ...] ) [ WHERE predicate ] }}} 특이한 것만보자. 물론 초짜인 본인에게 특이하게 보이는 것이다. {{{ method The name of the method to be used for the index. Choices are btree, hash, rtree, and gist. The default method is btree. opclass The name of an operator class. See below for details. predicate The constraint expression for a partial index. }}} 아~ 모르겠다. 여기서 막혔다. 책을 봐야겠다. 제발 설명이 나왔으면...안나옵니다. 그래서 제가 PostgresSQL에 대해서 database.sarang.net에서 찾아보았습니다. 이렇게 나오는 군요. <검색한글> btree는 디폴트로 걸리는 인덱스이고요 주로 숫자형이나 문자형에 걸리는 인덱스입니다. rtree는 다차원데이터타입에 쓰이는 인덱스이고요, 주로 2차원을 가지는 데이터 타입인 point나 polygon 따위의 데이터를 저장하는 테이블에 걸리는 인덱스입니다. hash는 hashing 방법을 이용한 인덱싱 방법입니다. hash는 트리구조를 가지지 않고 hash function을 이용하는게 특징입니다. gist는 GeneraIzed Search Tree의 약자이고요, 개발자들을 위한 인덱스 패키지라고 생각하면 쉽습니다. contrib 폴더에 가시면 다양한 타입들을 지원하는데요... 그러한 타입들에 인덱스를 만들고 싶을때 모든 코딩을 전부 하지 않더라도 이 gist라는 놈을 이용하면 index를 비교적 쉽게 만들 수 있죠... 그래서, gist는 그 자체로는 인덱스로 보긴 어렵습니다. 하지만, gist를 이용해서 rtree on gist 혹은 btree on gist, int array on gist 등의 인덱스를 만들 수 있고, Create Index 문장에서 이렇게 구체화된 인덱스를 사용할 수 있습니다... 도우말에는 기본적으로 생성되는 인덱스는 btree라고 되어 있습니다. rtree는 다차원이라는 것을 보니 PostgreSQL의 데이터 타입에 독립적인 못보던 인덱스같네요. 아직 데이터형을 살펴보지 않았으니 뭐.. 그러려니 하고 넘어갑니다. hash는 우리가 익히 알고 있는 hash를 이용한 것이겠죠. 범위검색 따위는 별로 도움이 안될 것입니다. 단일행 검색에는 매우 탁월하겠죠..함수 연산에 의한 것이니 CPU 속도에 따라 많은 성능차를 보일 것입니다. gist는 구체화된 인덱스라고 하는 것을 보니 MSSQL Server의 클러스터드 인덱스 쯤으로 생각하면 될 것 같습니다. 뭐..아직까지는 짐작일 뿐입니다. 또는 지리정보DB를 위한 것이던지.. 아마도 이거인듯.. {{{ UserDB=> create index idx_fn_substr_town UserDB-> on customer UserDB-> using btree (substr(town,1,1)) UserDB-> where (title = 'MR'); CREATE INDEX }}} 오~~ 대단한 기능입니다. 함수기반의 인덱스도 생성할 수 있고, 인덱스 종류도 여러가지이고, 특정 부분에만 인덱스를 타게 할 수도 있습니다. {{{ UserDB=> create index idx_town UserDB-> on customer(town); CREATE INDEX UserDB=> explain select town from customer UserDB-> where town = 'Hightown'; QUERY PLAN --------------------------------------------------------- Seq Scan on customer (cost=0.00..1.19 rows=1 width=66) Filter: ((town)::text = 'Hightown'::text) (2 rows) UserDB=> explain select town from customer UserDB-> where town = 'Lowtown'; QUERY PLAN --------------------------------------------------------- Seq Scan on customer (cost=0.00..1.19 rows=1 width=66) Filter: ((town)::text = 'Lowtown'::text) (2 rows) UserDB=> }}} ㅡㅡ;;; Index라는 단어가 안보입니다. 아무래도 데이터가 약한가 봅니다. 몇천건은 집어 넣어야 할 듯합니다. 오늘은 인덱스 타는 것만 보고 끝내야 겠군요... attachment:4_Item_Insert_Data.txt 를 다운로드하여 넣으세요. {{{ UserDB=> create table big_item UserDB-> ( UserDB(> item_id varchar(64) not null, UserDB(> cost_price numeric , UserDB(> sell_price numeric , UserDB(> CONSTRAINT big_item_pk PRIMARY KEY(item_id) UserDB(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "big_item_pk" for table "big_item" CREATE TABLE UserDB=> select count(*) from big_item; count ------- 4961 (1 row) UserDB=> select * from big_item UserDB-> where item_id = 'W59201'; item_id | cost_price | sell_price ---------+------------+------------ W59201 | 50 | 50 (1 row) UserDB=> explain select * from big_item UserDB-> where item_id = 'W59201'; QUERY PLAN ------------------------------------------------------------------------------ Index Scan using big_item_pk on big_item (cost=0.00..4.82 rows=1 width=178) Index Cond: ((item_id)::text = 'W59201'::text) (2 rows) UserDB=> }}} 드뎌 인덱스 사용한 것이 보이는군요...우헐헐~