In tinkering with pgbench and EXPLAIN, I found the following:
[root@fc26c91163dc /]# pgbench -i
100000 of 100000 tuples (100%) done (elapsed 0.21 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
[root@fc26c91163dc /]# psql
psql (12.1)
Type "help" for help.
postgres=# insert into pgbench_branches values (generate_series(2,100000),1,'');
INSERT 0 99999
postgres=# vacuum full analyze;
VACUUM
postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON (a.bid=b.bid) WHERE a.aid < 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=15.01..16.64 rows=106 width=194) (actual time=0.114..0.323 rows=99 loops=1)
Merge Cond: (b.bid = a.bid)
-> Index Scan using pgbench_branches_pkey on pgbench_branches b (cost=0.29..4247.29 rows=100000 width=97) (actual time=0.013..0.016 rows=2 loops=1)
-> Sort (cost=14.71..14.98 rows=106 width=97) (actual time=0.091..0.098 rows=99 loops=1)
Sort Key: a.bid
Sort Method: quicksort Memory: 38kB
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts a (cost=0.29..11.15 rows=106 width=97) (actual time=0.008..0.038 rows=99 loops=1)
Index Cond: (aid < 100)
Planning Time: 0.690 ms
Execution Time: 0.380 ms
(10 rows)
Notice Index Scan using pgbench_branches_pkey...actual time=0.013..0.016 rows=2...
There is only one distinct bid value in pgbench_accounts:
postgres=# select distinct bid from pgbench_accounts ;
bid
-----
1
(1 row)
bid is a primary key in pgbench_branches:
postgres=# select * from pgbench_branches where bid = 1;
bid | bbalance | filler
-----+----------+--------
1 | 0 |
(1 row)
So why does the Index Scan produce rows=2 instead of rows=1?
