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 tes (event_time timestamptz, page_type text);

CREATE TABLE


insert into tes  values ('2022-06-20 14:06:10.352+03',	''),
('2022-06-20 14:06:48.311+03',	'product'),
('2022-06-20 14:07:47.261+03',	'product'),
('2022-06-20 14:07:47.89+03',	''),
('2022-06-20 14:07:57.874+03',	'product'),
('2022-06-20 14:08:27.698+03',	''),
('2022-06-20 14:08:37.175+03',	'product'),
('2022-06-20 14:08:53.634+03',	'product'),
('2022-06-20 14:08:54.257+03',	''),
('2022-06-20 14:09:05.697+03',	'product'),
('2022-06-20 14:09:17.387+03',	'product'),
('2022-06-20 14:09:17.888+03',	''),
('2022-06-20 14:09:56.096+03',	'product'),
('2022-06-20 14:10:19.37+03',	''),
('2022-06-20 14:10:29.124+03',	''),
('2022-06-20 14:10:37.072+03',	'collection'),
('2022-06-20 14:10:42.288+03',	'product'),
('2022-06-20 14:11:14.274+03',	''),
('2022-06-20 14:11:22.808+03',	''),
('2022-06-20 14:11:41.997+03',	'product'),
('2022-06-20 14:12:30.118+03',	'product'),
('2022-06-20 14:13:16.277+03',	''),
('2022-06-20 14:13:55.078+03',	'collection'),
('2022-06-20 14:16:04.04+03',	'order'),
('2022-06-20 14:16:12.968+03',	'checkout'),
('2022-06-20 21:14:40.085+03',	'');

INSERT 0 26


SELECT
  ARRAY_AGG(tes.page_type ORDER BY tes.event_time)
      FILTER (WHERE tes.page_type <> tes.last_page_type) AS consecutives_combined,
  ARRAY_AGG(tes.page_type ORDER BY tes.event_time) AS all_values
FROM (
    SELECT
      *,
      LAG(page_type, 1, '') OVER (ORDER BY tes.event_time) AS last_page_type
    FROM tes
    WHERE tes.page_type <> ''
) tes;


consecutives_combined
all_values


{product,collection,product,collection,order,checkout}
{product,product,product,product,product,product,product,product,collection,product,product,product,collection,order,checkout}



SELECT 1