Preliminary notes
You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would be much more efficient for multiple reasons. (Or bigint, if you plan to burn more than 2 billion rows over the lifetime of the table.) Related:
LIKE is pointless without wildcards. Use = instead. Faster.
x.location_host LIKE '2015.testonline.ca'
x.location_host = '2015.testonline.ca'
Use count(e1.*) or count(*) instead of adding a dummy column with the value 1 for each subquery. (Except for the last (e3), where you don't need any actual data.)
You're inconsistent in sometimes casting the string literal to timestamp and sometimes not (timestamp '2016-04-30 23:59:59.999'). Either it makes sense, then do it all the time, or it doesn't, then don't do it.
It doesn't. When compared to a timestamp column, a string literal is coerced to timestamp anyway. So you don't need an explicit cast.
The Postgres data type timestamp has up to 6 fractional digits. Your BETWEEN expressions leave corner cases. I replaced them with less error-prone expressions.
Indexes
Important: to optimize performance create multicolumn indexes.
For the first subquery hp:
CREATE INDEX event_pg_location_host_timestamp__idx
ON event_pg (location_host, timestamp_);
Or, if you can get index-only scans out of it, append person_id to the index:
CREATE INDEX event_pg_location_host_timestamp__person_id_idx
ON event_pg (location_host, timestamp_, person_id);
For very large time ranges spanning most or all of the table, this index should be preferable - it also supports the hlp subquery, so create it either way:
CREATE INDEX event_pg_location_host_person_id_timestamp__idx
ON event_pg (location_host, person_id, timestamp_);
For tnk:
CREATE INDEX event_pg_location_fragment_timestamp__idx
ON event_pg (location_fragment, person_id, timestamp_);
If your predicates on location_host and location_fragment are constants, we can use much cheaper partial indexes instead, especially since your location_* columns seem big:
CREATE INDEX event_pg_hp_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE location_host = '2015.testonline.ca';
CREATE INDEX event_pg_hlp_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE location_host = 'helpcentre.testonline.ca';
CREATE INDEX event_pg_tnk_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE location_fragment = '/file/thank-you';
Consider:
Again, all of these indexes are substantially smaller and faster with integer or bigint for person_id.
Generally, you need to ANALYZE the table after creating a new index - or wait till autovacuum kicks in to do it for you.
To get index-only scans, your table has to be VACUUM'ed enough. Test immediately after VACUUM as proof of concept. Read the linked Postgres Wiki page for details if you are unfamiliar with index-only scans.
Basic Query
Implementing what I discussed. Query for small ranges (few rows per person_id):
SELECT count(*)::int AS view_homepage
, count(hlp.hlp_ts)::int AS use_help
, count(tnk.yes)::int AS thank_you
FROM (
SELECT DISTINCT ON (person_id)
person_id, timestamp_ AS hp_ts
FROM event_pg
WHERE timestamp_ >= '2016-04-23'
AND timestamp_ < '2016-05-01'
AND location_host = '2015.testonline.ca'
ORDER BY person_id, timestamp_
) hp
LEFT JOIN LATERAL (
SELECT timestamp_ AS hlp_ts
FROM event_pg y
WHERE y.person_id = hp.person_id
AND timestamp_ >= hp.hp_ts
AND timestamp_ < '2016-05-01'
AND location_host = 'helpcentre.testonline.ca'
ORDER BY timestamp_
LIMIT 1
) hlp ON true
LEFT JOIN LATERAL (
SELECT true AS yes -- we only need existence
FROM event_pg z
WHERE z.person_id = hp.person_id -- we can use hp here
AND location_fragment = '/file/thank-you'
AND timestamp_ >= hlp.hlp_ts -- this introduces dependency on hlp anyways.
AND timestamp_ < '2016-05-01'
ORDER BY timestamp_
LIMIT 1
) tnk ON true;
DISTINCT ON is often cheaper for few rows per person_id. Detailed explanation:
If you have many rows per person_id (more likely for bigger time ranges), the recursive CTE discussed in this answer in chapter 1a can be (much) faster:
See it integrated below.
Optimize & automate best query
It's the old conundrum: one query technique is best for a smaller set, another for a larger set. In your particular case we have a very good indicator from the start - the length of the given time period - which we can use to decide.
We wrap it all in a PL/pgSQL function. My implementation switches from DISTINCT ON to rCTE when the given time period is longer than a set threshold:
CREATE OR REPLACE FUNCTION f_my_counts(_ts_low_inc timestamp, _ts_hi_excl timestamp)
RETURNS TABLE (view_homepage int, use_help int, thank_you int) AS
$func$
BEGIN
CASE
WHEN _ts_hi_excl <= _ts_low_inc THEN
RAISE EXCEPTION 'Timestamp _ts_hi_excl (1st param) must be later than _ts_low_inc!';
WHEN _ts_hi_excl - _ts_low_inc < interval '10 days' THEN -- example value !!!
-- DISTINCT ON for few rows per person_id
RETURN QUERY
WITH hp AS (
SELECT DISTINCT ON (person_id)
person_id, timestamp_ AS hp_ts
FROM event_pg
WHERE timestamp_ >= _ts_low_inc
AND timestamp_ < _ts_hi_excl
AND location_host = '2015.testonline.ca'
ORDER BY person_id, timestamp_
)
, hlp AS (
SELECT hp.person_id, hlp.hlp_ts
FROM hp
CROSS JOIN LATERAL (
SELECT timestamp_ AS hlp_ts
FROM event_pg
WHERE person_id = hp.person_id
AND timestamp_ >= hp.hp_ts
AND timestamp_ < _ts_hi_excl
AND location_host = 'helpcentre.testonline.ca' -- match partial idx
ORDER BY timestamp_
LIMIT 1
) hlp
)
SELECT (SELECT count(*)::int FROM hp) -- AS view_homepage
, (SELECT count(*)::int FROM hlp) -- AS use_help
, (SELECT count(*)::int -- AS thank_you
FROM hlp
CROSS JOIN LATERAL (
SELECT 1 -- we only care for existence
FROM event_pg
WHERE person_id = hlp.person_id
AND location_fragment = '/file/thank-you'
AND timestamp_ >= hlp.hlp_ts
AND timestamp_ < _ts_hi_excl
ORDER BY timestamp_
LIMIT 1
) tnk
);
ELSE
-- rCTE for many rows per person_id
RETURN QUERY
WITH RECURSIVE hp AS (
( -- parentheses required
SELECT person_id, timestamp_ AS hp_ts
FROM event_pg
WHERE timestamp_ >= _ts_low_inc
AND timestamp_ < _ts_hi_excl
AND location_host = '2015.testonline.ca' -- match partial idx
ORDER BY person_id, timestamp_
LIMIT 1
)
UNION ALL
SELECT x.*
FROM hp, LATERAL (
SELECT person_id, timestamp_ AS hp_ts
FROM event_pg
WHERE person_id > hp.person_id -- lateral reference
AND timestamp_ >= _ts_low_inc -- repeat conditions
AND timestamp_ < _ts_hi_excl
AND location_host = '2015.testonline.ca' -- match partial idx
ORDER BY person_id, timestamp_
LIMIT 1
) x
)
, hlp AS (
SELECT hp.person_id, hlp.hlp_ts
FROM hp
CROSS JOIN LATERAL (
SELECT timestamp_ AS hlp_ts
FROM event_pg y
WHERE y.person_id = hp.person_id
AND location_host = 'helpcentre.testonline.ca' -- match partial idx
AND timestamp_ >= hp.hp_ts
AND timestamp_ < _ts_hi_excl
ORDER BY timestamp_
LIMIT 1
) hlp
)
SELECT (SELECT count(*)::int FROM hp) -- AS view_homepage
, (SELECT count(*)::int FROM hlp) -- AS use_help
, (SELECT count(*)::int -- AS thank_you
FROM hlp
CROSS JOIN LATERAL (
SELECT 1 -- we only care for existence
FROM event_pg
WHERE person_id = hlp.person_id
AND location_fragment = '/file/thank-you'
AND timestamp_ >= hlp.hlp_ts
AND timestamp_ < _ts_hi_excl
ORDER BY timestamp_
LIMIT 1
) tnk
);
END CASE;
END
$func$ LANGUAGE plpgsql STABLE STRICT;
Call:
SELECT * FROM f_my_counts('2016-01-23', '2016-05-01');
The rCTE works with a CTE by definition. I also slipped in CTEs for the DISTINCT ON query (like I discussed with @Lennart in the comments), which allows us to use CROSS JOIN instead of LEFT JOIN to reduce the set with each step, since we can count each CTE separately. This has effects working in opposite directions:
- On the one had we reduce the number of rows which should make the third join cheaper.
- On the other hand we introduce overhead for the CTEs and need considerably more RAM, which may be particularly important for big queries like yours.
You'll have to test which outweighs the other.
