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