Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 21654B5DBB7 for ; Tue, 24 May 2011 20:45:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 07271-07 for ; Tue, 24 May 2011 23:45:47 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com [209.85.216.46]) by mail.postgresql.org (Postfix) with ESMTP id AC546B5D801 for ; Tue, 24 May 2011 20:45:47 -0300 (ADT) Received: by qwk3 with SMTP id 3so3636273qwk.19 for ; Tue, 24 May 2011 16:45:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=s1I9+fGWzxAS2lO8b/rO2PVEUG8Sd5BgshUgBRrXJyE=; b=ENQSEqfihaSYwxxDlR1eO7NZnQ7jMek1AM9jvN5iuOpRlrRl2gkYg2DvtP62XkG5Sn 83aIURMrE5Dbz9E1cmsDNdp/BlatMNPWdhHOvDo//KXkdR932suWYWdpzi2yLrjFzj7/ z3xUIH9w6oJjP7MnKjmam8bwK035unO2Ki9oA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=bxjxK8Kv+BtTgtSFvUtDKN2ETGXSKTRZ9m9Tfb/vWEHxsXC3IUmKiI87sjtmUriwB/ YpT8ToAL3QRRaZOrYP6nqb1yCgGJSHKkPWZWZpUIOMk/D0b3Uo4J1wiNG9WjkNvKPvRj woqeF4iF/m6nnEiEFq69HgqN9ZXw7lr4pPZ4c= MIME-Version: 1.0 Received: by 10.229.22.4 with SMTP id l4mr3278767qcb.298.1306280747493; Tue, 24 May 2011 16:45:47 -0700 (PDT) Received: by 10.229.13.75 with HTTP; Tue, 24 May 2011 16:45:47 -0700 (PDT) In-Reply-To: References: <4DD06EF3.7030003@postnewspapers.com.au> <99EECF01B5C87A498318776AB7E4A78D3640F59E26@sid00102.hsr.ch> Date: Wed, 25 May 2011 01:45:47 +0200 Message-ID: Subject: Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation) From: Stefan Keller To: Pierre C Cc: mmoncure@gmail.com, pgsql-performance@postgresql.org, Robert Haas Content-Type: text/plain; charset=UTF-8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201105/329 X-Sequence-Number: 43838 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 : > >> 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". > >