Re: Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps

On Saturday 11 April 2009 00:41:54 Jasen Betts wrote:
> On 2009-04-02, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > James Kitambara wrote:
> >> Dear Srikanth,
> >> You can solve your problem by doing this
> >>
> >> THE SQL IS AS FOLLOWS
> >>   ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND THE TABLE NAME time_interval
> >>
> >>  COUNT (*) FROM
> >>     (select customer_id, log_session_id, start_ts, end_ts , end_ts-start_ts as "Interval" from time_interval
> >>      where end_ts-start_ts >= '1 hour'
> >>      and '2008-12-07 07:59:59' between start_ts and end_ts)
> >> AS COUNT ;
> >
> > Another way to phrase the WHERE clause is with the OVERLAPS operator,
> > something like this:
> >
> > WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07 08:59:59')
> >
> > What I'm not so sure about is how optimizable this construct is.
> >
>
> http://www.postgresql.org/docs/8.3/interactive/xindex.html
> if you gave the apropriate GIST index on (start_ts, end_ts) the
> overlaps may be optimisable. the subquery will run to completion
> and count will count the results. - but this form gives different results.
>
> beter to do
>
> select COUNT (*) AS COUNT FROM time_interval
>   WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07 08:59:59')
>
> or
>
> select COUNT (*) AS COUNT FROM time_interval
>   where end_ts-start_ts >= '1 hour'
>   and '2008-12-07 07:59:59' between start_ts and end_ts;

I only managed to get this to use the gist-index, and not with the overlaps operator. I had to install the
contrib-modulebtree_gist in order to be able to create a gist index on the timestamps. 

This is my index:

CREATE index origo_tart_end_time_idx on onp_crm_activity_log using gist (start_time, end_time) ;

start_time and end_time are both timestamps.

Here are the EXPLAIN outputs:

andreak=# EXPLAIN ANALYZE select id from onp_crm_activity_log where end_time - start_time >= '1 hour' AND '2008-12-07
07:59:59'between start_time and end_time;                                                                         QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on onp_crm_activity_log  (cost=10.56..232.62 rows=76 width=4) (actual time=0.175..0.175 rows=0 loops=1)
RecheckCond: (('2008-12-07 07:59:59'::timestamp without time zone >= start_time) AND ('2008-12-07 07:59:59'::timestamp
withouttime zone <= end_time))  Filter: ((end_time - start_time) >= '01:00:00'::interval)  ->  Bitmap Index Scan on
origo_tart_end_time_idx (cost=0.00..10.54 rows=229 width=0) (actual time=0.168..0.168 rows=0 loops=1)        Index
Cond:(('2008-12-07 07:59:59'::timestamp without time zone >= start_time) AND ('2008-12-07 07:59:59'::timestamp without
timezone <= end_time))Total runtime: 0.274 ms 
(6 rows)

andreak=# EXPLAIN ANALYZE select id from onp_crm_activity_log where (start_time, end_time) OVERLAPS('2008-11-07
07:59:59'::timestamp,'2008-12-07 08:59:59'::timestamp);
  QUERY PLAN 

----------------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon onp_crm_activity_log  (cost=0.00..319.29 rows=2968 width=4) (actual time=14.542..15.794 rows=83 loops=1)
Filter:"overlaps"(start_time, end_time, '2008-11-07 07:59:59'::timestamp without time zone, '2008-12-07
08:59:59'::timestampwithout time zone)Total runtime: 16.129 ms 
(3 rows)

Is it possible to make the overlaps operator use the index? I'd prefer the overlaps-syntax as I find it cleaner.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Rosenholmveien 25       | know how to do a thing and to watch         |
1414 Trollåsen          | somebody else doing it wrong, without       |
NORWAY                  | comment.                                    |                       |
                   | 
Tlf:    +47 24 15 38 90 |                                             |
Fax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+