Re: Is it possible to create an index without keeping the indexed data in a column?

On Fri, Aug 1, 2014 at 10:48 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> On Fri, Aug 1, 2014 at 4:47 AM, Larry White <ljw1001(at)gmail(dot)com> 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