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 mmonc...@gmail.com An: Stefan Keller <sfkel...@gmail.com> cc: pgsql-performance@postgresql.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 <sfkel...@gmail.com>: > Hoi Michel > > Hast du die EXPLAIN ANALYZE information? > > LG, Stefan > > > ---------- Forwarded message ---------- > From: Craig Ringer <cr...@postnewspapers.com.au> > Date: 2011/5/16 > Subject: Re: [PERFORM] KVP table vs. hstore - hstore performance (Was: > Postgres NoSQL emulation) > To: Stefan Keller <sfkel...@gmail.com> > Cc: pgsql-performance@postgresql.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 > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
