From: | Stefan Keller <sfkeller(at)gmail(dot)com> |
---|---|
To: | Pierre C <lists(at)peufeu(dot)com> |
Cc: | mmoncure(at)gmail(dot)com, pgsql-performance(at)postgresql(dot)org, Robert Haas <robertmhaas(at)gmail(dot)com> |
Subject: | Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation) |
Date: | 2011-05-25 22:58:39 |
Message-ID: | BANLkTiknLoZMjV8eY6cdwwdXKb0jxgKP4w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi all
Thank you to all who answered: That worked:
CREATE INDEX planet_osm_point_tags_amenity
ON planet_osm_point ((tags->'amenity'))
WHERE (tags->'amenity') IS NOT NULL;
My problem is, that in fact I don't know which tag to index since I'm
running a web admin application where users can enter arbitrary
queries.
Yours, Stefan
2011/5/25 Pierre C <lists(at)peufeu(dot)com>:
>> 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);
>
> Hello ;
>
> I meant a plain old btree index like this :
>
> CREATE INDEX foo ON myhstore((kvps->'yourkeyname')) WHERE
> (kvps->'yourkeyname') IS NOT NULL;
>
> The idea is that :
>
> - The reason to use hstore is to have an arbitrary number of keys and use
> the keys you want, not have a fixed set of columns like in a table
> - Therefore, no hstore key is present in all rows (if it was, you'd make
> it a table column, and maybe index it)
> - You'll probably only want to index some of the keys/values (avoiding to
> index values that contain serialized data or other stuff that never
> appears in a WHERE clause)
>
> So, for each key that corresponds to a searchable attribute, I'd use a
> conditional index on that key, which only indexes the relevant rows. For
> keys that never appear in a WHERE, no index is needed.
>
> gist is good if you want the intersecton of a hstore with another one (for
> instance), btree is good if you want simple search or range search.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Crawford | 2011-05-26 00:03:02 | Re: Speeding up loops in pl/pgsql function |
Previous Message | Mark Kirkwood | 2011-05-25 22:42:15 | Re: serveRAID M5014 SAS |