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 test (
customer INT,
purshase_ts TIMESTAMP,
amount DECIMAL(10,2)
)
INSERT INTO test (customer,purshase_ts,amount)
VALUES
(1,'2018-12-18 16:00:00.000000 +11:00',2),
(1,'2018-12-18 16:01:00.000000 +11:00',2), 
(1,'2018-12-18 16:02:00.000000 +11:00',1), 
(1,'2018-12-18 16:03:00.000000 +11:00',3), 
(1,'2018-12-18 16:04:00.000000 +11:00',5), 
(1,'2018-12-18 16:05:00.000000 +11:00',5), 
(2,'2018-12-18 16:00:00.000000 +11:00',2), 
(2,'2018-12-18 16:05:00.000000 +11:00',2), 
(2,'2018-12-18 16:10:00.000000 +11:00',4)
9 rows affected

SELECT * FROM test ORDER BY customer,purshase_ts


customer
purshase_ts
amount


1
2018-12-18 16:00:00
2.00

1
2018-12-18 16:01:00
2.00

1
2018-12-18 16:02:00
1.00

1
2018-12-18 16:03:00
3.00

1
2018-12-18 16:04:00
5.00

1
2018-12-18 16:05:00
5.00

2
2018-12-18 16:00:00
2.00

2
2018-12-18 16:05:00
2.00

2
2018-12-18 16:10:00
4.00


SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)


customer


2

1


SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND COUNT(t2.amount) = 5
INSERT INTO test (customer,purshase_ts,amount)
VALUES
(1,'2018-12-18 16:06:00.000000 +11:00',3), 
(1,'2018-12-18 16:07:00.000000 +11:00',5), 
(1,'2018-12-18 16:08:00.000000 +11:00',5) 
3 rows affected

SELECT * FROM test ORDER BY customer,purshase_ts


customer
purshase_ts
amount


1
2018-12-18 16:00:00
2.00

1
2018-12-18 16:01:00
2.00

1
2018-12-18 16:02:00
1.00

1
2018-12-18 16:03:00
3.00

1
2018-12-18 16:04:00
5.00

1
2018-12-18 16:05:00
5.00

1
2018-12-18 16:06:00
3.00

1
2018-12-18 16:07:00
5.00

1
2018-12-18 16:08:00
5.00

2
2018-12-18 16:00:00
2.00

2
2018-12-18 16:05:00
2.00

2
2018-12-18 16:10:00
4.00


SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND COUNT(t2.amount) = 5


customer


1


SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND MAX(t2.purshase_ts) - MIN(t2.purshase_ts) = INTERVAL '4 MINUTE'


customer


1

customer	purshase_ts	amount
1	2018-12-18 16:00:00	2.00
1	2018-12-18 16:01:00	2.00
1	2018-12-18 16:02:00	1.00
1	2018-12-18 16:03:00	3.00
1	2018-12-18 16:04:00	5.00
1	2018-12-18 16:05:00	5.00
2	2018-12-18 16:00:00	2.00
2	2018-12-18 16:05:00	2.00
2	2018-12-18 16:10:00	4.00