-- tables
-- New column "span" added and new index created on both tables.
CREATE TABLE customer(
uid bigserial PRIMARY KEY,
name character varying(50) NOT NULL,
start_time timestamp without time zone,
end_time timestamp without time zone,
span tsrange,
comment text,
created timestamp without time zone DEFAULT now()
);
CREATE INDEX sidx_customer ON customer USING GiST (uid, span);
CREATE TABLE customer_log (
uid SERIAL PRIMARY KEY,
action character varying(32) NOT NULL,
start_time timestamp without time zone,
end_time timestamp without time zone,
customer_uid bigint,
span tsrange,
comment text,
created timestamp without time zone DEFAULT now()
);
CREATE INDEX sidx_customer_log ON customer_log USING GiST (customer_uid, span);
-- current query
EXPLAIN (analyze, buffers)
SELECT * FROM CUSTOMER t JOIN CUSTOMER_LOG tr ON t.uid = tr.customer_uid
WHERE t.start_time <= '2050-01-01 00:00:00'::timestamp without time zone AND t.end_time >= '1970-01-01 00:00:00'::timestamp without time zone
AND tr.start_time <= '2050-01-01 00:00:00'::timestamp without time zone AND tr.end_time >= '1970-01-01 00:00:00'::timestamp without time zone
AND tr.action like 'LOGIN'
ORDER BY t.uid asc limit 1000;
Question/Problem:
How to rewrite this query to leverage tsrange?
i.e.
SELECT *
FROM customer t JOIN customer_log tr ON t.uid = tr.customer_uid
WHERE t.span @> tsrange('1970-01-01 00:00:00', '2050-01-01 00:00:00', '[]')
AND tr.span @> tsrange('1970-01-01 00:00:00', '2050-01-01 00:00:00', '[]')
AND tr.action like 'LOGIN'
ORDER BY t.uid asc limit 1000;
Thanks in advance for any assistance with this query.
