Postgres 10

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 input (time varchar(100), useid varchar(100), value int);

CREATE TABLE


insert into input values ('t1','116C123',2);

INSERT 0 1


insert into input values ('t2','116C123',3);

INSERT 0 1


insert into input values ('t3','116C123',4);

INSERT 0 1


insert into input values ('t4','116C123',5);

INSERT 0 1


insert into input values ('t2','116C111',6); 

INSERT 0 1


insert into input values ('t3','116C111',7); 

INSERT 0 1


insert into input values ('t4','116C111',8); 

INSERT 0 1


select * from input;


time
useid
value


t1
116C123
2

t2
116C123
3

t3
116C123
4

t4
116C123
5

t2
116C111
6

t3
116C111
7

t4
116C111
8



SELECT 7


CREATE AGGREGATE MULTIPLICATE (int8) (SFUNC = int8mul, STYPE = int8);

CREATE AGGREGATE


SELECT
  time,
  useid,
  value,
  MULTIPLICATE(value) OVER (PARTITION BY useid ORDER BY useid, value) AS plt
FROM input 
GROUP BY useid, value, time
ORDER BY useid, value, time;


time
useid
value
plt


t2
116C111
6
6

t3
116C111
7
42

t4
116C111
8
336

t1
116C123
2
2

t2
116C123
3
6

t3
116C123
4
24

t4
116C123
5
120



SELECT 7

time	useid	value
t1	116C123	2
t2	116C123	3
t3	116C123	4
t4	116C123	5
t2	116C111	6
t3	116C111	7
t4	116C111	8