Документ взят из кэша поисковой машины. Адрес оригинального документа : http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2013.pdf Дата изменения: Fri Nov 1 18:01:20 2013 Дата индексирования: Mon Apr 11 08:03:23 2016 Кодировка:

Binary storage for nested data structures and application to hstore data type
Oleg Bartunov, Teodor Sigaev Moscow University

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Hstore developers



Teodor Sigaev, Oleg Bartunov Sternberg Astronomical Institute of Moscow University Major contributions: PostgreSQL extendability: GiST GIN, SP-GiST , Full-text search, ltree, pg_trgm, hstore, intarray,..
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Agenda


Introduction to hstore History of hstore development Hstore internals Limitations Hstore operators and functions Performance study Summary Development plans
Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev


Introduction to hstore


Hstore -- key/value storage
'a=>1, b=>2'::hstore


(inspired by perl hash)



Key, value -- strings Get value for a key: hstore -> text Operators with indexing support (GiST, GIN) Check for key: hstore ? text Contains: hstore @> hstore .........check documentations for more ........ Functions for hstore manipulations (akeys, avals, skeys, svals, each,......)
Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev


Introduction to hstore
«Google Trends» noticed hstore since 2011

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


History of hstore development


May 16, 2003 -- first version of hstore

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Introduction to hstore


Hstore benefits


In provides a flexible model for storing a semistructured data in relational database Too simple model ! Hstore key-value model doesn't supports tree-like structures as json (introduced in 2006, 3 years after hstore)



Hstore drawbacks


Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


hstore vs json


PostgreSQL already has json since 9.0, which supports document-based model, but






It's slow, since it has no binary representation and needs to be parsed every time Hstore is fast, thanks to binary representation and index support It's possible to convert hstore to json and vice versa, but current hstore is limited to key-value



Need hstore with document-based model. Share it's binary representation with json !
Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev


History of hstore development










May 16, 2003 - first (unpublished) version of hstore for PostgreSQL 7.3 Dec, 05, 2006 - hstore is a part of PostgreSQL 8.2 (thanks, Hubert Depesz Lubaczewski!) May 23, 2007 - GIN index for hstore, PostgreSQL 8.3 Sep, 20, 2010 - Andrew Gierth improved hstore, PostgreSQL 9.0 May 24, 2013 - Nested hstore with array support, key->value model document-based model PostgreSQL 9.4(?)
Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev


Hstore syntax


Hash-like:
'a=>1' '{a=>1}' 'a=>b, b=>c' '{a=>b, b=>"c"}'



Array-like:
'{a}' '{a,b}' '[a]' '[a,b]'


Ole

Scalar: 'a' g Bartunov, Teodor

Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Hstore types support


Numeric
=# select 'a=>10.2'::hstore, '{1E6,2E-3,3.123456789}'::hstore; hstore | hstore -----------+------------------------------"a"=>10.2 | {1000000, 0.002, 3.123456789} =# select pg_typeof('a=>1E6'::hstore -> 'a'); pg_typeof ----------text =# select pg_typeof('a=>10.2'::hstore ^> 'a'); pg_typeof ----------numeric

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Hstore types support


Boolean

=# select 'a=>t'::hstore, '{TRUE,true,FALSE,f}'::hstore; hstore | hstore --------+-------------"a"=>t | {t, t, f, f} =# select pg_typeof('a=>t'::hstore ?> 'a'); pg_typeof ----------boolean

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Hstore types support


NULL
IS NULL;

=# select ('a=>NULL'::hstore -> 'a') ?column? ---------t

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Current: HStore's internals

Varlena header

Npairs:31

Array of HEntry N = 2 * Npairs

Array of strings

New version flag:1

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Current: HEntry

Ending position of corresponding string, relative to the beginning of array of strings. (30 bit)

ISNULL:1 (only for values) ISFIRST:1

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Current: Summary
HEntry array String array

Varlena header

Npairs:31

Key endpos: 31

Val endpos: 31

...

key val

...

New version flag:1

ISNULL:1

Start First key i-th key i-th value 0 HEntry[i*2 - 1] HEntry[i*2]

End HEntry[0] HEntry[i*2] HEntry[i*2 + 1]

Pairs are lexicographically ordered by key
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Nested: Layout
HStore

Varlena header

Nelems:28 OR Npairs:28

HEntries

HEntry's values

ISSCALAR:1 ISARRAY too New version flag:1 ISHASH:1 ISARRAY:1

HEntry value could be an hstore itself Scalar stored as a single-element array
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Nested: HEntry
ISFIRST:1

1

3

Ending position of corresponding value, relative to the beginning of array of strings. Non-aligned!

0 0 0 0 0

0 0 1 0 1

0 1 0 1 1

1 0 0 1 1

-

numeric nested null (compatibility) bool (false) bool(true)
PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support


Nested: Summary
HStore
HEntry array Element array

Varlena header

Npairs:28 OR Nelems:28

Elem endpos: 28

...

Elem's value

...

Flags:4 (new version, isarray, ishash, isscalar)

Flags:4 (isfirst, isnull, isnest, isbool(istrue, isfalse), isnumeric)

Optional align bytes (only for nested/numeric): 0..3

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Nested: Access
For complex value start = INTALIGN(start) H F i i A i S r t t H st key h key h value End 0 HEntry[0] HEntry[i*2 - 1] HEntry[i*2] align(HEntry[i*2]) HEntry[i*2 + 1] Start

Pairs are lexicographically ordered by key ARRAY First elem i-th elem Start 0 align(HEntry[i - 1]) Elements are not ordered
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

End HEntry[0] HEntry[i]


Hstore limitations


Levels: unlimited Number of elements in array: 2^28 Number of pairs in hash: 2^28 Length of string: 2^28 bytes Length of nested hash or array: 2^28 bytes 2^28 bytes = 256 MB

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Compatibility






HStore as type is absolutely [pg_]upgradefriendly (ISHASH bit could be set automatically, current version will always contains zeros) It's also true for GIN indexes: instead of KV notation it uses KVE It's not true for GiST: old version doesn't uses KV notation, now it uses KVE. Indexes should be rebuilded
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Hstore syntax cont.
=# select '{{a=>1}, {1,2,3}, {c=>{d,f }}}'::hstore; hstore -------------------------------------------------{{"a"=>1}, {1,2,3}, {"c"=>{"d", f }}}


hstore.array_square_brackets [false],true
=# set hstore.array_square_brackets=true; =# select '{{a=>1}, {1,2,3}, {c=>{d,f }}}'::hstore; hstore -------------------------------------------------[{"a"=>1}, [1,2,3], {"c"=>["d", f]}]

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Hstore syntax cont.



hstore.root_hash_decorated true,[false]
=# select 'a=>1'::hstore; hstore -------"a"=>1 =# set hstore.root_hash_decorated=true; =# select 'a=>1'::hstore; hstore -----------{"a"=>1}

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Hstore syntax cont.
=# set hstore.pretty_print=true; =# select '{{a=>1}, {1,2,3}, {c=>{d,f}}}'::hstore; hstore ------------------{ + { + "a"=>1 + }, + { + 1, + 2, + 3 + }, + { + "c"=> + { + "d", + f + } + } + } (1 row)
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


Get value by key


text hstore -> text
=# select 'a=>1,b=>{c=>3,d=>{4,5,6}},1=>f '::hstore -> 'b'; ?column? ---------------------------"c"=>3, "d"=>{4,5,6}



hstore hstore %> text
=# select 'a=>1,b=>{c=>3,d=>{4,5,6}},1=>f '::hstore %> 'b'; ?column? ---------------------------"c"=>3, "d"=>{4,5,6}

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


Get value by path text hstore #> path
=# select 'a=>1,b=>{c=>3,d=>{4,5,6}},1=>f '::hstore #> '{b,d,0}'; ?column?

------4


hstore hstore #%> path
=# select 'a=>1,b=>{c=>3,d=>{4,5,6}},1=>f '::hstore #%>'{b,d}'; ?column? --------{4,5,6}

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


Get array element by index


text hstore->integer
=# select '{a,b,3,4,5}'::hstore->1; ?column? ---------b ?column? ---------4 ­ negative index starts from the end =# select '{a,b,3,4,5}'::hstore-> -2;

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


Get array element by index


hstore hstore%>integer
=# select '{a,b,3,4,5}'::hstore%>1; ?column? ---------"b" ?column? ---------4
Space is important :)

­ negative index starts from the end

=# select '{a,b,3,4,5}'::hstore%> -2;

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


Chaining operators to go deep
=# select 'a=>1,b=>{c=>3,d=>{4,5,6}},1=>f '::hstore %> 'b'->'c'; ?column? ---------3 =# select 'a=>1,b=>{c=>3,d=>{4,5,6}},1=>f '::hstore #%> '{b,d}'->0; ?column? ---------4

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


hstore hstore || hstore
=# select 'a=>1,b=>{c=>3,d=>{4,5,6}}'::hstore || 'b=>{c=>4}'::hstore; ?column? --------------------------"a"=>1, "b"=>{"c"=>4}



Concatenation with path hstore concat_path(hstore,text[],hstore)
=# select concat_path('a=>1,b=>{c=>3,d=>{4,5,6}}'::hstore,'{b,d}', '1'); concat_path -----------------------------------------------------{"a"=>1, "b"=>{"c"=>3, "d"=>{4, 5, 6, 1}}}
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


Concatenation with path hstore concat_path(hstore,text[],hstore)
With empty path it works exactly as old || operator
=# select concat_path('a=>1,b=>{c=>3,d=>{4,5,6}}'::hstore,'{}', 'a=>2'); concat_path -----------------------------------------------------{"a"=>2, "b"=>{"c"=>3, "d"=>{4, 5, 6}}}

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


Contains operators @>, <@ goes deep

=# SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{c=>3}}'; ?column? ---------t =# SELECT 'a=>{{c=>3}}' <@ 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore; ?column? ---------t

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


setof hstore hvals(hstore)
=# SELECT * FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore) AS q; q -----------------------"sh"=>2, "tags"=>1 "sh"=>4, "tags"=>3 =# SELECT q->'tags' FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore) AS q; ?column? ---------1 3

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions
setof hstore hvals(hstore, text[])
=# SELECT * FROM hvals('{{tags=>1, sh=>2,a=>{tags=>4}}, {tags=>3,sh=>4}}'::hstore,'{null,tags}'); hvals ------1 3


setof text svals(hstore,text[])

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


Replace with path hstore replace(hstore,text[],hstore)
=# select replace('a=>1,b=>{c=>3,d=>{4,5,6}}'::hstore,'{b,d}', '1'); replace --------------------------------------------------{"a"=>1, "b"=>{"c"=>3, "d"=>1}}

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


hstore <-> json conversion


json hstore_to_json(hstore)
=# select hstore_to_json('a=>1,b=>{c=>3,d=>{4,5,6}}'::hstore); hstore_to_json --------------------------------------------------{"a": "1", "b": {"c": "3", "d": ["4", "5", "6"]}}



hstore json_to_hstore(json)
=# select json_to_hstore('{"a": "1", "b": {"c": "3", "d": ["4", "5", "6"]}}'::json); json_to_hstore ------------------------------------------------{"a"=>1, "b"=>{"c"=>"3", "d"=>{"4", "5", "6"}}}

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


hstore <-> json cast


hstore::json
=# select 'a=>1'::hstore::json; json -----------{"a": 1}



json::hstore
=# select '{"a": 1}'::json::hstore; hstore ---------{"a"=>1}

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


hstore <-> json cast


Hstore had no types support as json, so :(
=# select '{"a":3.14}'::json::hstore::json; json --------------{"a": "3.14"} =# select '3.14'::json::hstore::json; json ---------["3.14"]

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions


hstore <-> json cast


Hstore has now types support and casting is fine !
=# select '{"a":3.14}'::json::hstore::json; json --------------{"a": 3.14} =# select '3.14'::json::hstore::json; json ---------3.14

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions
=# set hstore.pretty_print=true; =# select hstore_to_json('{{a=>1}, {1,2,3}, {c=>{d,f}}}'::hstore); hstore_to_json ------------------[ + { + "a": "1" + }, + [ + "1", + "2", + "3" + ], + { + "c": + [ + "d", + "f" + ] + } + ] (1 row)
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Operators matrix
-----------------+------+-----+--------------+--------------+----------+ right arg's type | text | int |text[](keys) | text[](path) | hstore | -----------------+ | | | | return type | | | | | | -----------------+------+-----+--------------+--------------+----------+ text | -> | -> | -> | #> | | -----------------+------+-----+--------------+--------------+----------+ hstore |%>, - |%>, -| | #%>, / | -,|| | -----------------+------+-----+--------------+--------------+----------+ bool | ?|?| ?&, ?| | #? |<@,@>,=,<>| -----------------+------+-----+--------------+--------------+----------+ numeric | ^> | ^> | ^> | #^> | | -----------------+------+-----+--------------+--------------+----------+

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Operators and functions

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Performance






Data 1,252,973 bookmarks from Delicious in json format The same bookmarks in hstore format The same bookmarks as text Server desktop Linux, 8 GB RAM, 4-cores Xeon 3.2 GHz, Test Input performance - copy data to table Access performance - get value by key Search performance contains @> operator
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Performance


Data 1,252,973 bookmarks from Delicious in json format The same bookmarks in hstore format The same bookmarks as text
=# \dt+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+---------+------------public | hs | table | postgres | 1379 MB | public | js | table | postgres | 1322 MB | public | tx | table | postgres | 1322 MB |

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Performance
=# select h from hs limit 1;
h ------------------------------------------------------------------------------------------------

"id"=>"http://delicious.com/url/b5b3cbf9a9176fe43c27d7b4af94a422#mcasas1", + "link"=>"http://www.theatermania.com/broadway/", + "tags"=> + { + { + "term"=>"NYC", + "label"=>NULL, + "scheme"=>"http://delicious.com/mcasas1/" + }, + { + "term"=>"english", + "label"=>NULL, + "scheme"=>"http://delicious.com/mcacas1/" + }, + }, + "links"=> + { + { + "rel"=>"alternate", + "href"=>"http://www.theatermania.com/broadway/", + "type"=>"text/html" + } + }, + "title"=>"TheaterMania", + "author"=>"mcasas1", + "source"=>NULL, + "updated"=>"Tue, 08 Sep 2009 23:28:55 +0000", + "comments"=>"http://delicious.com/url/b5b3cbf9a9176fe43c27d7b4af94a422", + "guidislink"=>"false", + "title_detail"=> + { + "base"=>"http://feeds.delicious.com/v2/rss/recent?min=1&count=100", + "type"=>"text/plain", + "value"=>"TheaterMania", + "language"=>NULL + }, + "wfw_commentrss"=>"http://feeds.delicious.com/v2/rss/url/b5b3cbf9a9176fe43c27d7b4 af94a422"+

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Performance


Input performance


Copy data (1,252,973 rows) as text, json,hstore
copy tt from '/path/to/test.dump'

Text: 57 s Json: 61 s Hstore: 76 s ­ there is some room to speedup

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Performance


Access performance -- get value by key Base: select h from hs; Hstore: select h->'updated' from hs; Json: select j->>'updated' from js; Regexp: select (regexp_matches(t, '"updated":"([^"]*)"'))[1] from tx; Base: 0.3 s hstore: 0.5 s Json: 11. s regexp: 18.8 s
Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev


Performance


Access performance -- get value by key Base: 0.3 s hstore: 0.5 s Json: 11. s regexp: 18.8 s



Hstore is ~ 50x faster json thanks to binary representation !

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Performance


Search performance -- contains @> operator Hstore - seqscan, GiST GIN ,
select count(*) from hs where h @> 'tags=>{{term=>NYC}}';


Json -- estimation, GiST, GIN (functional indexes) exact time > estimation (there are may be many tags)
select count(*) from js where j#>>'{tags,0,term}' = 'NYC';

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Performance


Search performance -- contains @> operator Hstore - seqscan, GiST GIN , 100s 400s - create index 64MB 815MB 0.98s 0.3s 0.1s 3x 10x


Json -- estimation, GiST, 130s 12s 2s 6x

GIN (functional indexes) 500s - create index 0.1s 120x
PGConf.EU, Dublin, Nov 1, 2013

Recheck (GiST) calls json_to_hstore()
Oleg Bartunov, Teodor Sigaev Nested hstore with array support


Summary










Hstore is now nested and supports arrays Document-based model ! Hstore access to specified field is fast (thanks to binary representation) Hstore operators can use GiST and GIN indexes Json users can use functional GIN index and get considerable speedup Hstore's binary representation can be used by json
Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev


Development plans


Speedup hstore input Hstore query language - hpath, hquery ? Better indexing - SP-GiST-GIN hybrid index Statistics support (challenging task) Types support (+) Documentation (+), thanks David Wheeler ! Submit patch for 9.4, David Wheeler review Add binary representation to json, Dunstan ? Add index support for json, Dunstan ?
Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev


GIN Fast-scan


Observation


GIN indexes separately keys and values Key 'tags' is very frequent -1138532, value '{{term=>NYC}}' is rare -- 285 Current GIN: time (freq & rare) ~ time(freq) Fast-scan : time (freq & rare) ~ time(rare)



=# select count(*) from hs where h::hstore @> 'tags=>{{term=>NYC}}'::hstore; count ------285 (1 row) Time: 17.372 ms
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


Performance


Search performance -- contains @> operator Hstore - seqscan, GiST GIN , GIN++ 100s 400s - create index 64MB 815MB 0.98s 0.3s 0.1s 0.017s 3x 10x 60x

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


MongoDB 2.4.7


Load data - ~8 min SLOW !

mongoimport --host localhost -c js --type json < delicious-rss-1250k Mon Oct 28 19:16:47.025 7400 2466/second ... Mon Oct 28 19:24:38.030 1250800 2638/second Mon Oct 28 19:24:38.902 check 9 1252973 Mon Oct 28 19:24:38.902 imported 1252973 objects



Search - ~ 1s (seqscan) THE SAME
db.js.find({tags: {$elemMatch:{ term: "NYC"}}}).count() 285 -- 980 ms

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


MongoDB 2.4.7


Search -- 1ms (index) WOW !

db.js.ensureIndex( {"tags.term" : 1} ) db.js.find({tags: {$elemMatch:{ term: "NYC"}}}).explain() { "cursor" : "BtreeCursor tags.term_1", "isMultiKey" : true, "n" : 285, "nscannedObjects" : 285, "nscanned" : 285, "nscannedObjectsAllPlans" : 285, ................................ "millis" : 1, "indexBounds" : { "tags.term" : [ [ "NYC", "NYC" ] ]
Oleg Bartunov, Teodor Sigaev Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013


GIN hstore hash index


Idea: index hash(full paths to elements and values) {a=>{b=>{c=>1}}, d=>{1,2,3}} path-keys: a.b.c.1, d..1, d..2,d..3 GIN: {hash(path-key)}

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


GIN hstore hash index

=# create index gin_hs_hash_idx on hs using gin(h gin_hstore_hash_ops); CREATE INDEX Time: 68777.418 ms =# explain analyze select count(*) from hs where h::hstore @> 'tags=>{{term=>NYC}}'::hstore; QUERY PLAN -----------------------------------------------------------------------Aggregate (cost=4733.21..4733.22 rows=1 width=0) (actual time=0.647..0.647 rows=1 loops=1) -> Bitmap Heap Scan on hs (cost=33.71..4730.08 rows=1253 width=0) (actual time=0.128..0.614 rows=285 loops=1) Recheck Cond: (h @> '"tags"=>{{"term"=>"NYC"}}'::hstore) -> Bitmap Index Scan on gin_hs_hash_idx (cost=0.00..33.40 rows=1253 width=0) (actual time=0.085..0.085 rows=285 loops=1) Index Cond: (h @> '"tags"=>{{"term"=>"NYC"}}'::hstore) Total runtime: 0.672 ms (6 rows)

WOW++ !
Nested hstore with array support PGConf.EU, Dublin, Nov 1, 2013

Oleg Bartunov, Teodor Sigaev


Performance


Search performance -- contains @> operator MongoDB uses very «narrow» index Hstore's indexes are general
GIN GIN++ GINhash 815MB 349MB 0.1s 0.017s 0.0007s 10x 60x 1400x MongoDB 100MB 0.001s 1000x

Hstore - seqscan, GiST, 64MB 0.98s 0.3s 3x

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Availability


Patch to master branch is available

http://www.sigaev.ru/misc/nested_hstore-0.36.patch.gz

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013


Thanks !

Oleg Bartunov, Teodor Sigaev

Nested hstore with array support

PGConf.EU, Dublin, Nov 1, 2013