Postgres 11

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 dictionary_word (headword text, dict_pages jsonb[], timestamp_updated timestamp);
insert into dictionary_word (headword, dict_pages, timestamp_updated) values ('a6', '{229}', '2023-07-28 14:49:13+00');
insert into dictionary_word (headword, dict_pages, timestamp_updated) values ('a5', '{213}', '2023-07-28 18:48:11+00');
insert into dictionary_word (headword, dict_pages, timestamp_updated) values ('a4', '{228}', '2023-07-27 18:37:42+00');
insert into dictionary_word (headword, dict_pages, timestamp_updated) values ('a3', '{228}', '2023-07-27 18:37:41+00');
insert into dictionary_word (headword, dict_pages, timestamp_updated) values ('a2', '{227, 228}', '2023-07-27 14:47:55+00');
insert into dictionary_word (headword, dict_pages, timestamp_updated) values ('a1', '{112, 227, 228}', '2023-07-27 14:47:51+00');
insert into dictionary_word (headword, dict_pages, timestamp_updated) values ('a0', '{227}', '2023-07-26 14:47:51+00');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


-- all data [1]
select * 
from dictionary_word
order by timestamp_updated;


headword
dict_pages
timestamp_updated


a0
{227}
2023-07-26 14:47:51

a1
{112,227,228}
2023-07-27 14:47:51

a2
{227,228}
2023-07-27 14:47:55

a3
{228}
2023-07-27 18:37:41

a4
{228}
2023-07-27 18:37:42

a6
{229}
2023-07-28 14:49:13

a5
{213}
2023-07-28 18:48:11



SELECT 7


-- all data [2]
select *
from dictionary_word, unnest(dict_pages) as page
order by timestamp_updated;


headword
dict_pages
timestamp_updated
page


a0
{227}
2023-07-26 14:47:51
227

a1
{112,227,228}
2023-07-27 14:47:51
112

a1
{112,227,228}
2023-07-27 14:47:51
228

a1
{112,227,228}
2023-07-27 14:47:51
227

a2
{227,228}
2023-07-27 14:47:55
228

a2
{227,228}
2023-07-27 14:47:55
227

a3
{228}
2023-07-27 18:37:41
228

a4
{228}
2023-07-27 18:37:42
228

a6
{229}
2023-07-28 14:49:13
229

a5
{213}
2023-07-28 18:48:11
213



SELECT 10


-- find max page number for each day
select
      to_char(timestamp_updated, 'YYYY-MM-DD') as day_date,
      max(page::integer) as daily_max_page
from dictionary_word dtw, unnest(dict_pages) page
group by day_date
order by day_date;


day_date
daily_max_page


2023-07-26
227

2023-07-27
228

2023-07-28
229



SELECT 3


-- find previous day's max page number for the current day
select
      day_date,
      coalesce(
            lag(daily_max_page) over (order by day_date), 
            daily_max_page
      ) as prev_max_page
from (
      select
            to_char(timestamp_updated, 'YYYY-MM-DD') as day_date,
            max(page::integer) as daily_max_page
      from dictionary_word dtw, unnest(dict_pages) page
      group by day_date
      order by day_date
) s;


day_date
prev_max_page


2023-07-26
227

2023-07-27
227

2023-07-28
228



SELECT 3


with all_pages as (
    select
        to_char(timestamp_updated, 'YYYY-MM-DD') as day_date,
        page
    from dictionary_word dtw, unnest(dict_pages) page
)
select day_date, count(distinct(page)) as pages_count
from all_pages
join (
    select
        day_date,
        coalesce(
              lag(daily_max_page) over (order by day_date), 
              daily_max_page
        ) as prev_max_page
    from (
          select
              to_char(timestamp_updated, 'YYYY-MM-DD') as day_date,
              max(page::integer) as daily_max_page
          from dictionary_word dtw, unnest(dict_pages) page
          group by day_date
          order by day_date
    ) s
) prev_day_max_page_stat using (day_date)
where page::integer >= prev_max_page
group by day_date
order by day_date;


day_date
pages_count


2023-07-26
1

2023-07-27
2

2023-07-28
1



SELECT 3


select
    headword, dict_pages, 
    to_char(timestamp_updated, 'YYYY-MM-DD') as day_date, 
    page,
    first_value(page) over (
        order by to_char(timestamp_updated, 'YYYY-MM-DD')
        groups between 1 preceding and current row
    ) as prev_day_max_page 
from dictionary_word, unnest(dict_pages) page
order by to_char(timestamp_updated, 'YYYY-MM-DD');


headword
dict_pages
day_date
page
prev_day_max_page


a0
{227}
2023-07-26
227
227

a4
{228}
2023-07-27
228
227

a3
{228}
2023-07-27
228
227

a2
{227,228}
2023-07-27
227
227

a2
{227,228}
2023-07-27
228
227

a1
{112,227,228}
2023-07-27
227
227

a1
{112,227,228}
2023-07-27
228
227

a1
{112,227,228}
2023-07-27
112
227

a6
{229}
2023-07-28
229
228

a5
{213}
2023-07-28
213
228



SELECT 10


select day_date, count(distinct(page)) as page_count
from 
(select 
    to_char(timestamp_updated, 'YYYY-MM-DD') as day_date, 
    page,
    first_value(page) over (
        order by to_char(timestamp_updated, 'YYYY-MM-DD')
        groups between 1 preceding and current row
    ) as prev_day_max_page 
from dictionary_word, unnest(dict_pages) page
) prev_day_page_data
where page >= prev_day_max_page
group by day_date
order by day_date;


day_date
page_count


2023-07-26
1

2023-07-27
2

2023-07-28
1



SELECT 3

headword	dict_pages	timestamp_updated
a0	{227}	2023-07-26 14:47:51
a1	{112,227,228}	2023-07-27 14:47:51
a2	{227,228}	2023-07-27 14:47:55
a3	{228}	2023-07-27 18:37:41
a4	{228}	2023-07-27 18:37:42
a6	{229}	2023-07-28 14:49:13
a5	{213}	2023-07-28 18:48:11

headword	dict_pages	day_date	page	prev_day_max_page
a0	{227}	2023-07-26	227	227
a4	{228}	2023-07-27	228	227
a3	{228}	2023-07-27	228	227
a2	{227,228}	2023-07-27	227	227
a2	{227,228}	2023-07-27	228	227
a1	{112,227,228}	2023-07-27	227	227
a1	{112,227,228}	2023-07-27	228	227
a1	{112,227,228}	2023-07-27	112	227
a6	{229}	2023-07-28	229	228
a5	{213}	2023-07-28	213	228