agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: imchifan@163.com
Subject: BUG #19696: DISTINCT ON with a target-list set-returning function causes a 1000-fold selectivity underestimate
Date: Fri, 18 Sep 2026 07:00:05 +0000
Message-ID: <19696-135220dd92659a4e@postgresql.org> (raw)
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:
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 = 1;
Actual result
-------------
The outer Subquery Scan is estimated at one row but returns 1000 rows:
Subquery Scan on s (cost=809.39..2610.14 rows=1 width=8) (actual
rows=1000.00 loops=1)
Filter: (s.k = 1)
Rows Removed by Filter: 99000
-> ProjectSet (cost=809.39..1360.14 rows=100000 width=8) (actual
rows=100000.00 loops=1)
-> Unique (cost=809.39..859.39 rows=100 width=4) (actual
rows=100.00 loops=1)
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.
Message-ID: <19696-135220dd92659a4e@postgresql.org>
Permalink: ../19696-135220dd92659a4e@postgresql.org/
Also on: postgresql.org/message-id/19696-135220dd92659a4e@postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, imchifan@163.com
Subject: Re: BUG #19696: DISTINCT ON with a target-list set-returning function causes a 1000-fold selectivity underestimate
In-Reply-To: <19696-135220dd92659a4e@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox