From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Peter J. Holzer" <hjp-pgsql@hjp.at>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Index not used in certain nested views but not in others
In-reply-to: <20250503123447.6axo52bqm2lyy33k@hjp.at>
References: <20250430151647.7kootztymzznydn5@victor> <20250503123447.6axo52bqm2lyy33k@hjp.at>
Comments: In-reply-to "Peter J. Holzer" <hjp-pgsql@hjp.at>
	message dated "Sat, 03 May 2025 14:34:47 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2173353.1746287428.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 03 May 2025 11:50:28 -0400
Message-ID: <2173354.1746287428@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2173354.1746287428%40sss.pgh.pa.us>
Precedence: bulk

"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> On 2025-04-30 17:16:47 +0200, Markus Demleitner wrote:
>> dfbsspec.raw_spectra is about 23 Megarows,
>> ->  Parallel Seq Scan on raw_spectra  (cost=3D0.00..2626995.66 rows=3D5=
803266 width=3D756) (actual time=3D0.137..6841.379 rows=3D4642657 loops=3D=
5)

> It estimates that it has to read 5803266 of those 23000000 rows.

No, you're misreading that (I admit it's confusing).  The rows report
is the average per parallel worker, and the loops count indicates we
had 5 workers.  So actually this parallel seqscan emitted 4642657*5
=3D 23213285 rows, or the whole table, which is what should be expected
given it has no filter condition and no LIMIT.

(I am wondering why the estimate is only 5803266 rows, because I don't
think that number is scaled for the number of workers...)

>> ->  Nested Loop  (cost=3D0.56..4871.60 rows=3D561 width=3D0) (actual ti=
me=3D2.478..2.479 rows=3D0 loops=3D1)
>>       ->  Seq Scan on main  (cost=3D0.00..52.61 rows=3D561 width=3D48) =
(actual time=3D0.011..0.317 rows=3D561 loops=3D1)
>>       ->  Index Scan using raw_spectra_pub_did on raw_spectra  (cost=3D=
0.56..8.58 rows=3D1 width=3D66) (actual time=3D0.003..0.003 rows=3D0 loops=
=3D561)
>>             Index Cond: (pub_did =3D main.obs_publisher_did)

> Here you select only 561 rows. That's just a tiny fraction of the whole
> table, so the optimizer estimates that doing a few hundred index lookups
> is faster than reading the whole table.

The point here is that the chosen plan shape allows pushing the join
qual "raw_spectra.pub_did =3D main.obs_publisher_did" down to be an
index condition, which is exactly what we have to do if we want to
avoid reading all of raw_spectra.  What Markus is complaining about
is that that fails to happen if there's a UNION ALL in the way.
Postgres is capable of doing that in other cases, so it's a fair
question.

			regards, tom lane