내 테이블은 다음과 같습니다.
Column | Type |
-----------------------+-------------------+
id | integer |
source_id | integer |
timestamp | integer |
observation_timestamp | integer |
value | double precision |
인덱스는 source_id, 타임 스탬프 및 타임 스탬프와 id의 조합 ( CREATE INDEX timeseries_id_timestamp_combo_idx ON timeseries (id, timeseries DESC NULLS LAST)
)에 존재합니다.
그 안에 20M 개의 행이 있습니다 (OK, 120M이지만 source_id = 1 인 20M). 보고 된 또는에서 관찰 된 사건 을 설명하는 timestamp
다양한 항목과 동일한 항목이 많이 있습니다. 예를 들어 오늘 오전 12시에 예측 된 온도는 내일 오후 2시에 예상됩니다.observation_timestamp
value
timestamp
observation_timestamp
이상적으로이 테이블은 몇 가지 일을 잘 수행합니다.
- 한 번에 100K 씩 새로운 항목을 일괄 삽입
- 시간 범위에 대해 관찰 된 데이터 선택 ( “1 월에서 3 월까지의 온도 예측은 무엇입니까”)
- 특정 시점에서 관찰 된 시간 범위에 대해 관찰 된 데이터 선택 ( “11 월 1 일에 생각한 1 월에서 3 월까지의 온도 예측보기”)
두 번째는이 질문의 중심에있는 것입니다.
표의 데이터는 다음과 같습니다.
id source_id timestamp observation_timestamp value
1 1 1531084900 1531083900 9999
2 1 1531084900 1531082900 1111
3 1 1531085900 1531083900 8888
4 1 1531085900 1531082900 7777
5 1 1531086900 1531082900 5555
쿼리의 결과는 다음과 같습니다 (최근 관찰 _ 시간 소인 행만 표시됨)
id source_id timestamp observation_timestamp value
1 1 1531084900 1531083900 9999
3 1 1531085900 1531083900 8888
5 1 1531086900 1531082900 5555
이러한 쿼리를 최적화하기 위해 이미 일부 자료를 참조했습니다.
- /programming/25536422/optimize-group-by-query-to-retrieve-latest-record-per-user/25536748#25536748
- PostgreSQL에서 DISTINCT ON 속도를 높이는 방법은 무엇입니까?
- /programming/3800551/select-first-row-in-each-group-by-group
… 제한적인 성공.
timestamp
측면에서 참조하기 쉽도록 별도의 테이블을 만드는 것을 고려 했지만 측면에서 쉽게 참조 할 수있는 사람들이 상대적으로 높은 카디널리티로 인해 도움이 될지 의심 스럽습니다 batch inserting new entries
.
세 가지 쿼리를보고 있는데 모두 성능이 좋지 않습니다.
- LATERAL 조인이 포함 된 재귀 CTE
- 창 기능
- 고 유지
(나는 그들이 현재 똑같은 일을하지는 않지만 알고있는 한 쿼리 유형의 좋은 예를 제공한다는 것을 알고 있습니다.)
LATERAL 조인이 포함 된 재귀 CTE
WITH RECURSIVE cte AS (
(
SELECT ts
FROM timeseries ts
WHERE source_id = 1
ORDER BY id, "timestamp" DESC NULLS LAST
LIMIT 1
)
UNION ALL
SELECT (
SELECT ts1
FROM timeseries ts1
WHERE id > (c.ts).id
AND source_id = 1
ORDER BY id, "timestamp" DESC NULLS LAST
LIMIT 1
)
FROM cte c
WHERE (c.ts).id IS NOT NULL
)
SELECT (ts).*
FROM cte
WHERE (ts).id IS NOT NULL
ORDER BY (ts).id;
공연:
Sort (cost=164999681.98..164999682.23 rows=100 width=28)
Sort Key: ((cte.ts).id)
CTE cte
-> Recursive Union (cost=1653078.24..164999676.64 rows=101 width=52)
-> Subquery Scan on *SELECT* 1 (cost=1653078.24..1653078.26 rows=1 width=52)
-> Limit (cost=1653078.24..1653078.25 rows=1 width=60)
-> Sort (cost=1653078.24..1702109.00 rows=19612304 width=60)
Sort Key: ts.id, ts.timestamp DESC NULLS LAST
-> Bitmap Heap Scan on timeseries ts (cost=372587.92..1555016.72 rows=19612304 width=60)
Recheck Cond: (source_id = 1)
-> Bitmap Index Scan on ix_timeseries_source_id (cost=0.00..367684.85 rows=19612304 width=0)
Index Cond: (source_id = 1)
-> WorkTable Scan on cte c (cost=0.00..16334659.64 rows=10 width=32)
Filter: ((ts).id IS NOT NULL)
SubPlan 1
-> Limit (cost=1633465.94..1633465.94 rows=1 width=60)
-> Sort (cost=1633465.94..1649809.53 rows=6537435 width=60)
Sort Key: ts1.id, ts1.timestamp DESC NULLS LAST
-> Bitmap Heap Scan on timeseries ts1 (cost=369319.21..1600778.77 rows=6537435 width=60)
Recheck Cond: (source_id = 1)
Filter: (id > (c.ts).id)
-> Bitmap Index Scan on ix_timeseries_source_id (cost=0.00..367684.85 rows=19612304 width=0)
Index Cond: (source_id = 1)
-> CTE Scan on cte (cost=0.00..2.02 rows=100 width=28)
Filter: ((ts).id IS NOT NULL)
(단지 EXPLAIN
, EXPLAIN ANALYZE
완료 할 수 없습니다,했다> 전체 쿼리 24)
창 기능
WITH summary AS (
SELECT ts.id, ts.source_id, ts.value,
ROW_NUMBER() OVER(PARTITION BY ts.timestamp ORDER BY ts.observation_timestamp DESC) AS rn
FROM timeseries ts
WHERE source_id = 1
)
SELECT s.*
FROM summary s
WHERE s.rn = 1;
공연:
CTE Scan on summary s (cost=5530627.97..5971995.66 rows=98082 width=24) (actual time=150368.441..226331.286 rows=88404 loops=1)
Filter: (rn = 1)
Rows Removed by Filter: 20673704
CTE summary
-> WindowAgg (cost=5138301.13..5530627.97 rows=19616342 width=32) (actual time=150368.429..171189.504 rows=20762108 loops=1)
-> Sort (cost=5138301.13..5187341.98 rows=19616342 width=24) (actual time=150368.405..165390.033 rows=20762108 loops=1)
Sort Key: ts.timestamp, ts.observation_timestamp DESC
Sort Method: external merge Disk: 689752kB
-> Bitmap Heap Scan on timeseries ts (cost=372675.22..1555347.49 rows=19616342 width=24) (actual time=2767.542..50399.741 rows=20762108 loops=1)
Recheck Cond: (source_id = 1)
Rows Removed by Index Recheck: 217784
Heap Blocks: exact=48415 lossy=106652
-> Bitmap Index Scan on ix_timeseries_source_id (cost=0.00..367771.13 rows=19616342 width=0) (actual time=2757.245..2757.245 rows=20762630 loops=1)
Index Cond: (source_id = 1)
Planning time: 0.186 ms
Execution time: 234883.090 ms
고 유지
SELECT DISTINCT ON (timestamp) *
FROM timeseries
WHERE source_id = 1
ORDER BY timestamp, observation_timestamp DESC;
공연:
Unique (cost=5339449.63..5437531.34 rows=15991 width=28) (actual time=112653.438..121397.944 rows=88404 loops=1)
-> Sort (cost=5339449.63..5388490.48 rows=19616342 width=28) (actual time=112653.437..120175.512 rows=20762108 loops=1)
Sort Key: timestamp, observation_timestamp DESC
Sort Method: external merge Disk: 770888kB
-> Bitmap Heap Scan on timeseries (cost=372675.22..1555347.49 rows=19616342 width=28) (actual time=2091.585..56109.942 rows=20762108 loops=1)
Recheck Cond: (source_id = 1)
Rows Removed by Index Recheck: 217784
Heap Blocks: exact=48415 lossy=106652
-> Bitmap Index Scan on ix_timeseries_source_id (cost=0.00..367771.13 rows=19616342 width=0) (actual time=2080.054..2080.054 rows=20762630 loops=1)
Index Cond: (source_id = 1)
Planning time: 0.132 ms
Execution time: 161651.006 ms
데이터를 어떻게 구성해야합니까, 스캔하지 않아야하는 스캔이 있습니까? 일반적으로 이러한 쿼리를 ~ 120이 아닌 ~ 1로 가져올 수 있습니까?
원하는 결과를 얻기 위해 데이터를 쿼리하는 다른 방법이 있습니까?
그렇지 않다면 어떤 인프라 / 아키텍처를보아야합니까?
답변
재귀 CTE 쿼리 ORDER BY (ts).id
를 사용하면 CTE가 자동으로 순서대로 쿼리를 작성하므로 최종 결과 는 불필요합니다. 쿼리를 훨씬 빠르게 만들려면 제거하면 500을 제외한 모든 행을 버리기 위해 20,180,572 개의 행을 생성하지 않고 조기에 중지 할 수 있습니다. 또한 인덱스를 작성하면 인덱스가 (source_id, id, timestamp desc nulls last)
더욱 향상됩니다.
다른 두 쿼리의 경우 비트 맵이 메모리에 적합 할 정도로 work_mem을 늘리면 손실 된 힙 블록을 제거 할 수 있습니다. 그러나 (source_id, "timestamp", observation_timestamp DESC)
인덱스 전용 스캔 과 같은 사용자 지정 인덱스 만큼은 아닙니다 (source_id, "timestamp", observation_timestamp DESC, value, id)
.