I'm trying to create an index that will support queries that use my custom operator. This is on PostgreSQL 10.4.
The custom operator
I followed the tips in this SO answer to create an operator that performs "LIKE" style matching on elements in an text ARRAY.
CREATE FUNCTION reverse_like (text, text) returns boolean language sql
as $$ select $2 like $1 $$;
CREATE OPERATOR <~~ ( function =reverse_like, leftarg = text, rightarg=text );
The above operator allows me to do things like
SELECT 'ab%' <~~ ANY('{"abc","def"}');
The schema, index and query
I have a table with web traffic visits called sessions
which includes an array column.
CREATE TABLE sessions
(
session_id varchar(24) NOT NULL,
first_seen timestamp,
domains varchar[]
);
To query the domains column to see if a given domain (or partial/ wildcarded domain name) was visited I can do the following:
SELECT count(*)
FROM session_4070ba14_f081_41cb_9ef7_9dd385934da7
WHERE 'www.foo%' <~~ ANY(domains);
I want to speed up the above queries with GIN index. So I created the index as follows:
CREATE INDEX idx_domains ON session USING GIN(domains);
The Question
After running analyze on the table and a set enable_seqscan = false;
I have no luck getting Postgres to employ this index. It's always doing a seqscan. It uses the above index of array operators like @>
but not for my custom <~~
operator.
I think its because the GIN index doesn't know how to handle my custom operator - so do I need to create an operator class and then create my index using that? Or do I create a functional index?