akretschmer
Datenbank-Guru
- Beiträge
- 10.370
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)
Soweit, so gut.
Angenommen, ich möchte Abfragen auf die Eigenschaft Farbe via Index beschleunigen:
Soweit, so gut.
Was ist, wenn ich nach Gewicht suche, und zwar was mehr als 5 kg hat?
Schaut gut aus, aber:
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:
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:
Paßt!
Was passiert nun eigenlich, wenn wir textuell ein Gewicht definieren, was kein INT ist? Probieren wir es aus!
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]
Kurze Demo.
(set enable_seqscan = off; gesetzt, um, wenn möglich, Index-Scans zu erzwingen)
Code:
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:
Code:
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?
Code:
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:
Code:
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:
Code:
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:
Code:
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!
Code:
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]