Poor performing query re-write using tsrange index

From: "Burgess, Freddie" <FBurgess(at)Radiantblue(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Poor performing query re-write using tsrange index
Date: 2015-04-02 03:26:48
Message-ID: 3BBE635F64E28D4C899377A61DAA9FE04524390B@NBSVR-MAIL01.radiantblue.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Browse pgsql-performance by date

  From Date Subject
Next Message Pietro Pugni 2015-04-02 10:33:15 Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
Previous Message Gerardo Herzig 2015-04-02 02:19:38 Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL