PowerPoint プレゼンテーション

PowerPoint プレゼンテーション

PostgreSQL KVS hstore 2011.3.12 NTT Software Corporation http://www.ntts.co.jp/ http://www.nttsoft.com/ KVS hstore

hstore RDBMS KVS 2 KVS PostgreSQL hstore

3 KVS KVS Key Value Store Value Store awk, perl, perl, perl, Google Bigtable KVS C Consistency Value Store A Availability Value Store P Partition Tolerance KVS RDBMS RDBMS RDBMS CA, perl, NoSQL AP or CP KVS RDBMS RDBMS 4 hstore hstore hstore PostgreSQL KVS contrib PostgreSQL 8.3

8.4 9.0 hstore hstore hstore hstore hstore hstore GiST/GIN 5 hstore hstore PostgreSQL Bigtable RDB XML hstore

6 hstore OR PostgreSQL XML PostgreSQL XML / XML XPath AP

7 hstore ID 1 XXX 550

700 2 ZZZ 450 NULL ID 1

XXX 550 700 NULL 2 ZZZ 450 NULL NULL 3 DDD 500 NULL

700 8 hstore ID ID ID

1 XXX 1 1 550 2 ZZZ 2 1 700

3 2 450 ID ID ID 1 XXX

1 1 550 2 ZZZ 2 1 700 3 DDD 3

2 450 4 3 550 5 3 700 9 hstore PostgreSQL hstore

test=# \d ramen \d ramen ramen Table "public.ramen Column | Type | Modifiers Type | Type | Modifiers Mod ramenifiers ---------+---------+---------id ramen | Type | Modifiers integer | Type | Modifiers name | Type | Modifiers text | Type | Modifiers ad ramend ramenress | Type | Modifiers text | Type | Modifiers menu | Type | Modifiers hstore | Type | Modifiers test=# \d ramen test=# \d ramen select * from ramen; from ramen; id ramen | Type | Modifiers name | Type | Modifiers ad ramend ramenress | Type | Modifiers menu ----+--------------------+---------------+----------------------------------------------------------1 | | Type | Modifiers | Type | Modifiers XXX | Type | Modifiers =>600, =>700 2 | | Type | Modifiers | Type | Modifiers ZZZ | Type | Modifiers =>50, =>700, =>2 | 00 3 | | Type | Modifiers | Type | Modifiers AAA | Type | Modifiers =>650, =>700, =>750 10 hstore

hstore contrib cd ramen < >/contrib/hstore make make install RPM RHEL5 64bit OS rpm ivh postgresql90-contrib-9.0.0-1 | PGDG.rhel5.x86_64.rpm hstore psql < > f < >/share/contrib/hstore.sq l 9.0 plpgsql createlang 8.4 createlang plpgsql 11 hstore hstore hstore TEXT TEXT hstore test=# \d ramen CREATE TABLE ramen (id ramen int, name text, ad ramend ramenress text, menu hstore, topping hstor e); CREATE TABLE test=# \d ramen \d ramen+ ramen Table "public.ramen Column | Type | Modifiers Type | Type | Modifiers Mod ramenifiers | Type | Modifiers Storage | Type | Modifiers Description

---------+---------+-----------+----------+------------id ramen | Type | Modifiers integer | Type | Modifiers | Type | Modifiers plain | Type | Modifiers name | Type | Modifiers text | Type | Modifiers | Type | Modifiers extend ramened ramen | Type | Modifiers ad ramend ramenress | Type | Modifiers text | Type | Modifiers | Type | Modifiers extend ramened ramen | Type | Modifiers menu | Type | Modifiers hstore | Type | Modifiers | Type | Modifiers extend ramened ramen | Type | Modifiers topping | Type | Modifiers hstore | Type | Modifiers | Type | Modifiers extend ramened ramen | Type | Modifiers Has OIDs: no no test=# \d ramen hstore Storage TEXT TOAST 12 hstore hstore key Value Store => value { [, key => value] }... value { [, key => value] }... [, perl, key Value Store => value { [, key => value] }... value] }... }... OK

' => 600' ' => 600, => 550, => 550' '" " => 600' OK. INSERT hstore cast test=# \d ramen insert into ramen values (1 | , ', ' XXX', ' => 500, => 600, => 750': no: nohstore ); INSERT 0 1 | test=# \d ramen 13 hstore => 600 => 600 OK. OK UTF-8 n

insert into ramen values (6, n, YYY, => 700, => 600: no: nohstore, => 1 | 00, => 50: no: nohstore); INSERT 0 1 | select name, menu from ramen; name | Type | Modifiers menu | Type | Modifiers topping ----------+-----------------------+--------------- | Type | Modifiers =>700 | Type | Modifiers =>1 | 00 (1 | row)) 14 hstore test=# \d ramen select name, menu from ramen; name | Type | Modifiers menu ----------+--------------------------------------------------------------

| Type | Modifiers =>500, =>750, =>600 | Type | Modifiers =>650, =>500 | Type | Modifiers =>600, =>700 | Type | Modifiers =>650 | Type | Modifiers =>700, =>700 (5 row)s) key Value Store test=# \d ramen select name, menu-> from ramen; name | Type | Modifiers ?column? ----------+--------- | Type | Modifiers 500 | Type | Modifiers 650 | Type | Modifiers 600 | Type | Modifiers 650 | Type | Modifiers (5 row)s) 15 key Value Store NULL

hstore WHERE topping ? SELECT name, topping FROM ramen; name | Type | Modifiers topping ----------+------------------------------------------------------- | Type | Modifiers | Type | Modifiers =>1 | 00, =>50, =>2 | 00 | Type | Modifiers | Type | Modifiers =>50, =>1 | 00 | Type | Modifiers =>50, =>1 | 00 (5 row)s) SELECT name, topping FROM ramen WHERE topping ? ; name | Type | Modifiers topping --------+------------------------------------------------------- | Type | Modifiers =>1 | 00, =>50, =>2 | 00 (1 | row)) 16 hstore ALL/ANY menu

?& ALL) ?| Type | Modifiers ANY) SELECT name, menu FROM ramen WHERE menu ?& ARRAY[ , ]; name | Type | Modifiers menu ------+-------------------------------------------------------------- | Type | Modifiers =>500, =>750, =>600 (1 | row)) SELECT name, menu FROM ramen WHERE menu ?| Type | Modifiers ARRAY[ , ]; name | Type | Modifiers menu --------+-------------------------------------------------------------- | Type | Modifiers =>500, =>750, =>600 | Type | Modifiers =>600, =>700 (2 | row)s) 17 hstore avals()

500 1 650 SELECT name, menu FROM ramen WHERE 500 >= ANY (avals(menu): no: noint[]) ; name | Type | Modifiers menu --------+-------------------------------------------------------------- | Type | Modifiers =>500, =>750, =>600 | Type | Modifiers =>650, =>500 (2 | row)s) SELECT name, menu FROM ramen WHERE 650 >= ALL (avals(menu): no: noint[]) ; name | Type | Modifiers menu --------+------------------------------------------- | Type | Modifiers =>650, =>500 | Type | Modifiers =>650 (2 | row)s) 18 hstore hstore http: no//w)w)w).postgresql.jp/d ramenocument/current/html/hstore.html# \d ramenAEN1 | 2 | 03 | 04 http: no//w)w)w).postgresql.jp/d ramenocument/current/html/hstore.html# \d ramenAEN1 | 2 | 073 | 6

19 hstore hstore | Type | Modifiers| Type | Modifiers | Type | Modifiers| Type | Modifiers - hstore UPDATE 20 hstore hstore menu 600 650 SELECT menu FROM ramen WHERE name = ; menu -------------------------------------------------------------- =>500, =>750, =>600 (1 | row)) SELECT ((menu | Type | Modifiers| Type | Modifiers => 600) | Type | Modifiers| Type | Modifiers => 650) -

: no: notext FROM ramen WHERE name = ; ?column? ---------------------------------------------------------------- =>500, =>650, =>600 (1 | row)) hstore UPDATE ramen UPDATE SET menu = ((menu | Type | Modifiers| Type | Modifiers => 600) | Type | Modifiers| Type | Modifiers => 650) - : no: notext WHERE name = '; 21 hstore htore GiST/GIN GiST GIN PostgreSQL http://www.postgresql.jp/document/current/html/gist.html http://www.postgresql.jp/document/current/html/gin.html B-tree

22 hstore hstore USING gin gist GIN CREATE INDEX item_socre_gin ON item USING gin (score); GiST CREATE INDEX item_socre_gist ON item USING gist (score); GIN GiST GIN 3 GiST 3 PostgreSQL 23 hstore EXPLAIN 1, perl, 000, perl, 000 1 hstore 1 50 1 1 100 EXPLAIN ANALYZE SELECT name, score FROM item WHERE (score ? 'U8999') ORDER by name;;

QUERY PLAN -------------------------------------------------------------------------------------------------------------Seq Scan on item (cost=0.00..1 | 1 | 3 | 558.02 | row)s=462 | 6 w)id ramenth=64) (actual time=3 | 6.2 | 2 | 6..2 | 42 | 48.1 | 1 | 3 | row)s=2 | 3 | 6 loops=1 | ) Filter: no (score ? 'U8999': no: notext) Total runtime: no 2 | 42 | 48.43 | 3 | ms (3 | row)s) GIN (rows) row /1000 EXPLAIN ANALYZE SELECT name, score FROM item WHERE (score ? 'U8999') ORDER by name;; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------Sort (cost=3 | 82 | 9.62 | ..3 | 83 | 2 | .1 | 2 | row)s=1 | 000 w)id ramenth=64) (actual time=3 | 1 | 3 | 5.095..3 | 1 | 3 | 5.2 | 49 row)s=2 | 3 | 6 loops=1 | ) Sort Key: no name Sort Method ramen: no quicksort Memory: no 43 | kB -> Bitmap Heap Scan on item (cost=1 | 93 | .82 | ..3 | 779.79 row)s=1 | 000 w)id ramenth=64) (actual time=52 | .080..3 | 1 | 3 | 3 | .704 row)s=2 | 3 | 6 loops=1 | ) Recheck Cond ramen: no (score ? 'U8999': no: notext) -> Bitmap Ind ramenex Scan on item_socre_gin (cost=0.00..1 | 93 | .57 row)s=1 | 000 w)id ramenth=0) (actual time=52 | .002 | ..52 | .002 | row)s=2 | 3 | 6 loops=1 | ) Ind ramenex Cond ramen: no (score ? 'U8999': no: notext) Total runtime: no 3 | 1 | 3 | 5.448 ms (8 row)s) 24 hstore RDB EXPLAIN ANALYZE select d ramenistinct n.name, s.key, s.value from item_name as n, item_score s w)here n.id ramen = s.id ramen AND s.key = 'U899 9' ORDER BY n.name;

QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------Unique (cost=51 | 2 | 1 | 07.1 | 0..51 | 3 | 3 | 57.07 row)s=1 | 2 | 4997 w)id ramenth=75) (actual time=2 | 4466.761 | ..2 | 4467.2 | 76 row)s=2 | 3 | 6 loops=1 | ) -> Sort (cost=51 | 2 | 1 | 07.1 | 0..51 | 2 | 41 | 9.59 row)s=1 | 2 | 4997 w)id ramenth=75) (actual time=2 | 4466.759..2 | 4466.91 | 3 | row)s=2 | 3 | 6 loops=1 | ) Sort Key: no n.name, s.value Sort Method ramen: no quicksort Memory: no 43 | kB -> Hash Join (cost=3 | 2 | 789.00..49041 | 6.1 | 5 row)s=1 | 2 | 4997 w)id ramenth=75) (actual time=4642 | .2 | 45..2 | 4465.61 | 5 row)s=2 | 3 | 6 loops=1 | ) Hash Cond ramen: no (s.id ramen = n.id ramen) -> Seq Scan on item_score s (cost=0.00..44762 | 6.70 row)s=1 | 2 | 4997 w)id ramenth=68) (actual time=2 | 2 | .055..2 | 2 | 2 | 1 | 5.71 | 0 row)s=2 | 3 | 6 loops=1 | ) Filter: no (key = 'U8999': no: notext) -> Hash (cost=1 | 5406.00..1 | 5406.00 row)s=1 | 000000 w)id ramenth=1 | 5) (actual time=1 | 888.2 | 52 | ..1 | 888.2 | 52 | row)s=1 | 000000 loops=1 | ) Buckets: no 4096 Batches: no 64 Memory Usage: no 750kB -> Seq Scan on item_name n (cost=0.00..1 | 5406.00 row)s=1 | 000000 w)id ramenth=1 | 5) (actual time=0.01 | 9..801 | .82 | 9 row)s=1 | 000 000 loops=1 | ) Total runtime: no 2 | 4467.604 ms (1 | 2 | row)s) hstore RDB 25 - value P.23 value hstore key Value Store value GIN

value hstore GIN avals() value value 26 - value test=# \d ramen \d ramen item Table "public.item Column | Type | Modifiers Type | Type | Modifiers Mod ramenifiers --------+---------+----------id ramen | Type | Modifiers integer | Type | Modifiers name | Type | Modifiers text | Type | Modifiers score | Type | Modifiers hstore | Type | Modifiers Ind ramenexes: no "item_socre_gist" gist (score) test=# \d ramen EXPLAIN ANALYZE select id ramen , avals(score) FROM item WHERE ARRAY[ '70'] <@ (avals(score)::text[]) ; (avals(score): no: notext[]) ; QUERY PLAN --------------------------------------------------------------------------------------------------------------Seq Scan on item (cost=0.00..7073 | 7.45 row)s=1 | 000 w)id ramenth=406) (actual time=7.3 | 2 | 8..763 | 8.879 row)s=2 | 1 | 402 | 2 | loops=1 | ) Filter: no ('{70}': no: notext[] <@ (avals(score)::text[]) ; avals(score)) Total runtime: no 7800.52 | 2 | ms hstore (3 | row)s) value test=# \d ramen CREATE INDEX item_score_value on item USING GIN ((avals(score): no: notext[])) ; CREATE INDEX test=# \d ramen EXPLAIN ANALYZE select id ramen , avals(score) FROM item WHERE ARRAY[ '70'] <@ (avals(score)::text[]) ; (avals(score): no: notext[]) ;

QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan on item (cost=72 | .64..3 | 661 | .1 | 0 row)s=1 | 000 w)id ramenth=406) (actual time=674.085..5944.91 | 6 row)s=2 | 1 | 402 | 2 | loops=1 | ) Recheck Cond ramen: no ('{70}': no: notext[] <@ (avals(score)::text[]) ; avals(score)) -> Bitmap Ind ramenex Scan on item_score_value (cost=0.00..72 | .3 | 9 row)s=1 | 000 w)id ramenth=0) (actual time=658.73 | 0..658.73 | 0 row)s=2 | 1 | 4 02 | 2 | loops=1 | ) Ind ramenex Cond ramen: no ('{70}': no: notext[] <@ (avals(score)::text[]) ; avals(score)) Total runtime: no 6087.999 ms (5 row)s) 1 | = ANY(avals(socre): no: notex test=# \d ramen t[] 27 hstore hstore PostgreSQL (pu_dump Streaming Replication DML

VACUUM 28 RDBMS KVS RDBMS KVS Web Oracle Oracle Application Express DB KV S My Value Store SQL My Value Store Cassandra Apache Cassandra My Value Store SQL DB2 29 hstore PostgreSQL

DB PostgreSQL / hstore 30 PostgreSQL 9.0.1 PostgreSQL 9.0.1 - F.13. hstore http://www.postgresql.jp/document/current/html/hstore.html PGPortal - hstore https://oss-gzk.isl.ntt.co.jp/pgportal/dbms/hstore Lets Postgres - hstore http://lets.postgresql.jp/documents/technical/log_analy Value Store sis/2 31

Recently Viewed Presentations

  • Understanding Pro Forma - Meetup

    Understanding Pro Forma - Meetup

    Pro Forma Definition. A financial projection based on assumptions. In a financial context, it is used to present data on an "as if" basis, where one or more assumptions or hypothetical conditions are built into the data. The Pro Forma...
  • The charmonium-nucleon interaction and the charmonium-nucleus ...

    The charmonium-nucleon interaction and the charmonium-nucleus ...

    A. Y., E. Hiyama and M. Oka, arXiv:1308.6102, accepted by PTEP. Akira Yokota. Tokyo Institute of Technology. Emiko Hiyama, Makoto Oka. チャーモニウムー原子核束縛状態におけるJ/ψ と ηcの混合 ...
  • Green World Group Training Center Green World Group

    Green World Group Training Center Green World Group

    NEBOSH International General Certificate. T. ... Our trainer profiles range from a vast working experience in the field of Oil & Gas, Airline industry, and corporate oil giants in the likes of Shell, BP, SBM Offshore. ... Toll-Free: 1800 ....
  • Scientific Misconduct and how do Journal Editors deal

    Scientific Misconduct and how do Journal Editors deal

    Scientific Misconduct and how do Journal Editors deal with it? Graham C. Parker, Ph.D. The Carman and Ann Adams Department of Pediatrics Wayne State University School of Medicine
  • Motivation and Work Chapter 12 - Weebly

    Motivation and Work Chapter 12 - Weebly

    Other taste preferences are conditioned, as when people given highly salted foods develop a liking for excess salt (Beauchamp, 1987), or when people who have been sickened by a food develop an aversion to it. (The frequency of children's illnesses...
  • HEATHER: HElium ion Accelerator for radioTHERapy J. Taylor,

    HEATHER: HElium ion Accelerator for radioTHERapy J. Taylor,

    Mass is closer to protons it is easier to model with less RBE uncertainties. ... Look at orbit extraction and emittance growth . ... in particle therapy. Physics in medicine and biology, 58(23), 8265. Chicago3. Pshenichnov, I., Mishustin, I. and...
  • 組織與管理簡介 - National Chiao Tung University

    組織與管理簡介 - National Chiao Tung University

    管 理 學 Chapter 3 Global Management 全球管理 Culture is the collective programming of the mind which distinguishes the members of one human group from another.
  • Obesity Medicine Ambassadors: What is Obesity Medicine?

    Obesity Medicine Ambassadors: What is Obesity Medicine?

    The American Board of Obesity Medicine (ABOM) was created by the Obesity Medicine Association and The Obesity Society in 2011 and has nearly 1,600 Diplomates as of 2016. The growth of this group is faster than any other sub-specialty