Message-ID: <e02b5ba8-d879-4121-a21e-8a3928be337e@postgrespro.ru>
Date: Thu, 6 Mar 2025 16:17:44 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: explain analyze rows=%.0f
To: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 Andrei Lepikhov <lepihov@gmail.com>, pgsql-hackers@lists.postgresql.org,
 Guillaume Lelarge <guillaume@lelarge.info>,
 Matheus Alcantara <matheusssilv97@gmail.com>
References: <CA+TgmoZv8DvmeQHQAX+rkn68id2N+DoUhFZB36d-_w1tyAeqeA@mail.gmail.com>
 <CA+TgmobfeePej0uNfbLy0v6jUrB3D9m1Kh+QQ40_-A9KQjkqUw@mail.gmail.com>
 <f428e80d-8607-4a9d-950c-be9600f24904@gmail.com>
 <01ec86c1-e04b-4cbe-b641-22cae8b87604@tantorlabs.com>
 <CA+TgmobVWYnYDj1HZ--iqeKx+n7zTgtYCar+YfZ0V6wiX-t0Mg@mail.gmail.com>
 <73e2440e-7d8b-4860-974d-931a93326d95@gmail.com>
 <CA+Tgmoaob_3aNF5S49WFoJs6Rb37rNnd5WH_e9nXAn_jtWWCLQ@mail.gmail.com>
 <3737950.1740182671@sss.pgh.pa.us>
 <CA+TgmobXwtS0ncAVfukE-Uhhy8Zt5rQWwQb2WHBcSop8r4Lcgw@mail.gmail.com>
 <3816053.1740190267@sss.pgh.pa.us>
 <CA+TgmoZp7LstKmDwyfUuB+6aFFhn0N3fv2QxTD=dkrxXVJ5dZA@mail.gmail.com>
 <CA+TgmoazzVHn8sFOMFAEwoqBTDxKT45D7mvkyeHgqtoD2cn58Q@mail.gmail.com>
 <40663fc5-edac-4b45-a2aa-a76976700ed9@tantorlabs.com>
 <CA+TgmoZxRP8cEhfdF27k600B_=8DXpj=8kiM7s_6qCHCqxQPmA@mail.gmail.com>
 <CA+TgmoapgkOFmezwVjJbs5Xq9F+Mfm9aNouBgTq_AuqQJkLYEg@mail.gmail.com>
 <f61cb18b-0e51-4350-b122-f2557e02c487@tantorlabs.com>
Content-Language: en-US
From: Alena Rybakina <a.rybakina@postgrespro.ru>
In-Reply-To: <f61cb18b-0e51-4350-b122-f2557e02c487@tantorlabs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/e02b5ba8-d879-4121-a21e-8a3928be337e%40postgrespro.ru>
Precedence: bulk

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