By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tuser ( user_id INTEGER, data VARCHAR(40) );
CREATE TABLE admin ( user_id INTEGER, admin_id VARCHAR(40) );
CREATE TABLE cust ( user_id INTEGER, cust_id VARCHAR(40) );
CREATE TABLE login_history ( user_id INTEGER, login_on TIMESTAMP );
CREATE VIEW login_counts_view AS
SELECT week_start_dates.week_start_date::text AS week_start_date,
count(distinct a.user_id) AS admin_count,
count(distinct c.user_id) AS cust_count
FROM (
SELECT to_char(i::date, 'YYYY-MM-DD') AS week_start_date
FROM generate_series(date_trunc('year', NOW()), to_char(NOW(), 'YYYY-12-31')::date, '1 week') i
) week_start_dates
LEFT JOIN login_history l ON l.login_on::date BETWEEN week_start_dates.week_start_date::date AND (week_start_dates.week_start_date::date + INTERVAL '6 day')::date
LEFT JOIN admin a ON a.user_id = l.user_id
LEFT JOIN cust c ON c.user_id = l.user_id
GROUP BY week_start_date
;
CREATE VIEW login_counts_view_fast AS
WITH RECURSIVE Numbers(i) AS ( SELECT 0 UNION ALL SELECT i + 1 FROM Numbers WHERE i < 52 )
SELECT CAST ( date_trunc('year', NOW()) AS DATE) + 7 * n.i week_start_date
, count(distinct lw.admin_id) admin_count
, count(distinct lw.cust_id) cust_count
FROM (
SELECT i FROM Numbers
) n
LEFT JOIN (
SELECT admin_id
, cust_id
, base
, pit
, pit-base delta
, (pit-base) / (3600 * 24 * 7) week
FROM (
SELECT a.user_id admin_id
, c.user_id cust_id
, CAST ( EXTRACT ( EPOCH FROM l.login_on ) AS INTEGER ) pit
, CAST ( EXTRACT ( EPOCH FROM date_trunc('year', NOW()) ) AS INTEGER ) base
FROM login_history l
LEFT JOIN admin a ON a.user_id = l.user_id
LEFT JOIN cust c ON c.user_id = l.user_id
) le
) lw
ON lw.week = n.i
GROUP BY n.i
;
INSERT INTO tuser VALUES (1, 'user_1');
INSERT INTO tuser VALUES (2, 'user_2');
INSERT INTO tuser VALUES (3, 'user_3');
INSERT INTO tuser VALUES (4, 'user_4');
INSERT INTO admin VALUES (1, 'a');
INSERT INTO admin VALUES (4, 'b');
INSERT INTO cust VALUES (2, 'dd');
INSERT INTO cust VALUES (3, 'ff');
INSERT INTO login_history VALUES ( 1, '2022-01-01 13:22:43'::TIMESTAMP);
INSERT INTO login_history VALUES ( 1, '2022-01-02 16:16:27'::TIMESTAMP);
INSERT INTO login_history VALUES ( 3, '2022-01-05 21:17:52'::TIMESTAMP);
INSERT INTO login_history VALUES ( 2, '2022-01-11 11:12:26'::TIMESTAMP);
INSERT INTO login_history VALUES ( 3, '2022-01-12 03:34:47'::TIMESTAMP);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
explain (analyze,buffers)select * from login_counts_view;
| QUERY PLAN |
|---|
| GroupAggregate (cost=542154.11..563940.32 rows=200 width=48) (actual time=1.510..1.635 rows=53 loops=1) |
| Group Key: (to_char(((i.i)::date)::timestamp with time zone, 'YYYY-MM-DD'::text)) |
| Buffers: shared hit=12 |
| -> Sort (cost=542154.11..547599.78 rows=2178271 width=40) (actual time=1.422..1.431 rows=56 loops=1) |
| Sort Key: (to_char(((i.i)::date)::timestamp with time zone, 'YYYY-MM-DD'::text)) |
| Sort Method: quicksort Memory: 27kB |
| Buffers: shared hit=12 |
| -> Merge Left Join (cost=131272.21..193713.33 rows=2178271 width=40) (actual time=1.052..1.139 rows=56 loops=1) |
| Merge Cond: (l.user_id = c.user_id) |
| Buffers: shared hit=9 |
| -> Merge Left Join (cost=131227.25..142900.60 rows=702668 width=16) (actual time=1.013..1.034 rows=56 loops=1) |
| Merge Cond: (l.user_id = a.user_id) |
| Buffers: shared hit=8 |
| -> Sort (cost=131182.29..131748.96 rows=226667 width=12) (actual time=0.950..0.956 rows=56 loops=1) |
| Sort Key: l.user_id |
| Sort Method: quicksort Memory: 27kB |
| Buffers: shared hit=7 |
| -> Nested Loop Left Join (cost=0.02..107145.52 rows=226667 width=12) (actual time=0.328..0.875 rows=56 loops=1) |
| Join Filter: (((l.login_on)::date >= (to_char(((i.i)::date)::timestamp with time zone, 'YYYY-MM-DD'::text))::date) AND ((l.login_on)::date <= (((to_char(((i.i)::date)::timestamp with time zone, 'YYYY-MM-DD'::text))::date + '6 days'::interval))::date)) |
| Rows Removed by Join Filter: 260 |
| Buffers: shared hit=1 |
| -> Function Scan on generate_series i (cost=0.02..10.02 rows=1000 width=8) (actual time=0.289..0.295 rows=53 loops=1) |
| -> Materialize (cost=0.00..40.60 rows=2040 width=12) (actual time=0.000..0.001 rows=5 loops=53) |
| Buffers: shared hit=1 |
| -> Seq Scan on login_history l (cost=0.00..30.40 rows=2040 width=12) (actual time=0.011..0.014 rows=5 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=44.96..46.51 rows=620 width=4) (actual time=0.037..0.038 rows=3 loops=1) |
| Sort Key: a.user_id |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=1 |
| -> Seq Scan on admin a (cost=0.00..16.20 rows=620 width=4) (actual time=0.013..0.013 rows=2 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=44.96..46.51 rows=620 width=4) (actual time=0.032..0.033 rows=2 loops=1) |
| Sort Key: c.user_id |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=1 |
| -> Seq Scan on cust c (cost=0.00..16.20 rows=620 width=4) (actual time=0.006..0.007 rows=2 loops=1) |
| Buffers: shared hit=1 |
| Planning: |
| Buffers: shared hit=108 read=11 dirtied=1 |
| Planning Time: 0.960 ms |
| Execution Time: 2.089 ms |
explain (analyze,buffers)select * from login_counts_view_fast;
| QUERY PLAN |
|---|
| Subquery Scan on login_counts_view_fast (cost=408.64..440.02 rows=31 width=20) (actual time=0.624..0.800 rows=53 loops=1) |
| Buffers: shared hit=3 |
| -> GroupAggregate (cost=408.64..439.71 rows=31 width=24) (actual time=0.622..0.790 rows=53 loops=1) |
| Group Key: numbers.i |
| Buffers: shared hit=3 |
| CTE numbers |
| -> Recursive Union (cost=0.00..2.95 rows=31 width=4) (actual time=0.002..0.051 rows=53 loops=1) |
| -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1) |
| -> WorkTable Scan on numbers numbers_1 (cost=0.00..0.23 rows=3 width=4) (actual time=0.000..0.000 rows=1 loops=53) |
| Filter: (i < 52) |
| Rows Removed by Filter: 0 |
| -> Sort (cost=405.68..413.28 rows=3038 width=12) (actual time=0.493..0.511 rows=56 loops=1) |
| Sort Key: numbers.i |
| Sort Method: quicksort Memory: 27kB |
| Buffers: shared hit=3 |
| -> Merge Left Join (cost=165.65..229.95 rows=3038 width=12) (actual time=0.422..0.462 rows=56 loops=1) |
| Merge Cond: (l.user_id = c.user_id) |
| Buffers: shared hit=3 |
| -> Merge Left Join (cost=120.69..136.97 rows=980 width=12) (actual time=0.397..0.425 rows=56 loops=1) |
| Merge Cond: (l.user_id = a.user_id) |
| Buffers: shared hit=2 |
| -> Sort (cost=75.74..76.53 rows=316 width=8) (actual time=0.370..0.384 rows=56 loops=1) |
| Sort Key: l.user_id |
| Sort Method: quicksort Memory: 27kB |
| Buffers: shared hit=1 |
| -> Hash Right Join (cost=1.01..62.62 rows=316 width=8) (actual time=0.310..0.343 rows=56 loops=1) |
| Hash Cond: ((((EXTRACT(epoch FROM l.login_on))::integer - (EXTRACT(epoch FROM date_trunc('year'::text, now())))::integer) / 604800) = numbers.i) |
| Buffers: shared hit=1 |
| -> Seq Scan on login_history l (cost=0.00..30.40 rows=2040 width=12) (actual time=0.009..0.011 rows=5 loops=1) |
| Buffers: shared hit=1 |
| -> Hash (cost=0.62..0.62 rows=31 width=4) (actual time=0.109..0.110 rows=53 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 10kB |
| -> CTE Scan on numbers (cost=0.00..0.62 rows=31 width=4) (actual time=0.004..0.066 rows=53 loops=1) |
| -> Sort (cost=44.96..46.51 rows=620 width=4) (actual time=0.024..0.024 rows=3 loops=1) |
| Sort Key: a.user_id |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=1 |
| -> Seq Scan on admin a (cost=0.00..16.20 rows=620 width=4) (actual time=0.008..0.009 rows=2 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=44.96..46.51 rows=620 width=4) (actual time=0.023..0.023 rows=2 loops=1) |
| Sort Key: c.user_id |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=1 |
| -> Seq Scan on cust c (cost=0.00..16.20 rows=620 width=4) (actual time=0.007..0.007 rows=2 loops=1) |
| Buffers: shared hit=1 |
| Planning Time: 0.506 ms |
| Execution Time: 1.064 ms |