Hi! I got a query plan with a strange number of rows. Could you please
help me understand it?
create temp table ta (id int primary key, val int);
create temp table tb (id int primary key, aval int);
create temp table tc (id int primary key, aid int);
insert into ta select id, id from generate_series(1,1000) as id;
insert into tb select id, id from generate_series(500,1000) as id;
insert into tc select id, id from generate_series(400,1000) as id;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
SELECT 1
FROM ta ta1
WHERE EXISTS (SELECT 1
FROM tb
JOIN tc
ON tb.id = ta1.id and
ta1.id < 1000
where exists (select 1 from ta ta2 where ta2.id =
ta1.id));
QUERY PLAN
--------------------------------------------------------------------------------------
Seq Scan on ta ta1 (actual rows=500.00 loops=1)
Filter: EXISTS(SubPlan 2)
Rows Removed by Filter: 500
SubPlan 2
-> Result (actual rows=0.50 loops=1000)
One-Time Filter: ((ta1.id < 1000) AND (InitPlan 1).col1)
InitPlan 1
-> Index Only Scan using ta_pkey on ta ta2 (actual
rows=1.00 loops=999)
Index Cond: (id = ta1.id)
Heap Fetches: 999
-> Nested Loop (actual rows=0.50 loops=999)
-> Seq Scan on tb (actual rows=0.50 loops=999)
Filter: (id = ta1.id)
Rows Removed by Filter: 375
-> Seq Scan on tc (actual rows=1.00 loops=500)
(15 rows)
To be honest I can't understand why 0.50 number of rows here?
--
Regards,
Alena Rybakina
Postgres Professional
