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 T (seller_id int, payment_id varchar(3), payment_time timestamp, second_diff int);
INSERT INTO T (seller_id, payment_id, payment_time, second_diff)
VALUES
(1, 'pl', '2015-01-08 09:23:04', 151),
(1, 'p2', '2015-01-08 09:25:35', 50),
(1, 'p3', '2015-01-08 09:26:25', 48),
(1, 'p4', '2015-01-08 09:27:23', 36),
(1, 'p5', '2015-01-08 09:27:59', 41),
(1, 'p6', '2015-01-08 09:28:40', 70),
(1, 'p7', '2015-01-08 09:29:50', 50),
(1, 'p8', '2015-01-08 09:30:40', 45),
(1, 'p9', '2015-01-08 09:31:25', 35),
(1, 'p10', '2015-01-08 09:32:00', null),
(2, 'pll', '2015-01-08 09:25:35', 25),
(2, 'p12', '2015-01-08 09:26:00', 55),
(2, 'p13', '2015-01-08 09:26:55', 30),
(2, 'p14', '2015-01-08 09:27:25', 95),
(2, 'p15', '2015-01-08 09:29:00', null),
(3, 'p16', '2015-01-08 10:41:00', 65),
(3, 'p17', '2015-01-08 10:42:05', 75),
(3, 'p18', '2015-01-08 10:43:20', 90),
(3, 'p19', '2015-01-08 10:43:20', 39),
(3, 'p20', '2015-01-08 10:43:59', 50),
(3, 'p21', '2015-01-08 10:44:49', null);
21 rows affected
explain (analyze,buffers)
with A as (
select seller_id, payment_time, second_diff,
case when
lag(case when second_diff < 60 then 1 else 0 end)
over (partition by seller_id order by payment_time)
= case when second_diff < 60 then 1 else 0 end
then 0 else 1 end as transition
from T
), B as (
select *,
sum(transition)
over (partition by seller_id order by payment_time) as grp
from A
), C as (
select seller_id, count(*) as p
from B
where second_diff < 60
group by seller_id, grp
having count(*) >= 3
)
select count(distinct seller_id) as sellers, sum(p) as payments
from C;
| QUERY PLAN |
|---|
| Aggregate (cost=298.65..298.66 rows=1 width=40) (actual time=3.524..3.525 rows=1 loops=1) |
| Buffers: shared hit=7 |
| CTE a |
| -> WindowAgg (cost=94.38..131.78 rows=1360 width=20) (actual time=0.087..0.137 rows=21 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=94.38..97.78 rows=1360 width=16) (actual time=0.054..0.071 rows=21 loops=1) |
| Sort Key: t.seller_id, t.payment_time |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> Seq Scan on t (cost=0.00..23.60 rows=1360 width=16) (actual time=0.015..0.021 rows=21 loops=1) |
| Buffers: shared hit=1 |
| CTE b |
| -> WindowAgg (cost=97.98..125.18 rows=1360 width=28) (actual time=0.219..0.237 rows=21 loops=1) |
| Buffers: shared hit=7 |
| -> Sort (cost=97.98..101.38 rows=1360 width=20) (actual time=0.204..0.205 rows=21 loops=1) |
| Sort Key: a.seller_id, a.payment_time |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=7 |
| -> CTE Scan on a (cost=0.00..27.20 rows=1360 width=20) (actual time=0.088..0.145 rows=21 loops=1) |
| Buffers: shared hit=1 |
| CTE c |
| -> HashAggregate (cost=35.13..37.00 rows=187 width=20) (actual time=0.271..0.274 rows=3 loops=1) |
| Group Key: b.seller_id, b.grp |
| Filter: (count(*) >= 3) |
| Rows Removed by Filter: 1 |
| Buffers: shared hit=7 |
| -> CTE Scan on b (cost=0.00..30.60 rows=453 width=12) (actual time=0.232..0.252 rows=12 loops=1) |
| Filter: (second_diff < 60) |
| Rows Removed by Filter: 9 |
| Buffers: shared hit=7 |
| -> CTE Scan on c (cost=0.00..3.74 rows=187 width=12) (actual time=0.274..0.281 rows=3 loops=1) |
| Buffers: shared hit=7 |
| Planning time: 2.486 ms |
| Execution time: 7.750 ms |
explain (analyze,buffers)
with A as (
select seller_id, payment_time, second_diff,
case when
lag(case when second_diff < 60 then 1 else 0 end)
over (partition by seller_id order by payment_time)
= case when second_diff < 60 then 1 else 0 end
then 0 else 1 end as transition
from T
), B as (
select *,
sum(transition)
over (partition by seller_id order by payment_time) as grp
from A
)
select
dense_rank() over (order by seller_id)
+ dense_rank() over (order by seller_id desc) - 1 as sellers,
sum(count(*)) over () as payments
from B
where second_diff < 60
group by seller_id, grp
having count(*) >= 3
limit 1;
| QUERY PLAN |
|---|
| Limit (cost=311.35..311.39 rows=1 width=52) (actual time=0.272..0.272 rows=1 loops=1) |
| Buffers: shared hit=1 |
| CTE a |
| -> WindowAgg (cost=94.38..131.78 rows=1360 width=20) (actual time=0.065..0.086 rows=21 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=94.38..97.78 rows=1360 width=16) (actual time=0.049..0.050 rows=21 loops=1) |
| Sort Key: t.seller_id, t.payment_time |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> Seq Scan on t (cost=0.00..23.60 rows=1360 width=16) (actual time=0.011..0.014 rows=21 loops=1) |
| Buffers: shared hit=1 |
| CTE b |
| -> WindowAgg (cost=97.98..125.18 rows=1360 width=28) (actual time=0.141..0.157 rows=21 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=97.98..101.38 rows=1360 width=20) (actual time=0.131..0.132 rows=21 loops=1) |
| Sort Key: a.seller_id, a.payment_time |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> CTE Scan on a (cost=0.00..27.20 rows=1360 width=20) (actual time=0.066..0.091 rows=21 loops=1) |
| Buffers: shared hit=1 |
| -> WindowAgg (cost=54.39..60.93 rows=187 width=52) (actual time=0.265..0.265 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> WindowAgg (cost=54.39..57.66 rows=187 width=36) (actual time=0.244..0.246 rows=3 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=54.39..54.85 rows=187 width=28) (actual time=0.240..0.241 rows=3 loops=1) |
| Sort Key: b.seller_id DESC |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=1 |
| -> WindowAgg (cost=44.06..47.33 rows=187 width=28) (actual time=0.217..0.221 rows=3 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=44.06..44.52 rows=187 width=20) (actual time=0.208..0.208 rows=3 loops=1) |
| Sort Key: b.seller_id |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=1 |
| -> HashAggregate (cost=35.13..37.00 rows=187 width=20) (actual time=0.196..0.198 rows=3 loops=1) |
| Group Key: b.seller_id, b.grp |
| Filter: (count(*) >= 3) |
| Rows Removed by Filter: 1 |
| Buffers: shared hit=1 |
| -> CTE Scan on b (cost=0.00..30.60 rows=453 width=12) (actual time=0.153..0.177 rows=12 loops=1) |
| Filter: (second_diff < 60) |
| Rows Removed by Filter: 9 |
| Buffers: shared hit=1 |
| Planning time: 0.545 ms |
| Execution time: 0.401 ms |
explain (analyze,buffers)
with A as (
select seller_id, payment_time, second_diff,
row_number() over (partition by seller_id order by payment_time) as rn
from T
), B as (
select *,
rn - row_number() over (partition by seller_id order by payment_time) as grp
from A
where second_diff < 60
), C as (
select seller_id, count(*) as p
from B
group by seller_id, grp
having count(*) >= 3
)
select count(distinct seller_id) as sellers, sum(p) as payments
from C;
| QUERY PLAN |
|---|
| Aggregate (cost=202.95..202.96 rows=1 width=40) (actual time=0.142..0.142 rows=1 loops=1) |
| Buffers: shared hit=1 |
| CTE a |
| -> WindowAgg (cost=94.38..121.58 rows=1360 width=24) (actual time=0.038..0.054 rows=21 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=94.38..97.78 rows=1360 width=16) (actual time=0.031..0.033 rows=21 loops=1) |
| Sort Key: t.seller_id, t.payment_time |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> Seq Scan on t (cost=0.00..23.60 rows=1360 width=16) (actual time=0.008..0.010 rows=21 loops=1) |
| Buffers: shared hit=1 |
| CTE b |
| -> WindowAgg (cost=50.58..60.78 rows=453 width=32) (actual time=0.089..0.095 rows=12 loops=1) |
| Buffers: shared hit=1 |
| -> Sort (cost=50.58..51.72 rows=453 width=24) (actual time=0.082..0.082 rows=12 loops=1) |
| Sort Key: a.seller_id, a.payment_time |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=1 |
| -> CTE Scan on a (cost=0.00..30.60 rows=453 width=24) (actual time=0.048..0.066 rows=12 loops=1) |
| Filter: (second_diff < 60) |
| Rows Removed by Filter: 9 |
| Buffers: shared hit=1 |
| CTE c |
| -> HashAggregate (cost=13.59..15.59 rows=200 width=20) (actual time=0.114..0.115 rows=3 loops=1) |
| Group Key: b.seller_id, b.grp |
| Filter: (count(*) >= 3) |
| Rows Removed by Filter: 1 |
| Buffers: shared hit=1 |
| -> CTE Scan on b (cost=0.00..9.06 rows=453 width=12) (actual time=0.089..0.096 rows=12 loops=1) |
| Buffers: shared hit=1 |
| -> CTE Scan on c (cost=0.00..4.00 rows=200 width=12) (actual time=0.115..0.119 rows=3 loops=1) |
| Buffers: shared hit=1 |
| Planning time: 0.101 ms |
| Execution time: 0.231 ms |