Date: Sat, 3 May 2025 14:34:47 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Index not used in certain nested views but not in others
Message-ID: <20250503123447.6axo52bqm2lyy33k@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <20250430151647.7kootztymzznydn5@victor>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="tdoi43ypoznsufan"
Content-Disposition: inline
In-Reply-To: <20250430151647.7kootztymzznydn5@victor>
Archived-At: <https://www.postgresql.org/message-id/20250503123447.6axo52bqm2lyy33k%40hjp.at>
Precedence: bulk


--tdoi43ypoznsufan
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2025-04-30 17:16:47 +0200, Markus Demleitner wrote:
> There is another view made up of about 20 tables, looking somewhat
> like this:
[...]
> The first view, ivoa.obs_radio, is just a few hundred records,
> dfbsspec.raw_spectra is about 23 Megarows,
[...]
>                                  	 ->  Parallel Seq Scan on raw_spectra  =
(cost=3D0.00..2626995.66 rows=3D5803266 width=3D756) (actual time=3D0.137..=
6841.379 rows=3D4642657 loops=3D5)
[...]
>=20
> My problem is: I can't seem to figure out why Postgres chooses to ignore
> the pubdid index on raw_spectra.pub_did and instead does the
> time-consuming seqscan.

It estimates that it has to read 5803266 of those 23000000 rows. That's
25 %! I'm not surprised that it thinks just reading the whole table is
faster than doing almost 6 million index lookups (The actual count is
4642657, so that estimate wasn't totally off).


> Trying to investigate more closely, I wanted to simplify the
> situation and created a view like ivoa.obscore but only having the
> evil table in it:
[...]
>    ->  Nested Loop  (cost=3D0.56..4871.60 rows=3D561 width=3D0) (actual t=
ime=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=
=3D0.56..8.58 rows=3D1 width=3D66) (actual time=3D0.003..0.003 rows=3D0 loo=
ps=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.

> So, when the SELECT statement on dfbsspec.ssa stands along in the view
> definition, Postgres does the right thing; when the exact same query
> stands in a UNION ALL with other tables, Postgres doesn't use the
> index.  Hu?

It is obviously not the exact same query if one of them need to read
10000 times as many rows.

        hjp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--tdoi43ypoznsufan
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmgWDWAACgkQ8g5IURL+
KF0wxw/+Kk1039D27WbwH0FlBDPS6+oCngTm0Q+MIRNJ/WFKLCLA0C0NnFO6/Pf5
bVQkHa5T1iBku//ygnd9ig1RgzTqv8pQ2KtrmGwNrshvzL70OAR7uFZAUPgm/buf
E6DkxX2aIWZ3MY4Rxbgzp5xPeNwT6mgQpK70PnYTyrgCNme/Rx/KOiuI8yu+BlIj
OoluVoCm6rp0FIh6FQIUnXlf6GMfSsxhS589hl7O3DdDgYCYwnlXhUHzA7Qy9W6p
fH8CrWGXGD38wEWyp5wP+IF5bTbHHpaAl8u/uRym5/PK5Y/XwOZpZnn9pkAWL5BK
lnoQ0zF+CmtYql+UtDycgSGR2MVuc1CSTweKM3s4grKZ7C+1IdUcYJGpa61OyP+I
vQTSKBKJXKfAUq/aK7cdh/7Lr6zHz1LHuDsRGvqUEq/V8YKnguqS/ik6cOgK7Ckh
Gsa+AXm2xbB9L1pmyP3MY9fOzxduq9lPe8lM+OgYdivdvZJWo84TZirGuGo7bNdW
zUsaGlNNopAopJsc08zW+Lie9ZrsFXiZLmlAMT3hyNl+JIkUjscoe0+oB4Rb7Uhf
w5qfVhoJuujVZdA776P1F/3FuUvLg1PH+wMk11bhyyYQG6OU0tIKM4FhYhH+8qjA
nCx4oGu7CwkTD89JsxXlqkWsPnTPLN0NCGtaJiAIEifYiPxliks=
=DEeT
-----END PGP SIGNATURE-----

--tdoi43ypoznsufan--