On Mon, May 28, 2012 at 10:45 AM, Marti Raudsepp <marti@juffo.org> wrote:
> Query:
> SELECT '2012-05-28T09:00:00', count(*),
> uniq(sort(array_agg(visitor_id))), banner_id, client_body_id,
> partner_body_id, space_id, campaign_id, evt_type_id FROM stats_request
> WHERE stats_request.request_time >= '2012-05-28T09:00:00' AND
> stats_request.request_time < (timestamp '2012-05-28T09:00:00' +
> interval E'1 hour')::timestamp
> GROUP BY banner_id, client_body_id, partner_body_id, space_id,
> campaign_id, evt_type_id ORDER BY banner_id, client_body_id,
> partner_body_id, space_id, campaign_id, evt_type_id;
Oh, I forgot to mention that stats_request is a view and that's where
the JOIN is coming from:
CREATE VIEW stats_request ASSELECT a.request_id, a.request_time, b.b2s_id, a.evt_type_id,
b.space_id, b.banner_id, COALESCE(b.visitor_id, a.visitor_id) AS
visitor_id, COALESCE(b.partner_body_id, a.partner_body_id) AS
partner_body_id, b.client_body_id, b.campaign_id FROM request a JOIN request_data b USING (request_id);
request and request_data are both large partitioned tables.
Regards,
Marti