Poor performing query re-write using tsrange index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux