Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19696: DISTINCT ON with a target-list set-returning function
 causes a 1000-fold selectivity underestimate
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: imchifan@163.com
Reply-To: imchifan@163.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 18 Sep 2026 07:00:05 +0000
Message-ID: <19696-135220dd92659a4e@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19696-135220dd92659a4e%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19696
Logged by:          Qifan Liu
Email address:      imchifan@163.com
PostgreSQL version: 18.6
Operating system:   Linux/amd64
Description:       =20

PostgreSQL version: PostgreSQL 20devel at
a12600b762c36d91450ce085fa25ef75250bc1c2; PostgreSQL 18.6; PostgreSQL 17.11
Operating system: Linux/amd64

Description
-----------
Filtering a one-column DISTINCT ON subquery by its distinct key is estimated
as unique even when a set-returning function in the target list expands each
key after the Unique node. In this example, the planner estimates one row
while the query returns 1000 rows. Such underestimation can lead to poor
plan choices.

Steps to reproduce
------------------
Run the following with psql:

\set ON_ERROR_STOP on
DROP TABLE IF EXISTS bugseer_postgres_00002_srf_distinct;
CREATE TABLE bugseer_postgres_00002_srf_distinct(k integer);
INSERT INTO bugseer_postgres_00002_srf_distinct
SELECT (g % 100) + 1 FROM generate_series(1, 10000) AS g;
ANALYZE bugseer_postgres_00002_srf_distinct;

EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY OFF)
SELECT *
FROM (
  SELECT DISTINCT ON (k) k, generate_series(1, 1000) AS expanded
  FROM bugseer_postgres_00002_srf_distinct
  ORDER BY k
  OFFSET 0
) AS s
WHERE k =3D 1;

Actual result
-------------
The outer Subquery Scan is estimated at one row but returns 1000 rows:

 Subquery Scan on s  (cost=3D809.39..2610.14 rows=3D1 width=3D8) (actual
rows=3D1000.00 loops=3D1)
   Filter: (s.k =3D 1)
   Rows Removed by Filter: 99000
   ->  ProjectSet  (cost=3D809.39..1360.14 rows=3D100000 width=3D8) (actual
rows=3D100000.00 loops=3D1)
         ->  Unique  (cost=3D809.39..859.39 rows=3D100 width=3D4) (actual
rows=3D100.00 loops=3D1)

Expected result
---------------
The outer row estimate should account for the target-list generate_series
expansion and estimate 1000 rows rather than treating the DISTINCT ON key as
producing at most one output row.

Additional information
----------------------
The issue was reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and
PostgreSQL 17.11.