On Saturday 11 April 2009 00:41:54 Jasen Betts wrote:
On 2009-04-02, Alvaro Herrera alvhe...@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-module btree_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 Heap Scan on onp_crm_activity_log (cost=10.56..232.62 rows=76 width=4)
(actual time=0.175..0.175 rows=0 loops=1)
Recheck Cond: (('2008-12-07 07:59:59'::timestamp without time zone =
start_time) AND ('2008-12-07 07:59:59'::timestamp without time 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 time zone =
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 Scan on 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'::timestamp without 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 andr...@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 | |
+-+
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
