Poor performing query re-write using tsrange index

Поиск
Список
Период
Сортировка
От Burgess, Freddie
Тема Poor performing query re-write using tsrange index
Дата
Msg-id 3BBE635F64E28D4C899377A61DAA9FE04524390B@NBSVR-MAIL01.radiantblue.local
обсуждение исходный текст
Список 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.


В списке pgsql-performance по дате отправления:

Предыдущее
От: Gerardo Herzig
Дата:
Сообщение: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
Следующее
От: Pietro Pugni
Дата:
Сообщение: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL