From: Stefan Keller <sfkeller@gmail.com>
To: Pierre C <lists@peufeu.com>
Cc: mmoncure@gmail.com, pgsql-performance@postgresql.org, Robert Haas <robertmhaas@gmail.com>
Subject: Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Date: Wed, 25 May 2011 01:45:47 +0200
Message-ID: <BANLkTimiphaS_5yBLjr-jDPFjWV3Uc1etA@mail.gmail.com> (raw)
In-Reply-To: <op.vvxkj7o8eorkce@apollo13>
References: <BANLkTi=LjFTnn8hLwBZc1c87Vq+pw02+jQ@mail.gmail.com>
<4DD06EF3.7030003@postnewspapers.com.au>
<BANLkTimdGR4X0uMMyMwtvy9Uex-zeMtVCQ@mail.gmail.com>
<BANLkTintNbJZw-hnVpy8q5jLW1__E9BY1w@mail.gmail.com>
<99EECF01B5C87A498318776AB7E4A78D3640F59E26@sid00102.hsr.ch>
<op.vvxkj7o8eorkce@apollo13>
Salut Pierre
You wrote
> Try to create a btree index on "(bench_hstore->bench_id) WHERE
> (bench_hstore->bench_id) IS NOT NULL".
What do you mean exactly?
=> CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps) WHERE
??? IS NOT NULL;
My table's def is:
> CREATE TABLE myhstore ( id bigint PRIMARY KEY, kvps hstore NOT NULL );
So I'm doing something like:
CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps);
Stefan
2011/5/23 Pierre C <lists@peufeu.com>:
>
>> 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.
>>
>
> Try to create a btree index on "(bench_hstore->bench_id) WHERE
> (bench_hstore->bench_id) IS NOT NULL".
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sfkeller@gmail.com, lists@peufeu.com, robertmhaas@gmail.com
Subject: Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
In-Reply-To: <BANLkTimiphaS_5yBLjr-jDPFjWV3Uc1etA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox