Postgres 14

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