Postgres 12

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 test (
  ts TIMESTAMPTZ not null,
  w BOOLEAN not null
);


-- select '1999-12-11'::timestamp + ((10*random())::int) * INTERVAL '1 second'; 

INSERT INTO test(ts, w)
select distinct '2020-07-27 15:40:04.045+00'::timestamp + ((100000*random())::int) * interval '1 second'
     , case when random() > 0.9 then false else true end
from generate_series(1,50);

--VALUES
--('2020-07-27 15:40:04.045+00', true),
--('2020-07-27 15:41:04.045+00', true),
--('2020-07-27 15:42:04.045+00', false),
--('2020-07-27 15:43:04.045+00', true),
--('2020-07-27 15:44:04.045+00', true),
--('2020-07-27 15:45:04.045+00', true);

create type tsdc as (
    start          TIMESTAMPTZ,
    en		   TIMESTAMPTZ,
    dur		   INT,
    lastDur        INT,
    last	   BOOLEAN
);

create or replace function testDur(agg_state tsdc, ts timestamptz, el boolean)
returns tsdc
immutable
language plpgsql
as $$
declare
  dur int;
begin
  if agg_state IS NULL then
     agg_state = (NULL, NULL, 0, 0, false)::tsdc;
  end if;

  if agg_state.start IS NULL and el = true then
	agg_state.start = ts;
  end if;

  if el = true then
	agg_state.en = ts;
  end if;  

  if agg_state.start IS NOT NULL and agg_state.en IS NOT NULL then
    EXECUTE 'SELECT EXTRACT(EPOCH FROM ($1  - $2))'
    INTO dur
    USING agg_state.en, agg_state.start;
    agg_state.lastDur = dur;
  end if;

  if agg_state.last = true and el = false then
	agg_state.start = NULL;
	agg_state.en = NULL;
	agg_state.dur = agg_state.dur + agg_state.lastDur;
  end if;
   
  agg_state.last = el;

  return agg_state;
end;
$$;


create or replace function testDurFin(agg_state tsdc)
returns int
immutable
strict
language plpgsql
as $$
begin
  return agg_state.dur + agg_state.lastDur;
end;
$$;

create aggregate ddur (timestamptz, boolean)
(
    sfunc = testDur,
    stype = tsdc,
    finalfunc = testDurFin
);
50 rows affected

SELECT * FROM test order by ts;


ts
w


2020-07-27 15:40:58.045+01
t

2020-07-27 15:47:49.045+01
f

2020-07-27 16:58:42.045+01
t

2020-07-27 17:21:03.045+01
t

2020-07-27 17:22:50.045+01
t

2020-07-27 18:17:38.045+01
t

2020-07-27 18:45:07.045+01
t

2020-07-27 19:01:07.045+01
t

2020-07-27 20:14:46.045+01
t

2020-07-27 20:49:53.045+01
t

2020-07-27 21:20:13.045+01
t

2020-07-27 21:32:41.045+01
t

2020-07-27 21:33:35.045+01
t

2020-07-27 21:37:29.045+01
t

2020-07-27 21:45:51.045+01
t

2020-07-27 21:50:41.045+01
t

2020-07-27 21:56:25.045+01
t

2020-07-27 22:01:32.045+01
t

2020-07-27 22:33:03.045+01
t

2020-07-27 22:40:40.045+01
t

2020-07-27 23:35:24.045+01
t

2020-07-28 00:19:11.045+01
t

2020-07-28 01:07:57.045+01
t

2020-07-28 01:09:51.045+01
t

2020-07-28 01:48:04.045+01
t

2020-07-28 02:03:45.045+01
t

2020-07-28 03:55:23.045+01
t

2020-07-28 04:08:31.045+01
t

2020-07-28 05:24:21.045+01
t

2020-07-28 05:48:11.045+01
t

2020-07-28 06:19:37.045+01
t

2020-07-28 06:27:24.045+01
t

2020-07-28 07:34:33.045+01
t

2020-07-28 08:07:25.045+01
t

2020-07-28 08:25:12.045+01
t

2020-07-28 08:47:06.045+01
t

2020-07-28 09:07:27.045+01
t

2020-07-28 09:26:42.045+01
f

2020-07-28 10:08:32.045+01
t

2020-07-28 10:17:48.045+01
t

2020-07-28 12:30:21.045+01
t

2020-07-28 12:55:52.045+01
t

2020-07-28 13:57:55.045+01
t

2020-07-28 14:30:11.045+01
t

2020-07-28 14:49:27.045+01
t

2020-07-28 16:04:10.045+01
t

2020-07-28 16:35:22.045+01
t

2020-07-28 16:41:47.045+01
t

2020-07-28 17:23:58.045+01
t

2020-07-28 18:40:56.045+01
t


--explain analyze SELECT ddur(ts, w) FROM test;
SELECT ddur(ts, w) FROM test;


ddur


-24881


select EXTRACT(EPOCH from sum(dur))
from (
select max(ts) - min(ts) as dur
from (
select ts, w, row_number() over (order by ts)
    -  row_number() over (partition by w order by ts) as grp
from test
) as t
where w
group by grp) as tt; 


date_part


88869


--create unique index x1 on test (ts, w);
--explain analyse select EXTRACT(EPOCH from sum(dur))
--from (
--select max(ts) - min(ts) as dur
--from (
--select ts, w, row_number() over (order by ts)
--    -  row_number() over (partition by w order by ts) as grp
--from test
--) as t
--where w
--group by grp) as tt; 

ts	w
2020-07-27 15:40:58.045+01	t
2020-07-27 15:47:49.045+01	f
2020-07-27 16:58:42.045+01	t
2020-07-27 17:21:03.045+01	t
2020-07-27 17:22:50.045+01	t
2020-07-27 18:17:38.045+01	t
2020-07-27 18:45:07.045+01	t
2020-07-27 19:01:07.045+01	t
2020-07-27 20:14:46.045+01	t
2020-07-27 20:49:53.045+01	t
2020-07-27 21:20:13.045+01	t
2020-07-27 21:32:41.045+01	t
2020-07-27 21:33:35.045+01	t
2020-07-27 21:37:29.045+01	t
2020-07-27 21:45:51.045+01	t
2020-07-27 21:50:41.045+01	t
2020-07-27 21:56:25.045+01	t
2020-07-27 22:01:32.045+01	t
2020-07-27 22:33:03.045+01	t
2020-07-27 22:40:40.045+01	t
2020-07-27 23:35:24.045+01	t
2020-07-28 00:19:11.045+01	t
2020-07-28 01:07:57.045+01	t
2020-07-28 01:09:51.045+01	t
2020-07-28 01:48:04.045+01	t
2020-07-28 02:03:45.045+01	t
2020-07-28 03:55:23.045+01	t
2020-07-28 04:08:31.045+01	t
2020-07-28 05:24:21.045+01	t
2020-07-28 05:48:11.045+01	t
2020-07-28 06:19:37.045+01	t
2020-07-28 06:27:24.045+01	t
2020-07-28 07:34:33.045+01	t
2020-07-28 08:07:25.045+01	t
2020-07-28 08:25:12.045+01	t
2020-07-28 08:47:06.045+01	t
2020-07-28 09:07:27.045+01	t
2020-07-28 09:26:42.045+01	f
2020-07-28 10:08:32.045+01	t
2020-07-28 10:17:48.045+01	t
2020-07-28 12:30:21.045+01	t
2020-07-28 12:55:52.045+01	t
2020-07-28 13:57:55.045+01	t
2020-07-28 14:30:11.045+01	t
2020-07-28 14:49:27.045+01	t
2020-07-28 16:04:10.045+01	t
2020-07-28 16:35:22.045+01	t
2020-07-28 16:41:47.045+01	t
2020-07-28 17:23:58.045+01	t
2020-07-28 18:40:56.045+01	t