زمان جایگزینی IN() با EXISTS() – سوالات فرعی همبسته و نامرتبط

create extension if not exists pgcrypto;

create table users (
 user_id uuid primary key default gen_random_uuid()
 , user_country text
 , active boolean default true
);

create table messages (
 message_id uuid primary key default gen_random_uuid()
 , user_id uuid references users
 , message_country text
);

insert into users (active, user_country)
select 
 random()>0.1
 ,(ARRAY['CH','FR','ES'])[3*random()]
from generate_series(1,10)
;

insert into messages (user_id,message_country)
select 
 case when random()>0.1 then user_id end
 ,(ARRAY['CH','FR','ES'])[3*random()]
from users, generate_series(1,1000)
;

create index on users(user_country);
create index on messages(message_country asc, user_id asc);
analyze users, messages;

yugabyte=# explain (analyze, dist, costs off, summary off)
select m.*
from messages m
where user_id in (
 select user_id
 from users u
 where active
)
;

                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Join (actual time=3.013..9.913 rows=8091 loops=1)
   Hash Cond: (m.user_id = u.user_id)
   ->  Seq Scan on messages m (actual time=1.248..6.190 rows=10000 loops=1)
         Storage Table Read Requests: 12
         Storage Table Read Execution Time: 4.078 ms
         Storage Table Rows Scanned: 10000
   ->  Hash (actual time=1.745..1.745 rows=9 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on users u (actual time=1.731..1.734 rows=9 loops=1)
               Storage Filter: active
               Storage Table Read Requests: 1
               Storage Table Read Execution Time: 0.884 ms
               Storage Table Rows Scanned: 10

yugabyte=# explain (analyze, dist, costs off, summary off)
select m.*
from messages m
join users u
on (m.user_id=u.user_id)
where u.active
;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Join (actual time=2.469..9.494 rows=8091 loops=1)
   Hash Cond: (m.user_id = u.user_id)
   ->  Seq Scan on messages m (actual time=1.274..6.371 rows=10000 loops=1)
         Storage Table Read Requests: 12
         Storage Table Read Execution Time: 4.188 ms
         Storage Table Rows Scanned: 10000
   ->  Hash (actual time=1.178..1.179 rows=9 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on users u (actual time=1.127..1.131 rows=9 loops=1)
               Storage Filter: active
               Storage Table Read Requests: 1
               Storage Table Read Execution Time: 1.052 ms
               Storage Table Rows Scanned: 10

yugabyte=# explain (analyze, dist, costs off, summary off)
select m.*
from messages m
where exists (
 select from users u
 where
 m.user_id = u.user_id
 and active
)
;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Join (actual time=1.875..8.790 rows=8091 loops=1)
   Hash Cond: (m.user_id = u.user_id)
   ->  Seq Scan on messages m (actual time=1.154..6.126 rows=10000 loops=1)
         Storage Table Read Requests: 12
         Storage Table Read Execution Time: 4.002 ms
         Storage Table Rows Scanned: 10000
   ->  Hash (actual time=0.706..0.706 rows=9 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on users u (actual time=0.692..0.695 rows=9 loops=1)
               Storage Filter: active
               Storage Table Read Requests: 1
               Storage Table Read Execution Time: 0.621 ms
               Storage Table Rows Scanned: 10

yugabyte=# explain (analyze, dist, costs off, summary off)
select m.*
from messages m
where user_id in (
 select user_id
 from users u
 where active
 and u.user_country = m.message_country
)
;

                                QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on messages m (actual time=4.163..5657.120 rows=2084 loops=1)
   Filter: (SubPlan 1)
   Rows Removed by Filter: 7916
   SubPlan 1
     ->  Seq Scan on users u (actual time=0.559..0.560 rows=2 loops=10000)
           Storage Filter: (active AND (user_country = m.message_country))
           Storage Table Read Requests: 1
           Storage Table Read Execution Time: 0.501 ms
           Storage Table Rows Scanned: 11

yugabyte=# explain (analyze, dist, costs off, summary off)
select m.*
from messages m
where message_country in (
 select user_country
 from users u
 where u.user_id=m.user_id
 and active
)
;
                                QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on messages m (actual time=4.835..5720.221 rows=2084 loops=1)
   Filter: (SubPlan 1)
   Rows Removed by Filter: 7916
   SubPlan 1
     ->  Seq Scan on users u (actual time=0.566..0.567 rows=1 loops=10000)
           Storage Filter: (active AND (user_id = m.user_id))
           Storage Table Read Requests: 1
           Storage Table Read Execution Time: 0.507 ms
           Storage Table Rows Scanned: 11

yugabyte=# explain (analyze, dist, costs off, summary off)
select *
from messages m
join users    u on (
 m.user_id=u.user_id
 and
 m.message_country=u.user_country
 and u.active
) ;
                                                                        QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 YB Batched Nested Loop Join (actual time=4.527..9.453 rows=2084 loops=1)
   Join Filter: ((m.user_id = u.user_id) AND (m.message_country = u.user_country))
   ->  Seq Scan on users u (actual time=0.790..0.794 rows=9 loops=1)
         Storage Filter: active
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 0.691 ms
         Storage Table Rows Scanned: 10
   ->  Index Scan using messages_message_country_user_id_idx on messages m (actual time=3.544..7.153 rows=2084 loops=1)
         Index Cond: (ROW(message_country, user_id) = ANY (ARRAY[ROW(u.user_country, u.user_id), ROW($1, $1025), ROW($2, $1026), ..., ROW($1023, $2047)]))
         Storage Table Read Requests: 3
         Storage Table Read Execution Time: 4.701 ms
         Storage Table Rows Scanned: 2084
         Storage Index Read Requests: 3
         Storage Index Read Execution Time: 0.831 ms
         Storage Index Rows Scanned: 2084

yugabyte=# explain (analyze, dist, costs off, summary off)
select m.*
from messages m
where ( m.message_country,m.user_id) in (
 select u.user_country,   u.user_id
 from users u
 where active
)
;
                                                                        QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 YB Batched Nested Loop Join (actual time=5.342..10.323 rows=2084 loops=1)
   Join Filter: ((m.message_country = u.user_country) AND (m.user_id = u.user_id))
   ->  Seq Scan on users u (actual time=0.929..0.933 rows=9 loops=1)
         Storage Filter: active
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 0.830 ms
         Storage Table Rows Scanned: 10
   ->  Index Scan using messages_message_country_user_id_idx on messages m (actual time=4.231..7.894 rows=2084 loops=1)
         Index Cond: (ROW(message_country, user_id) = ANY (ARRAY[ROW(u.user_country, u.user_id), ROW($1, $1025), ROW($2, $1026), ..., ROW($1023, $2047)]))
         Storage Table Read Requests: 3
         Storage Table Read Execution Time: 5.359 ms
         Storage Table Rows Scanned: 2084
         Storage Index Read Requests: 3
         Storage Index Read Execution Time: 0.840 ms
         Storage Index Rows Scanned: 2084

yugabyte=# explain (analyze, dist, costs off, summary off)
select m.*
from messages m
where exists (
 select from users u
 where
 m.user_id = u.user_id
 and
 m.message_country=u.user_country
 and u.active
)
;
                                                                        QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 YB Batched Nested Loop Join (actual time=4.356..10.501 rows=2084 loops=1)
   Join Filter: ((m.user_id = u.user_id) AND (m.message_country = u.user_country))
   ->  Seq Scan on users u (actual time=0.767..0.770 rows=9 loops=1)
         Storage Filter: active
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 0.658 ms
         Storage Table Rows Scanned: 10
   ->  Index Scan using messages_message_country_user_id_idx on messages m (actual time=3.409..8.277 rows=2084 loops=1)
         Index Cond: (ROW(message_country, user_id) = ANY (ARRAY[ROW(u.user_country, u.user_id), ROW($1, $1025), ROW($2, $1026), ..., ROW($1023, $2047)]))
         Storage Table Read Requests: 3
         Storage Table Read Execution Time: 5.606 ms
         Storage Table Rows Scanned: 2084
         Storage Index Read Requests: 3
         Storage Index Read Execution Time: 0.790 ms
         Storage Index Rows Scanned: 2084

alter table messages drop constraint messages_user_id_fkey;
alter table users drop constraint users_pkey;
create index on users ( user_id );
analyze users;

 YB Batched Nested Loop Join (actual time=37.856..43.425 rows=2084 loops=1)
   Join Filter: ((m.user_id = u.user_id) AND (m.message_country = u.user_country))
   ->  HashAggregate (actual time=0.926..0.928 rows=9 loops=1)
         Group Key: u.user_id, u.user_country
         ->  Seq Scan on users u (actual time=0.913..0.916 rows=9 loops=1)
               Storage Filter: active

 Merge Semi Join (actual time=12.213..33.015 rows=2084 loops=1)
   Merge Cond: ((m.message_country = u.user_country) AND (m.user_id = u.user_id))
   ->  Index Scan using messages_message_country_user_id_idx on messages m (actual time=3.746..30.814 rows=8322 loops=1)
         Storage Table Read Requests: 9