Re: Postgres NoSQL emulation

On Tue, May 10, 2011 at 12:56 PM, Pierre C <lists(at)peufeu(dot)com> wrote:
>
> While reading about NoSQL,
>
>> MongoDB let's you store and search JSON objects.In that case, you don't
>> need to have the same "columns" in each "row"
>
> The following ensued. Isn't it cute ?
>
> CREATE TABLE mongo ( id SERIAL PRIMARY KEY, obj hstore NOT NULL );
> INSERT INTO mongo (obj) SELECT ('a=>'||n||',key'||(n%10)||'=>'||n)::hstore
> FROM generate_series(1,100000) n;
>
> SELECT * FROM mongo LIMIT 10;
>  id |           obj
> ----+-------------------------
>  1 | "a"=>"1", "key1"=>"1"
>  2 | "a"=>"2", "key2"=>"2"
>  3 | "a"=>"3", "key3"=>"3"
>  4 | "a"=>"4", "key4"=>"4"
>  5 | "a"=>"5", "key5"=>"5"
>  6 | "a"=>"6", "key6"=>"6"
>  7 | "a"=>"7", "key7"=>"7"
>  8 | "a"=>"8", "key8"=>"8"
>  9 | "a"=>"9", "key9"=>"9"
>  10 | "a"=>"10", "key0"=>"10"
>
> CREATE INDEX mongo_a ON mongo((obj->'a')) WHERE (obj->'a') IS NOT NULL;
> CREATE INDEX mongo_k1 ON mongo((obj->'key1')) WHERE (obj->'key1') IS NOT
> NULL;
> CREATE INDEX mongo_k2 ON mongo((obj->'key2')) WHERE (obj->'key2') IS NOT
> NULL;
> VACUUM ANALYZE mongo;
>
> SELECT * FROM mongo WHERE (obj->'key1')='271';
>  id  |            obj
> -----+---------------------------
>  271 | "a"=>"271", "key1"=>"271"
> (1 ligne)
>
> EXPLAIN ANALYZE SELECT * FROM mongo WHERE (obj->'key1')='271';
>                                                     QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------
>  Index Scan using mongo_k1 on mongo  (cost=0.00..567.05 rows=513 width=36)
> (actual time=0.024..0.025 rows=1 loops=1)
>   Index Cond: ((obj -> 'key1'::text) = '271'::text)
>  Total runtime: 0.048 ms

why even have multiple rows? just jam it all it there! :-D

merlin