Re: explain analyze rows=%.0f

Alena Rybakina <a.rybakina@postgrespro.ru>

From: Alena Rybakina <a.rybakina@postgrespro.ru>
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>
Date: 2025-03-06T13:17:44Z
Lists: pgsql-hackers
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




Commits

  1. EXPLAIN: Always use two fractional digits for row counts.

  2. Adjust EXPLAIN test case to filter out "Actual Rows" values.

  3. Allow EXPLAIN to indicate fractional rows.

  4. Fix pgbench performance issue induced by commit af35fe501.