Timeseries query in PostgreSQL without TimescaleDB

[ctrlcctrlv]

If I understood @KunstDerFuge right in Discord this is what he's looking for.

EXPLAIN ANALYZE SELECT INTERVAL_DATE_TRUNC('15 minutes'::INTERVAL, pp."timestamp"), COUNT(*) FROM posts_post_documents JOIN posts_post_fk_view pp USING(id) WHERE TO_TSVECTOR('english'::REGCONFIG, document) @@ TO_TSQUERY('kek') GROUP BY INTERVAL_DATE_TRUNC('15 minutes'::INTERVAL, pp."timestamp");

QUERY PLAN
GroupAggregate (cost=130712.35..134937.52 rows=15795 width=16) (actual time=193.963..195.688 rows=6118 loops=1)
Group Key: (interval_date_trunc('00:15:00'::interval, pp."timestamp", '1969-12-31 19:00:00-05'::timestamp with time zone))
-> Sort (cost=130712.35..130751.84 rows=15795 width=8) (actual time=193.946..194.463 rows=15668 loops=1)
Sort Key: (interval_date_trunc('00:15:00'::interval, pp."timestamp", '1969-12-31 19:00:00-05'::timestamp with time zone))
Sort Method: quicksort Memory: 1119kB
-> Nested Loop (cost=163.11..129610.88 rows=15795 width=8) (actual time=84.972..191.827 rows=15668 loops=1)
-> Bitmap Heap Scan on posts_post_documents (cost=162.68..43154.49 rows=15797 width=8) (actual time=84.865..115.671 rows=15668 loops=1)
Recheck Cond: (to_tsvector('english'::regconfig, document) @@ to_tsquery('kek'::text))
Heap Blocks: exact=13412
-> Bitmap Index Scan on posts_post_document_merged_bodies_tsvector_search_idx (cost=0.00..158.73 rows=15797 width=0) (actual time=83.218..83.218 rows=15668 loops=1)
Index Cond: (to_tsvector('english'::regconfig, document) @@ to_tsquery('kek'::text))
-> Index Scan using posts_post_fk_view_pkey on posts_post_fk_view pp (cost=0.43..5.21 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=15668)
Index Cond: (id = posts_post_documents.id)
Planning Time: 0.320 ms
JIT:
Functions: 12
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.556 ms, Inlining 7.851 ms, Optimization 43.878 ms, Emission 29.841 ms, Total 82.126 ms
Execution Time: 196.430 ms

(19 rows)

Example 100 lines:

interval_date_trunc	count
2020-12-25 19:45:00-05	1
2020-12-21 04:45:00-05	1
2020-11-13 00:30:00-05	6
2020-11-13 00:15:00-05	5
2020-11-13 00:00:00-05	3
2020-11-12 22:30:00-05	1
2020-11-12 22:15:00-05	3
2020-11-12 22:00:00-05	6
2020-11-12 21:45:00-05	3
2020-11-12 21:30:00-05	2
2020-11-08 07:30:00-05	1
2020-11-03 01:45:00-05	4
2020-11-03 01:30:00-05	5
2020-11-03 01:15:00-05	3
2020-11-03 01:00:00-05	1
2020-11-02 17:45:00-05	3
2020-11-02 17:30:00-05	3
2020-11-02 17:15:00-05	1
2020-10-31 18:45:00-04	2
2020-10-31 18:30:00-04	1
2020-10-30 11:45:00-04	4
2020-10-30 11:30:00-04	1
2020-10-30 11:15:00-04	2
2020-10-30 10:45:00-04	2
2020-10-30 02:00:00-04	2
2020-10-30 01:45:00-04	1
2020-10-30 01:30:00-04	2
2020-10-30 01:15:00-04	4
2020-10-30 00:45:00-04	3
2020-10-30 00:30:00-04	1
2020-10-22 20:00:00-04	9
2020-10-22 19:45:00-04	5
2020-10-22 18:00:00-04	1
2020-10-22 17:45:00-04	2
2020-10-22 17:30:00-04	1
2020-10-22 14:15:00-04	4
2020-10-22 14:00:00-04	7
2020-10-22 13:45:00-04	1
2020-10-22 12:30:00-04	2
2020-10-22 11:45:00-04	1
2020-10-22 11:30:00-04	2
2020-10-22 11:15:00-04	3
2020-10-22 11:00:00-04	3
2020-10-22 02:45:00-04	1
2020-10-22 02:30:00-04	6
2020-10-22 02:15:00-04	4
2020-10-22 02:00:00-04	8
2020-10-22 01:45:00-04	4
2020-10-22 01:30:00-04	3
2020-10-22 01:15:00-04	6
2020-10-22 01:00:00-04	2
2020-10-22 00:45:00-04	6
2020-10-22 00:30:00-04	3
2020-10-21 22:45:00-04	6
2020-10-21 22:30:00-04	2
2020-10-21 22:15:00-04	4
2020-10-21 21:30:00-04	1
2020-10-21 21:15:00-04	2
2020-10-21 21:00:00-04	3
2020-10-21 20:45:00-04	4
2020-10-21 20:30:00-04	7
2020-10-21 20:15:00-04	3
2020-10-21 19:00:00-04	1
2020-10-21 18:45:00-04	5
2020-10-21 18:30:00-04	3
2020-10-21 18:15:00-04	1
2020-10-21 15:15:00-04	1
2020-10-21 15:00:00-04	1
2020-10-21 14:45:00-04	2
2020-10-21 14:30:00-04	5
2020-10-21 14:15:00-04	1
2020-10-21 14:00:00-04	2
2020-10-21 13:45:00-04	1
2020-10-21 13:30:00-04	1
2020-10-21 13:15:00-04	2
2020-10-21 09:30:00-04	1
2020-10-21 09:00:00-04	1
2020-10-21 08:15:00-04	1
2020-10-20 16:45:00-04	2
2020-10-20 16:30:00-04	7
2020-10-20 16:15:00-04	2
2020-10-20 15:00:00-04	1
2020-10-19 01:30:00-04	1
2020-10-19 01:15:00-04	4
2020-10-19 01:00:00-04	2
2020-10-19 00:30:00-04	2
2020-10-18 16:15:00-04	1
2020-10-18 16:00:00-04	2
2020-10-18 15:45:00-04	4
2020-10-18 15:30:00-04	2
2020-10-18 15:15:00-04	4
2020-10-18 15:00:00-04	2
2020-10-18 14:45:00-04	3
2020-10-18 14:30:00-04	2
2020-10-18 13:30:00-04	2
2020-10-18 13:15:00-04	3
2020-10-18 13:00:00-04	5
2020-10-18 12:45:00-04	1
2020-10-18 12:30:00-04	7
2020-10-18 12:15:00-04	2

(100 rows)

[KunstDerFuge]

I was not aware aggregates were even possible natively with postgres, but your example output looks more or less exactly like what I'm getting from elasticsearch currently. Re: what you said in #1, I think this is such a valuable and exceptional case, and eliminating elasticsearch so noble a task, that an exception should definitely be made here on the raw SQL thing if necessary. The existing code is actually more convoluted than this, anyway.