подытожу, о чём примерно шла речь:
подготовка кейса
DDL:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE tlog
(
id serial NOT NULL,
num character(9) NOT NULL,
dt timestamp without time zone NOT NULL,
PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
CREATE INDEX
ON tlog
USING btree
(num, dt);
--DATA:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
BEGIN;
TRUNCATE tlog;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
INSERT INTO tlog(num, dt)
SELECT trim(n::text ) , date'2013-01-01' + (random()*d*10 )::integer
FROM
generate_series(1, 10000) n
,generate_series(1, 200) d;
END;
BEGIN;
ANALYZE tlog;
END;
планы :
GROUP 1.
2.
3.
4.
select num
from tlog
group by num,extract(week from dt),extract(year from dt)
having count(extract(week from dt))>1
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
GroupAggregate (cost=3673065.00..4008066.22 rows=2000008 width=18) (actual time=312409.734..403131.516 rows=2197589 loops=1)
Output: num, (date_part('week'::text, dt)), (date_part('year'::text, dt))
Filter: (count((date_part('week'::text, tlog.dt))) > 1)
Rows Removed by Filter: 319805
Buffers: shared hit=13750 read=113639, temp read=163178 written=163178
-> Sort (cost=3673065.00..3723065.18 rows=20000072 width=18) (actual time=312217.935..393348.149 rows=20000000 loops=1)
Output: num, (date_part('week'::text, dt)), (date_part('year'::text, dt)), dt
Sort Key: tlog.num, (date_part('week'::text, tlog.dt)), (date_part('year'::text, tlog.dt))
Sort Method: external merge Disk: 977472kB
Buffers: shared hit=13750 read=113639, temp read=163178 written=163178
-> Seq Scan on public.tlog (cost=0.00..427390.08 rows=20000072 width=18) (actual time=0.031..112665.319 rows=20000000 loops=1)
Output: num, date_part('week'::text, dt), date_part('year'::text, dt), dt
Buffers: shared hit=13750 read=113639
Total runtime: 404599.624 ms
//на distinct забиваем
LOOSE--INDEXSCAN 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
WITH RECURSIVE rt AS
(
(select t1.num
from tlog t1
where
(
select date_part('week',t2.dt) from tlog t2
where
t2.num=t1.num
and t2.id<>t1.id
and t2.dt > t1.dt
and t2.dt <= t1.dt::date +8
ORDER BY t2.num, t2.dt LIMIT 1
)
= date_part('week',t1.dt)
ORDER BY t1.num, t1.dt LIMIT 1)
UNION ALL
(select t1.num
from rt
,LATERAL (SELECT num FROM tlog t1
where
t1.num>rt.num
AND (
select date_part('week',t2.dt) from tlog t2
where
t1.num>rt.num
AND t2.num=t1.num
and t2.id<>t1.id
and t2.dt > t1.dt
and t2.dt <= t1.dt::date +8
ORDER BY t2.num, t2.dt LIMIT 1
)
= date_part('week',t1.dt)
ORDER BY t1.num, t1.dt LIMIT 1) t1
)
)
SELECT * FROM rt --LIMIT 10000
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
CTE Scan on rt (cost=176393.33..176395.35 rows=101 width=40) (actual time=0.116..100131.760 rows=10000 loops=1)
Output: rt.num
Buffers: shared hit=74285 read=25749
CTE rt
-> Recursive Union (cost=0.56..176393.33 rows=101 width=18) (actual time=0.114..100122.313 rows=10000 loops=1)
Buffers: shared hit=74285 read=25749
-> Subquery Scan on "*SELECT* 1" (cost=0.56..1745.39 rows=1 width=18) (actual time=0.113..0.114 rows=1 loops=1)
Output: "*SELECT* 1".num
Buffers: shared hit=8 read=2
-> Limit (cost=0.56..1745.38 rows=1 width=18) (actual time=0.112..0.112 rows=1 loops=1)
Output: t1.num, t1.dt
Buffers: shared hit=8 read=2
-> Index Scan using tlog_num_dt_idx on public.tlog t1 (cost=0.56..174481850.39 rows=100000 width=18) (actual time=0.111..0.111 rows=1 loops=1)
Output: t1.num, t1.dt
Filter: ((SubPlan 1) = date_part('week'::text, t1.dt))
Buffers: shared hit=8 read=2
SubPlan 1
-> Limit (cost=0.57..4.94 rows=1 width=18) (actual time=0.041..0.042 rows=1 loops=1)
Output: (date_part('week'::text, t2.dt)), t2.num, t2.dt
Buffers: shared hit=5
-> Index Scan using tlog_num_dt_idx on public.tlog t2 (cost=0.57..48.70 rows=11 width=18) (actual time=0.033..0.033 rows=1 loops=1)
Output: date_part('week'::text, t2.dt), t2.num, t2.dt
Index Cond: ((t2.num = t1.num) AND (t2.dt > t1.dt) AND (t2.dt <= ((t1.dt)::date + 8)))
Filter: (t2.id <> t1.id)
Buffers: shared hit=5
-> Nested Loop (cost=0.56..17464.59 rows=10 width=10) (actual time=10.009..10.010 rows=1 loops=10000)
Output: t1_1.num
Buffers: shared hit=74277 read=25747
-> WorkTable Scan on rt rt_1 (cost=0.00..0.20 rows=10 width=40) (actual time=0.000..0.001 rows=1 loops=10000)
Output: rt_1.num
-> Limit (cost=0.56..1746.42 rows=1 width=18) (actual time=10.008..10.008 rows=1 loops=10000)
Output: t1_1.num, t1_1.dt
Buffers: shared hit=74277 read=25747
-> Index Scan using tlog_num_dt_idx on public.tlog t1_1 (cost=0.56..58194644.09 rows=33333 width=18) (actual time=10.006..10.006 rows=1 loops=10000)
Output: t1_1.num, t1_1.dt
Index Cond: (t1_1.num > rt_1.num)
Filter: ((SubPlan 2) = date_part('week'::text, t1_1.dt))
Buffers: shared hit=74277 read=25747
SubPlan 2
-> Limit (cost=0.57..4.95 rows=1 width=18) (actual time=1.421..1.421 rows=1 loops=9999)
Output: (date_part('week'::text, t2_1.dt)), t2_1.num, t2_1.dt
Buffers: shared hit=42802 read=7201
-> Result (cost=0.57..48.71 rows=11 width=18) (actual time=1.420..1.420 rows=1 loops=9999)
Output: date_part('week'::text, t2_1.dt), t2_1.num, t2_1.dt
One-Time Filter: (t1_1.num > $5)
Buffers: shared hit=42802 read=7201
-> Index Scan using tlog_num_dt_idx on public.tlog t2_1 (cost=0.57..48.68 rows=11 width=18) (actual time=1.414..1.414 rows=1 loops=9999)
Output: t2_1.id, t2_1.num, t2_1.dt
Index Cond: ((t2_1.num = t1_1.num) AND (t2_1.dt > t1_1.dt) AND (t2_1.dt <= ((t1_1.dt)::date + 8)))
Filter: (t2_1.id <> t1_1.id)
Buffers: shared hit=42802 read=7201
Total runtime: 100135.295 ms
резюме : при count(distinct name) < 1/2000 от count(1) имеет смысл LOOSE indexscan. (время у него, навскидку, в этой задаче зависит от распределения, а минимум -- только от этого отношения -- для каждого нейм -- 2 сика. и либо пан, либо нет.
если же распределение хуже -- число сиков будет расти с переходом в пределе к пресловутым "20 лямам"*2)
ну а реальная ширина таблицы [если в ней много больше 2--х полей] добавит преимуществ. |
