Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: mmoncure@gmail.com, sfkeller@gmail.com, pgsql-performance@postgresql.org,
	m1ott@hsr.ch
Subject: Re: FW: KVP table vs. hstore - hstore performance (Was:
	Postgres NoSQL emulation)
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>
Date: Mon, 23 May 2011 12:53:57 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vvxkj7o8eorkce@apollo13>
In-Reply-To: <99EECF01B5C87A498318776AB7E4A78D3640F59E26@sid00102.hsr.ch>
User-Agent: Opera Mail/11.10 (Linux)


> 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".