-- 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. |