On Fri, Aug 1, 2014 at 10:48 AM, Michael Paquier
<michael.paquier@xxxxxxxxx> wrote:
> On Fri, Aug 1, 2014 at 4:47 AM, Larry White <ljw1001@xxxxxxxxx> wrote:
>> Is there a way to get Postgres to index the table as if the JSON were there,
>> but not actually put the data in the table?
>> I could either store the docs
>> elsewhere and keep a reference, or compress them and put them in the table
>> in compressed form as a blob.
> No. This is equivalent to the creation of an index on a foreign table.
Not sure exactly if it applies here; but I seem to recall reading
somewhere that you can index "generated" columns. Something like
following (this example is similar what I recall seeing there)
postgres=# CREATE TABLE test(a, b) AS SELECT md5(g::text)::char(10),
md5(g::text)::char(5) FROM generate_series(1, 100000) g;
SELECT 100000
postgres=# CREATE OR REPLACE FUNCTION ab(rec test) RETURNS text AS $$
SELECT rec.a || rec.b; $$ STABLE LANGUAGE SQL;
CREATE FUNCTION
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
postgres=# CREATE INDEX test_idx ON test USING GIN (ab(test) gin_trgm_ops);
CREATE INDEX
postgres=# EXPLAIN SELECT * FROM test WHERE ab(test) LIKE '%c4c%';
QUERY PLAN
-------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=16.09..52.53 rows=10 width=17)
Recheck Cond: (((a)::text || (b)::text) ~~ '%c4c%'::text)
-> Bitmap Index Scan on test_idx (cost=0.00..16.08 rows=10 width=0)
Index Cond: (((a)::text || (b)::text) ~~ '%c4c%'::text)
Planning time: 0.361 ms
(5 rows)
--
Amit
