From: | <m1ott(at)hsr(dot)ch> |
---|---|
To: | <mmoncure(at)gmail(dot)com>, <sfkeller(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation) |
Date: | 2011-05-17 15:10:31 |
Message-ID: | 99EECF01B5C87A498318776AB7E4A78D3640F59E26@sid00102.hsr.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi Merlin
The analyze command gave the following result:
On the KVP table:
Index Scan using kvpidx on bench_kvp (cost=0.00..8.53 rows=1 width=180) (actual time=0.037..0.038 rows=1 loops=1)
Index Cond: (bench_id = '200000_200000'::text)
Total runtime: 0.057 ms
And on the Hstore table:
Bitmap Heap Scan on bench_hstore (cost=32.22..3507.54 rows=1000 width=265) (actual time=145.040..256.173 rows=1 loops=1)
Recheck Cond: (bench_hstore @> '"bench_id"=>"200000_200000"'::hstore)
-> Bitmap Index Scan on hidx (cost=0.00..31.97 rows=1000 width=0) (actual time=114.748..114.748 rows=30605 loops=1)
Index Cond: (bench_hstore @> '"bench_id"=>"200000_200000"'::hstore)
Total runtime: 256.211 ms
For Hstore I'm using a GIST index.
Table analysis returned no message.
Michel
Von: Merlin Moncure mmoncure(at)gmail(dot)com
An: Stefan Keller <sfkeller(at)gmail(dot)com>
cc: pgsql-performance(at)postgresql(dot)org
Datum: 16. Mai 2011 15:47
Betreff: Re: [PERFORM] KVP table vs. hstore - hstore performance (Was:
Postgres NoSQL emulation)
Merlin Moncure
hstore is not really designed for large-ish sets like that.
merlin
2011/5/16 Stefan Keller <sfkeller(at)gmail(dot)com>:
> Hoi Michel
>
> Hast du die EXPLAIN ANALYZE information?
>
> LG, Stefan
>
>
> ---------- Forwarded message ----------
> From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
> Date: 2011/5/16
> Subject: Re: [PERFORM] KVP table vs. hstore - hstore performance (Was:
> Postgres NoSQL emulation)
> To: Stefan Keller <sfkeller(at)gmail(dot)com>
> Cc: pgsql-performance(at)postgresql(dot)org
>
>
> On 14/05/11 18:10, Stefan Keller wrote:
>> Hi,
>>
>> I am conducting a benchmark to compare KVP table vs. hstore and got
>> bad hstore performance results when the no. of records is greater than
>> about 500'000.
>>
>> CREATE TABLE kvp ( id SERIAL PRIMARY KEY, key text NOT NULL, value text );
>> -- with index on key
>> CREATE TABLE myhstore ( id SERIAL PRIMARY KEY, obj hstore NOT NULL );
>> -- with GIST index on obj
>>
>> Does anyone have experience with that?
>
> What are your queries?
>
> What does EXPLAIN ANALYZE report on those queries?
>
> Did you ANALYZE your tables after populating them?
>
> --
> Craig Ringer
>
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Johansen | 2011-05-17 15:31:04 | Pushing LIMIT into sub-queries of a UNION ALL? |
Previous Message | Cédric Villemain | 2011-05-17 13:52:33 | Re: Fill Factor |