Search Postgresql Archives

count number of concurrent requests

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

I have the following table:

dwh=> \d events
                 Table "public.events"
      Column      |            Type             | Modifiers
------------------+-----------------------------+-----------
 datetime         | timestamp without time zone |
 request_duration | integer                     |

dwh=> select count(*) from events;
  count
----------
 82912116
(1 row)

Each row represents one event which started at 'datetime' and finished
at 'datetime +  request_duration'. I would like to know how many other
events started between 'datetime' and ''datetime +  request_duration'
(ie. concurrency).

I've started with this query (CTE + join):
with e as (
    select
        datetime as date_s,
        datetime + (request_duration::text || ' msec')::interval as date_e,
        request_duration
    from events
    where datetime < '2012-08-01 00:01:00'
)
select
    e1.date_s,
    e1.date_e,
    count(*) as "count",
    count(case when e1.request_duration > 1000 then true else null
end) as "over 1000"
from e as e1
left join e as e2 on (e2.date_s between e1.date_s and e1.date_e)
group by e1.date_s, e1.date_e
having count(case when e1.request_duration > 1000 then true else null end) > 0

which is incredibly slow (as expected) and I can not analyse more than
several minutes of real traffic. I need to run this query over few
days at least. Second try was this one:

select
    date_s,
    date_e,
    counts[1] as "count",
    counts[2] as "over 1000"
from (
    select
        datetime as date_s,
        datetime + (request_duration::text || ' msec')::interval as date_e,
        (
            select
                array[
                    count(*),
                    count(case when ee.request_duration > 1000 then
true else null end)
                ]
            from events ee
            where ee.datetime < '2012-08-01 00:01:00'
                and ee.datetime >= e.datetime
                and ee.datetime <= e.datetime +
(e.request_duration::text || ' msec')::interval
        ) as counts
    from events as e
    where datetime < '2012-08-01 00:01:00'
) as x
where counts[2] > 0

Which is much better (like few minutes per day) but I can not use any
of those queries because:
- events table is partitioned so the first one is not "going to work"
at all and second could be fixed by adding date ranges to "select ...
from events ee" query
- and I would like to have one query across two database platform but
other one doesn't support "correlated subquery"

Hence I've written simple PHP script which looks like "Window
Function". I tried to rewrite query using window function(s) but I
can't get my head around it.

Any ideas? I'm looking for something which is relatively fast and
doesn't use "correlated subquery"...

Thanks!

-- 
Ondrej Ivanic
(ondrej.ivanic@xxxxxxxxx)


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux