فیلتر کردن بر روی DENSE_RANK() بهینه شده به صورت DISTINCT پایینرفته در YugabyteDB

توابع تحلیلی SQL (با نام مستعار توابع پنجره) قدرتمند هستند و می توانید از آنها برای فیلتر کردن ردیف اول در یک پنجره استفاده کنید. با این حال، تابع تحلیلی در یک پرسش فرعی پردازش می شود تا بعدا فیلتر شود، که ممکن است از برخی بهینه سازی ها در دسترسی به داده ها جلوگیری کند. من نشان خواهم داد که چگونه می توان با فیلتر کردن در طول اسکن شاخص، همین کار را با کارایی بیشتری انجام داد.
در اینجا یک جدول ساده با نوع مشاهده، نام، متن و مهر زمانی آمده است:
drop table observations;
create extension if not exists orafce;
create table observations (
primary key ( observation_type asc,observation_name asc, observation_date desc )
, observation_type text
, observation_name text
, observation_date timestamptz
, observation_text text
);
insert into observations select
dbms_random.string('U',1),dbms_random.string('U',2),clock_timestamp(), dbms_random.string('L',100) from generate_series(1,1000);
\watch 0.01
DENSE_RANK() = 1
من آخرین مقدار را برای هر مشاهده در یک نوع خاص می خواهم.
در اینجا برنامه اجرا هنگام استفاده از DENSE_RANK() و فیلتر کردن رتبه شماره 1 آمده است:
explain (analyze, dist, costs off)
select o."observation_type", o."observation_name", o."observation_text"
from (
select *, dense_rank () over (
partition by o."observation_type", o."observation_name"
order by o."observation_date" desc
) as rank_number from observations o
where o."observation_type" = 'Z'
) as o where "o".rank_number=1
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Subquery Scan on o (actual time=3.176..466.641 rows=143 loops=1)
Filter: (o.rank_number = 1)
Rows Removed by Filter: 162857
-> WindowAgg (actual time=3.174..456.372 rows=163000 loops=1)
-> Index Scan using observations_pkey on observations o_1 (actual time=3.156..342.562 rows=163000 loops=1)
Index Cond: (observation_type = 'Z'::text)
Storage Index Read Requests: 160
Storage Index Execution Time: 228.001 ms
Planning Time: 0.103 ms
Execution Time: 466.741 ms
Storage Read Requests: 160
Storage Write Requests: 0
Storage Execution Time: 228.001 ms
Peak Memory Usage: 491 kB
(14 rows)
این خوانده بود rows=163000
ردیف هایی از ذخیره سازی توزیع شده با Storage Index Read Requests: 160
تماس های شبکه و سپس پسوند postgres حذف شده است Rows Removed by Filter: 162857
از آن ردیف ها فقط نگه دارید rows=143
که فیلتر را تأیید می کند.
پرس و جوی متمایز و اسکالر
این را میتوان با خواندن تنها ردیفهای مورد نیاز، محدود کردن تماسهای شبکه، انتقال و فیلتر کردن تکفرآیند بیشتر بهینه کرد.
این در این شاخص به لطف اسکن هیبریدی YugabyteDB امکان پذیر است، اما، حداقل در این نسخه (YB-2.17)، فیلتر کردن عملکرد پنجره به اسکن شاخص پایین نمی آید.
برای اجازه بهینه سازی اسکن ترکیبی، از DISTINCT در یک پرسش فرعی برای جایگزینی PARTITION BY پنجره تحلیلی استفاده می کنم. سپس، برای هر پنجره، آخرین مقدار را با یک زیرپرس و جوی اسکالر دریافت میکنم که ORDER BY و LIMIT را انجام میدهد تا اولین سطر را برای هر کدام به دست بیاورم که معادل DENSE_RANK()=1 است.
explain (analyze, dist, costs off)
select d."observation_type", d."observation_name" , (
select observation_text
from observations
where ( observation_type , observation_name )
=(d."observation_type", d."observation_name")
order by observation_date desc
limit 1
) from (
-- get all distinct values with push down optimization
select distinct o."observation_type", o."observation_name"
from observations o
-- use range condition to force an index scan
where o."observation_type" between 'Z' and 'Z'
) d
;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Subquery Scan on d (actual time=1.757..37.104 rows=143 loops=1)
-> Unique (actual time=1.392..1.695 rows=143 loops=1)
-> Index Scan using observations_pkey on observations o_1 (actual time=1.391..1.607 rows=143 loops=1)
Index Cond: ((observation_type >= 'Z'::text) AND (observation_type <= 'Z'::text))
Storage Index Read Requests: 1
Storage Index Execution Time: 0.000 ms
SubPlan 1
-> Limit (actual time=0.246..0.246 rows=1 loops=143)
-> Index Scan using observations_pkey on observations (actual time=0.237..0.237 rows=1 loops=143)
Index Cond: ((observation_type = d.observation_type) AND (observation_name = d.observation_name))
Storage Index Read Requests: 1
Storage Index Execution Time: 0.224 ms
Planning Time: 1.614 ms
Execution Time: 37.187 ms
Storage Read Requests: 144
Storage Write Requests: 0
Storage Execution Time: 32.000 ms
Peak Memory Usage: 56 kB
(18 rows)
اضافه کرده ام between 'Z' and 'Z'
زیرا در این نسخه YugabyteDB از Index Scan استفاده نمی شود = 'Z'
. همیشه مهم است که هنگام انجام چنین بهینه سازی، برنامه اجرا را بررسی کنید و یک نظر در پرس و جو اضافه کنید.
پیوستن DISTINCT و LATERAL
هنگامی که به بیش از یک ستون نیاز دارید، بهتر است از یک پیوست LATERAL به جای یک زیرپرسوجویی اسکالر پرس و جو کنید:
explain (analyze, dist, costs off)
select o.* from (
-- get all distinct values with push down optimization
select distinct o."observation_type", o."observation_name"
from observations o
-- use range condition to force an index scan
where o."observation_type" between 'Z' and 'Z'
) d , lateral (
-- get additional information for each distinct value
select * from observations
where ( observation_type , observation_name )
=(d."observation_type", d."observation_name")
order by observation_date desc
limit 1
) o
;
طرح اجرا نیز مشابه است. مهمترین چیز این است که عدم وجود آن را تأیید کنید Rows Removed by Filter
، Index Scan
یا Index Only Scan
برای هر دو پرسش فرعی، کوچک Storage Index Read Requests
برای Unique
شعبه و کل Storage Read Requests
مطابقت دادن rows=
برای Unique
شاخه.