Erklärt: HSTORE

[akretschmer]

Ich hab ja letze Zeit ab und an mal auf den hstore-Datentype als schnellen Key-Value verwiesen. Aber wie arbeitet man damit?

Kurze Demo.
(set enable_seqscan = off; gesetzt, um, wenn möglich, Index-Scans zu erzwingen)

test=# create table artikel(id int primary key, name text, eigenschaften hstore);CREATE TABLE
Time: 8,557 ms
test=*# insert into artikel values (1, 'artikel 1','farbe=>rot, gewicht_in_kg=>2'::hstore);INSERT 0 1
Time: 0,483 ms
test=*# insert into artikel values (2, 'artikel 2','farbe=>gelb, gewicht_in_kg=>12, mindestbestellmenge=>10'::hstore);INSERT 0 1
Time: 0,177 ms
test=*# insert into artikel values (3, 'artikel 3','farbe=>blau, mindestbestellmenge=>10'::hstore);
INSERT 0 1
Time: 0,186 ms
test=*# commit;
COMMIT
Time: 0,449 ms
test=#
test=#
test=# select * from artikel ;
id |  name  |  eigenschaften
----+-----------+---------------------------------------------------------------------
  1 | artikel 1 | "farbe"=>"rot", "gewicht_in_kg"=>"2"
  2 | artikel 2 | "farbe"=>"gelb", "gewicht_in_kg"=>"12", "mindestbestellmenge"=>"10"
  3 | artikel 3 | "farbe"=>"blau", "mindestbestellmenge"=>"10"
(3 rows)

Soweit, so gut.

Angenommen, ich möchte Abfragen auf die Eigenschaft Farbe via Index beschleunigen:

test=*# create index idx_artikel_eigenschaften_farbe on artikel ((eigenschaften->'farbe'));CREATE INDEX
Time: 4,819 ms
test=*# explain select * from artikel where eigenschaften->'farbe' = 'rot';
  QUERY PLAN
------------------------------------------------------------------------------------------------
Index Scan using idx_artikel_eigenschaften_farbe on artikel  (cost=0.13..8.15 rows=1 width=68)
  Index Cond: ((eigenschaften -> 'farbe'::text) = 'rot'::text)
(2 rows)

Soweit, so gut.

Was ist, wenn ich nach Gewicht suche, und zwar was mehr als 5 kg hat?

test=*# create index idx_artikel_eigenschaften_gewicht on artikel ((eigenschaften->'gewicht_in_kg'));
CREATE INDEX
Time: 7,000 ms
test=*# explain select * from artikel where eigenschaften->'gewicht_in_kg' > '5';
  QUERY PLAN
--------------------------------------------------------------------------------------------------
Index Scan using idx_artikel_eigenschaften_gewicht on artikel  (cost=0.13..8.15 rows=1 width=68)
  Index Cond: ((eigenschaften -> 'gewicht_in_kg'::text) > '5'::text)
(2 rows)

Schaut gut aus, aber:

test=*# select * from artikel where eigenschaften->'gewicht_in_kg' > '5';
id | name | eigenschaften
----+------+---------------
(0 rows)

Es stimmt nicht! Warum? Wir erwarten hier einen numerischen Vergleich, arbeiten aber mit Texten. Das geht ja nun mal nicht.

Aber PG wäre nicht PG, wenn es da nicht einen Weg gäbe:

test=*# select * from artikel where (eigenschaften->'gewicht_in_kg')::int > 5::int;
id |  name  |  eigenschaften
----+-----------+---------------------------------------------------------------------
  2 | artikel 2 | "farbe"=>"gelb", "gewicht_in_kg"=>"12", "mindestbestellmenge"=>"10"
(1 row)

Time: 0,248 ms
test=*# explain select * from artikel where (eigenschaften->'gewicht_in_kg')::int > 5::int;
  QUERY PLAN
----------------------------------------------------------------------------
Seq Scan on artikel  (cost=10000000000.00..10000000001.06 rows=1 width=68)
  Filter: (((eigenschaften -> 'gewicht_in_kg'::text))::integer > 5)
(2 rows)

Paßt zwar, ist aber ein Seq-Scan. Grund: im Index steht nur der TEXT-Wert, wir vergleichen aber den INT-Wert. Damit ist der Index wertlos.

Und wieder: PG wäre nicht PG, wenn es da nicht was gäbe ;-)

Wir schreiben eine kleine Funktion, und erstellen auf dieser Funktion einen Index:

test=*# create or replace function get_gewicht(hstore) returns int as $$begin return (($1->'gewicht_in_kg'))::int; end;$$language plpgsql immutable strict;
CREATE FUNCTION
Time: 0,584 ms
test=*# create index idx_artikel_eigenschaften_gewicht2 on artikel (get_gewicht(eigenschaften));
CREATE INDEX
test=*# explain select * from artikel where get_gewicht(eigenschaften) > 5;
  QUERY PLAN
---------------------------------------------------------------------------------------------------
Index Scan using idx_artikel_eigenschaften_gewicht2 on artikel  (cost=0.38..8.40 rows=1 width=68)
  Index Cond: (get_gewicht(eigenschaften) > 5)
(2 rows)

Paßt!

Was passiert nun eigenlich, wenn wir textuell ein Gewicht definieren, was kein INT ist? Probieren wir es aus!

test=*# insert into artikel values (4, 'artikel 4','farbe=>blau, mindestbestellmenge=>10, gewicht_in_kg=>15kg'::hstore);
ERROR:  invalid input syntax for integer: "15kg"
CONTEXT:  PL/pgSQL function get_gewicht(hstore) line 1 at RETURN
Time: 0,427 ms

Wow!

Auch wenn HSTORE eigentlich ein TEXT-Type ist: durch den definierten Index auf gewicht_in_kg, welcher dieses Feld ausliest und als INT zurückliefert, erzwingen wir, daß da nur Strings stehen dürfen, die als INT interpretierbar sind!

Das ganze ist entstanden heute, weil ich mal a bissl damit spielen wollte. Ich stehe da auch noch, da bin ich ehrlich, noch am Anfang. Aber das sieht echt spannend aus! Oder?[/code]

 

[Hony%]

Das sieht wirklich spannend aus. Ich habe dazu auch ein paar andere Quellen gelesen. Für mich sieht hstore eher nach einem Datentyp für Dokumentenspeicher als nach Key-Value aus. Ich würde daher eher mit CouchDB als mit Redis/Memcached vergleichen.

Zu Redis habe ich einen Vergleich gefunden: https://gist.github.com/NateBarnes/3001890

Allerdings unterschlägt der Autor das PostgreSQL Setup. Den es macht einen gewaltigen Unterschied ob die Daten von der Platte oder aus dem Speicher kommen. Das würde allerdings erklären warum PG bei 10000 Operationen im Vergleich so stark schwächelt.

 

[akretschmer]

Allerdings unterschlägt der Autor das PostgreSQL Setup. Den es macht einen gewaltigen Unterschied ob die Daten von der Platte oder aus dem Speicher kommen. Das würde allerdings erklären warum PG bei 10000 Operationen im Vergleich so stark schwächelt.

Ja, man könnte in PG die Tabelle als 'unlogged' erstellen, damit landet sie nicht im WAL. Vorteil: erheblich schneller im Schreiben. Nachteil: nach einem Crash ist die Tabelle leer und sie wird auch nicht repliziert. Als Ersatz für einen In-Memory-Cache aber sind die Nachteile eher egal.