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

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

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)

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)

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
;