Postgres Query Optimization ฟังก์ชันต้นทุนสูง
Aug 27 2020
ตอนนี้ฉันกำลังทำงานกับตาราง postgres ที่มีลักษณะเช่นนี้ (postgres12)
create table if not exists asset (
id text,
symbol text not null,
name text not null
primary key (id)
);
create table if not exists latest_value (
timestamp bigint,
asset text,
price decimal null,
market_cap decimal null,
primary key (asset),
foreign key (asset)
references asset (id)
on delete cascade
);
create table if not exists value_aggregation (
context aggregation_context,
timestamp bigint,
asset text,
price jsonb null,
market_cap jsonb null,
primary key (context, timestamp, asset),
foreign key (asset)
references asset (id)
on delete cascade
) partition by list (context);
create table if not exists value_aggregation_hour
partition of value_aggregation
for values in ('hour');
create index if not exists value_aggregation_timestamp_index
on value_aggregation using brin(timestamp)
with (autosummarize=true);
ตารางvalue_aggregation_hourมีประมาณ 2 ล้านแถว priceคอลัมน์ประกอบด้วย jsonb ที่มีคุณลักษณะเช่นเปิดปิดเฉลี่ย
ตอนนี้ปัญหา:
ข้อความค้นหาต่อไปนี้ใช้เวลานานเกินไป
WITH base_table AS
(SELECT asset, timestamp, market_cap, price
FROM latest_value
ORDER BY market_cap DESC
LIMIT 50
OFFSET 0)
SELECT asset.name, asset.symbol, asset.id, asset.market_data, asset.meta_data, timestamp, market_cap, price, spark.sparkline
FROM base_table LEFT JOIN (
SELECT asset, array_agg(CAST(price->>'open' AS decimal) ORDER BY timestamp ASC) AS sparkline
FROM value_aggregation
WHERE context = 'hour'
AND timestamp > extract(epoch from (now() - INTERVAL '7d'))
AND asset IN (
SELECT asset
FROM base_table)
GROUP BY asset
) spark ON base_table.asset = spark.asset
INNER JOIN asset ON base_table.asset = asset.id;
แบบสอบถามผลลัพธ์มีลักษณะดังนี้:
Merge Left Join (cost=234610.64..234774.05 rows=494 width=1740) (actual time=9173.660..9176.986 rows=50 loops=1)
Merge Cond: (base_table.asset = value_aggregation_hour.asset)
CTE base_table
-> Limit (cost=140.48..140.61 rows=50 width=71) (actual time=2.040..2.051 rows=50 loops=1)
-> Sort (cost=140.48..145.48 rows=2001 width=71) (actual time=2.039..2.043 rows=50 loops=1)
Sort Key: latest_value.market_cap DESC
Sort Method: top-N heapsort Memory: 36kB
-> Seq Scan on latest_value (cost=0.00..74.01 rows=2001 width=71) (actual time=0.011..0.536 rows=2001 loops=1)
-> Sort (cost=377.41..377.54 rows=50 width=1740) (actual time=2.582..2.660 rows=50 loops=1)
Sort Key: base_table.asset
Sort Method: quicksort Memory: 127kB
-> Nested Loop (cost=0.28..376.00 rows=50 width=1740) (actual time=2.071..2.434 rows=50 loops=1)
-> CTE Scan on base_table (cost=0.00..1.00 rows=50 width=232) (actual time=2.042..2.068 rows=50 loops=1)
-> Index Scan using asset_pkey on asset (cost=0.28..7.50 rows=1 width=1508) (actual time=0.006..0.006 rows=1 loops=50)
Index Cond: (id = base_table.asset)
-> GroupAggregate (cost=234092.62..234226.12 rows=1977 width=54) (actual time=9171.070..9174.268 rows=15 loops=1)
Group Key: value_aggregation_hour.asset
-> Sort (cost=234092.62..234110.75 rows=7253 width=203) (actual time=9167.909..9168.235 rows=2501 loops=1)
Sort Key: value_aggregation_hour.asset
Sort Method: quicksort Memory: 761kB
-> Hash Semi Join (cost=1.62..233627.54 rows=7253 width=203) (actual time=8985.832..9163.859 rows=2501 loops=1)
Hash Cond: (value_aggregation_hour.asset = base_table_1.asset)
-> Seq Scan on value_aggregation_hour (cost=0.00..232792.39 rows=286795 width=203) (actual time=8983.255..9112.164 rows=304163 loops=1)
Filter: ((\"timestamp\" > '1597855853329'::bigint) AND (context = 'hour'::aggregation_context))
Rows Removed by Filter: 2228311
-> Hash (cost=1.00..1.00 rows=50 width=32) (actual time=0.032..0.032 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> CTE Scan on base_table base_table_1 (cost=0.00..1.00 rows=50 width=32) (actual time=0.004..0.014 rows=50 loops=1)
Planning Time: 1.203 ms
Execution Time: 9177.185 ms
ฉันสังเกตเห็นว่าผู้วางแผนแบบสอบถามไม่ได้ใช้ดัชนีที่สร้างขึ้นvalue_aggregation_hourและสงสัยว่าทำไม หลังจาก googling บางครั้งฉันปิดการใช้งาน seqscan ในระหว่างการดีบักดำเนินการค้นหาอีกครั้งโดยexplain analyzeใช้แผนการสืบค้นข้อมูลต่อไปนี้:
Merge Left Join (cost=10000237612.82..10000237776.37 rows=494 width=1740) (actual time=212.122..215.857 rows=50 loops=1)
Merge Cond: (base_table.asset = value_aggregation_hour.asset)
CTE base_table
-> Limit (cost=10000000140.48..10000000140.61 rows=50 width=71) (actual time=1.745..1.756 rows=50 loops=1)
-> Sort (cost=10000000140.48..10000000145.48 rows=2001 width=71) (actual time=1.744..1.748 rows=50 loops=1)
Sort Key: latest_value.market_cap DESC
Sort Method: top-N heapsort Memory: 36kB
-> Seq Scan on latest_value (cost=10000000000.00..10000000074.01 rows=2001 width=71) (actual time=0.006..0.555 rows=2001 loops=1)
-> Sort (cost=377.41..377.54 rows=50 width=1740) (actual time=2.240..2.250 rows=50 loops=1)
Sort Key: base_table.asset
Sort Method: quicksort Memory: 127kB
-> Nested Loop (cost=0.28..376.00 rows=50 width=1740) (actual time=1.771..2.090 rows=50 loops=1)
-> CTE Scan on base_table (cost=0.00..1.00 rows=50 width=232) (actual time=1.746..1.773 rows=50 loops=1)
-> Index Scan using asset_pkey on asset (cost=0.28..7.50 rows=1 width=1508) (actual time=0.006..0.006 rows=1 loops=50)
Index Cond: (id = base_table.asset)
-> GroupAggregate (cost=237094.80..237228.44 rows=1977 width=54) (actual time=209.877..213.542 rows=15 loops=1)
Group Key: value_aggregation_hour.asset
-> Sort (cost=237094.80..237112.96 rows=7262 width=203) (actual time=209.618..210.065 rows=2501 loops=1)
Sort Key: value_aggregation_hour.asset
Sort Method: quicksort Memory: 761kB
-> Hash Semi Join (cost=111.95..236629.08 rows=7262 width=203) (actual time=0.868..206.008 rows=2501 loops=1)
Hash Cond: (value_aggregation_hour.asset = base_table_1.asset)
-> Bitmap Heap Scan on value_aggregation_hour (cost=110.32..235792.92 rows=287144 width=203) (actual time=0.758..155.291 rows=304163 loops=1)
Recheck Cond: (\"timestamp\" > '1597855085099'::bigint)
Rows Removed by Index Recheck: 215
Filter: (context = 'hour'::aggregation_context)
Heap Blocks: lossy=23414
-> Bitmap Index Scan on value_aggregation_hour_timestamp_idx (cost=0.00..38.54 rows=287851 width=0) (actual time=0.698..0.698 rows=234240 loops=1)
Index Cond: (\"timestamp\" > '1597855085099'::bigint)
-> Hash (cost=1.00..1.00 rows=50 width=32) (actual time=0.025..0.025 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> CTE Scan on base_table base_table_1 (cost=0.00..1.00 rows=50 width=32) (actual time=0.001..0.007 rows=50 loops=1)
Planning Time: 1.532 ms
Execution Time: 216.114 ms
ค่าใช้จ่ายสุดท้ายค่อนข้างสูง แต่ฉันคิดว่าเป็นเพราะไม่มีดัชนีlatest_valueและเขาจำเป็นต้องใช้ seqscan (off = ค่าใช้จ่ายที่สูงมาก?)
แต่ตอนนี้เขาใช้ดัชนีของvalue_aggregation_hourและมันเร็วขึ้น
เนื่องจากการปิดใช้งาน seqscan ไม่ใช่ตัวเลือกที่ถูกต้องยกเว้นการดีบักฉันจะทำให้สิ่งนี้ทำงานได้อย่างถูกต้องได้อย่างไร ฉันสามารถเพิ่มประสิทธิภาพการสืบค้นได้หรือไม่ อาจจะเปลี่ยน BRIN อะไรสักอย่างเขาจึงใช้มันแทน seqscan?
หรือการปรับแต่งพารามิเตอร์จะเพียงพอกว่าดังนั้นฟังก์ชันต้นทุนจึงคำนวณแตกต่างกัน? ฉันใช้อินสแตนซ์ RDS postgres db.t3.small ด้วยการกำหนดค่าเริ่มต้น
อัปเดต # 1 :
การลบAND asset IN (...)ข้อความค้นหาย่อย (ซ้ำซ้อน?) จะเพิ่มเวลาในการดำเนินการเป็นวินาที (seqscan on) ซึ่งเป็นแผนแบบสอบถามผลลัพธ์
Merge Left Join (cost=285605.54..289542.19 rows=494 width=1589) (actual time=10213.724..10561.884 rows=50 loops=1)"
Merge Cond: (latest_value.asset = value_aggregation_hour.asset)"
-> Sort (cost=517.65..517.77 rows=50 width=1579) (actual time=2.315..2.347 rows=50 loops=1)"
Sort Key: latest_value.asset"
Sort Method: quicksort Memory: 127kB"
-> Nested Loop (cost=140.89..516.24 rows=50 width=1579) (actual time=1.646..2.160 rows=50 loops=1)"
-> Limit (cost=140.61..140.74 rows=50 width=71) (actual time=1.623..1.634 rows=50 loops=1)"
-> Sort (cost=140.61..145.62 rows=2004 width=71) (actual time=1.622..1.626 rows=50 loops=1)"
Sort Key: latest_value.market_cap DESC"
Sort Method: top-N heapsort Memory: 36kB"
-> Seq Scan on latest_value (cost=0.00..74.04 rows=2004 width=71) (actual time=0.006..0.507 rows=2004 loops=1)"
-> Index Scan using asset_pkey on asset (cost=0.28..7.50 rows=1 width=1508) (actual time=0.010..0.010 rows=1 loops=50)"
Index Cond: (id = latest_value.asset)"
-> GroupAggregate (cost=285087.89..288994.63 rows=1977 width=54) (actual time=10196.939..10558.723 rows=1795 loops=1)"
Group Key: value_aggregation_hour.asset"
-> Sort (cost=285087.89..285734.90 rows=258802 width=203) (actual time=10196.652..10291.799 rows=295051 loops=1)"
Sort Key: value_aggregation_hour.asset"
Sort Method: external merge Disk: 66000kB"
-> Seq Scan on value_aggregation_hour (cost=0.00..236164.67 rows=258802 width=203) (actual time=8901.696..9056.748 rows=304558 loops=1)"
Filter: ((\"timestamp\" > '1597925634239'::bigint) AND (context = 'hour'::aggregation_context))"
Rows Removed by Filter: 2264599"
Planning Time: 1.149 ms"
Execution Time: 10573.183 ms"
อัปเดต # 2:
การเปลี่ยนแบบสอบถามเป็น a_horse_with_no_name ซ้ายเข้าร่วมคำแนะนำด้านข้างส่งผลให้:
Nested Loop Left Join (cost=141.45..576626.74 rows=6550 width=1589) (actual time=68.291..1313.768 rows=50 loops=1)
-> Nested Loop (cost=140.89..516.24 rows=50 width=1579) (actual time=3.897..5.104 rows=50 loops=1)
-> Limit (cost=140.61..140.74 rows=50 width=71) (actual time=3.855..3.931 rows=50 loops=1)
-> Sort (cost=140.61..145.62 rows=2004 width=71) (actual time=3.853..3.900 rows=50 loops=1)
Sort Key: latest_value.market_cap DESC
Sort Method: top-N heapsort Memory: 37kB
-> Seq Scan on latest_value (cost=0.00..74.04 rows=2004 width=71) (actual time=0.016..0.915 rows=2004 loops=1)
-> Index Scan using asset_pkey on asset (cost=0.28..7.50 rows=1 width=1508) (actual time=0.017..0.017 rows=1 loops=50)
Index Cond: (id = latest_value.asset)
-> GroupAggregate (cost=0.56..11519.59 rows=131 width=54) (actual time=26.169..26.169 rows=0 loops=50)
Group Key: value_aggregation_hour.asset
-> Index Scan using value_aggregation_hour_pkey on value_aggregation_hour (cost=0.56..11516.32 rows=131 width=203) (actual time=18.780..26.105 rows=50 loops=50)
Index Cond: ((context = 'hour'::aggregation_context) AND (\"timestamp\" > '1597926623087'::bigint) AND (asset = latest_value.asset))
Planning Time: 1.066 ms
Execution Time: 1320.452 ms
การปรับปรุงครั้งใหญ่จะทำงานได้ดี แต่ก็ยังไม่ดีเท่ากับการใช้ดัชนี BRIN ในแบบสอบถามเริ่มต้น
คำตอบ
5 LaurenzAlbe Aug 27 2020 at 19:27
PostgreSQL ประมาณการการสแกนตามลำดับในvalue_aggregation_hourราคาถูกกว่าการสแกนดัชนีเล็กน้อย (233000 เทียบกับ 236000) ในขณะที่ในความเป็นจริงมีราคาถูกกว่ามาก
การประมาณจำนวนแถวนั้นดีมากดังนั้นปัญหาน่าจะเกิดจาก PostgreSQL มีความคิดที่ผิดเกี่ยวกับเครื่องของคุณ คุณสามารถลองปรับปรุงสิ่งนั้น:
กำหนด
effective_cache_sizeเป็นจำนวนหน่วยความจำที่พร้อมใช้งานสำหรับการแคชข้อมูล (shared_buffers+ แคชระบบไฟล์)ค่าที่สูงกว่าจะลดค่าประมาณ ค่าใช้จ่ายในการสแกนดัชนี
ตั้ง
random_page_costเป็นค่าที่ต่ำกว่า หากการเข้าถึงแบบสุ่มเร็วเท่ากับการเข้าถึงตามลำดับบนระบบจัดเก็บข้อมูลของคุณให้ใช้ค่า 1ค่าที่ต่ำกว่าจะลดค่าประมาณ ค่าใช้จ่ายในการสแกนดัชนี