Administrace komentářů

v 9.1 lze pouzit trigramy a indexovat i like, kde je zolik vlevo.

Jinak asi by bylo mozne navrhnout i neco efektivnejsiho, ale vyzadovalo by to vlastni typ indexu - a vicemene psani v C. Pak by to asi mohlo byt opravdu efektivni

Pripadne si pro kazdou pozici muzete udelat index - ale nejde to tak jak si predstavujete.

CREATE TABLE foo(s text);
CREATE INDEX ON foo USING GIN ((substring(s FROM 1 FOR 1));
CREATE INDEX ON foo USING GIN ((substring(s FROM 2 FOR 1));
CREATE INDEX ON foo USING GIN ((substring(s FROM 3 FOR 1));

SELECT * FROM foo WHERE substring(s FROM 3 FOR 1) = 'x';

tahle podminka bude mit dost mizernou selectivitu - dost mozna ze problem je jenom v like a stacilo by nepouzivat like

SELECT * FROM foo WHERE substring(s FROM 3 FOR 1) = 'x';

Jeste by slo pouzit hstore:

postgres=# create or replace function pack(text)
returns hstore as $$
SELECT string_agg(i || '=>' || substring($1 FROM i FOR 1),',')::hstore 
FROM generate_series(1,length($1)) g(i); $$ 
LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
postgres=# select pack('Pavel');
                       pack                       
--------------------------------------------------
 "1"=>"P", "2"=>"a", "3"=>"v", "4"=>"e", "5"=>"l"
(1 row)

postgres=# select * from xx;
   a   
-------
 Pavel
(1 row)

postgres=# CREATE INDEX ON xx USING GIN((pack(a)));
CREATE INDEX

postgres=# SELECT * FROM xx WHERE pack(a) @> '3=>v';
   a   
-------
 Pavel
(1 row)

postgres=# set enable_seqscan to off;
SET
postgres=# explain SELECT * FROM xx WHERE pack(a) @> '3=>v';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Bitmap Heap Scan on xx  (cost=12.25..16.51 rows=1 width=32)
   Recheck Cond: (pack(a) @> '"3"=>"v"'::hstore)
   ->  Bitmap Index Scan on xx_pack_idx  (cost=0.00..12.25 rows=1 width=0)
         Index Cond: (pack(a) @> '"3"=>"v"'::hstore)
(4 rows)

postgres=# SELECT * FROM xx WHERE pack(a) @> '3=>v, 4=>e';
   a   
-------
 Pavel
(1 row)

postgres=# SELECT * FROM xx WHERE pack(a) @> '3=>v, 4=>x';
 a 
---
(0 rows)

Ale co bude rychlejsi netusim - hstore je hodne genericky, takze pokud by tam byla nizka selectivita, tak by to nemuselo byt efektivni