Date: Thu, 23 Apr 2020 13:18:58 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	Thomas Kellerer <shammat@gmx.net>,
	"pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: PostgreSQL does not choose my indexes well
Message-ID: <20200423171858.GE13712@tamriel.snowman.net>
References: <35dc90d6-e0c2-13b8-3184-e3db5d854a78@gmail.com>
 <1bb37a19-77f6-8c49-073e-ed6251c98583@gmx.net>
 <CAKFQuwY9twbudMDvmuo+9Mjy0y-cJcvzPazD07-oB+WV1qOMnA@mail.gmail.com>
 <4315.1587655779@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="/8JRgMfzn8PY/uxg"
Content-Disposition: inline
In-Reply-To: <4315.1587655779@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Thursday, April 23, 2020, Thomas Kellerer <shammat@gmx.net> wrote:
> >> Plus: scanning idx_tabla_entidad is more efficient because that index =
is
> >> smaller.
>=20
> > Really?  The absence of 33 million rows in the partial index seems like=
 it
> > would compensate fully and then some for the extra included columns.
>=20
> On the other hand, an indexscan is likely to end up being effectively
> random-access rather than the purely sequential access involved in
> a seqscan. =20

An indexscan is what was chosen though, so this doesn't really seem to
be a question of index scan vs. seq scan, it's a question of why one
index vs. another, though it seems a bit odd that we'd pick a regular
index scan instead of a BitmapHeap/Index scan.

> (If the index was built recently, then it might not be
> so bad --- but the planner doesn't know that, so it assumes that the
> index leaf pages are laid out pretty randomly.)  Moreover, unless the
> table is mostly marked all-visible, there will be another pile of
> randomized accesses into the heap to validate visibility of the index
> entries.

If the table *is* marked all visible, though, then certainly that index
will be better, and I think that's what a lot of this is coming down to
in this particular case.

Populating the tables provided based on the minimal info we got,
minimizing the numbers of pages that 'cod_tabla=3D4' is on:

insert into tabla select generate_series, 'abcdef' from generate_series(1,2=
0);
insert into entidad select generate_series, 4, generate_series+1 from gener=
ate_series(1,1409985);
insert into entidad select generate_series+1409985, generate_series % 20 + =
1, generate_series+1 from generate_series(1,34413354) where generate_series=
 % 20 + 1 <> 4;
vacuum analyze entidad;

With this, the table is 1.7GB, idx_tabla_entidad is about 700MB, while
idx_entidad_tabla_4 is only 81MB.

With this, on v12-HEAD, PG will happily use the partial index:

=3D# explain (analyze, buffers) select * from entidad where cod_tabla =3D 4;
                                                                     QUERY =
PLAN                                                                     =
=20
---------------------------------------------------------------------------=
--------------------------------------------------------------------------
 Index Only Scan using idx_entidad_tabla_4 on entidad  (cost=3D0.43..55375.=
20 rows=3D1422085 width=3D24) (actual time=3D0.050..144.745 rows=3D1409985 =
loops=3D1)
   Heap Fetches: 0
   Buffers: shared hit=3D8497
 Planning Time: 0.338 ms
 Execution Time: 183.081 ms
(5 rows)

Dropping that index and then running it again shows:

=3D# explain (analyze, buffers) select * from entidad where cod_tabla =3D 4;
                                                                  QUERY PLA=
N                                                                 =20
---------------------------------------------------------------------------=
-------------------------------------------------------------------
 Bitmap Heap Scan on entidad  (cost=3D26641.72..608515.59 rows=3D1422085 wi=
dth=3D24) (actual time=3D102.844..242.522 rows=3D1409985 loops=3D1)
   Recheck Cond: (cod_tabla =3D 4)
   Heap Blocks: exact=3D8981
   Buffers: shared read=3D12838
   ->  Bitmap Index Scan on idx_tabla_entidad  (cost=3D0.00..26286.20 rows=
=3D1422085 width=3D0) (actual time=3D101.969..101.969 rows=3D1409985 loops=
=3D1)
         Index Cond: (cod_tabla =3D 4)
         Buffers: shared read=3D3857
 Planning Time: 0.264 ms
 Execution Time: 277.854 ms
(9 rows)

If we spread out where the 'cod_tabla=3D4' tuples are, the partial index
is still used (note that we end up with more like 1.7M tuples instead of
1.4M, but I don't think that's terribly relevant):

truncate entidad;
insert into entidad select generate_series, generate_series % 20 + 1, gener=
ate_series+1 from generate_series(1,34413354);

=3D# explain (analyze, buffers) select * from entidad where cod_tabla =3D 4;
                                                                     QUERY =
PLAN                                                                     =
=20
---------------------------------------------------------------------------=
--------------------------------------------------------------------------
 Index Only Scan using idx_entidad_tabla_4 on entidad  (cost=3D0.43..65231.=
31 rows=3D1664459 width=3D24) (actual time=3D0.036..185.171 rows=3D1720668 =
loops=3D1)
   Heap Fetches: 0
   Buffers: shared hit=3D10375
 Planning Time: 0.247 ms
 Execution Time: 233.205 ms
(5 rows)

Things get a lot worse when we drop that partial index:

drop index idx_entidad_tabla_4;

=3D# explain (analyze, buffers) select * from entidad where cod_tabla =3D 4;
                                                                  QUERY PLA=
N                                                                 =20
---------------------------------------------------------------------------=
-------------------------------------------------------------------
 Bitmap Heap Scan on entidad  (cost=3D30088.12..270087.86 rows=3D1664459 wi=
dth=3D24) (actual time=3D163.418..1465.733 rows=3D1720668 loops=3D1)
   Recheck Cond: (cod_tabla =3D 4)
   Heap Blocks: exact=3D219194
   Buffers: shared read=3D223609
   ->  Bitmap Index Scan on idx_tabla_entidad  (cost=3D0.00..29672.01 rows=
=3D1664459 width=3D0) (actual time=3D128.544..128.544 rows=3D1720668 loops=
=3D1)
         Index Cond: (cod_tabla =3D 4)
         Buffers: shared read=3D4415
 Planning Time: 0.094 ms
 Execution Time: 1515.066 ms
(9 rows)

To get the kind of plan that the OP got, I dropped random_page_cost to 1.0:

=3D# explain (analyze, buffers) select * from entidad where cod_tabla =3D 4;
                                                                   QUERY PL=
AN                                                                  =20
---------------------------------------------------------------------------=
---------------------------------------------------------------------
 Index Scan using idx_tabla_entidad on entidad  (cost=3D0.56..251946.06 row=
s=3D1664459 width=3D24) (actual time=3D0.216..1236.371 rows=3D1720668 loops=
=3D1)
   Index Cond: (cod_tabla =3D 4)
   Buffers: shared read=3D223609
 Planning Time: 0.192 ms
 Execution Time: 1283.460 ms
(5 rows)

Even in that case though, when I recreate the partial index:

=3D# explain (analyze, buffers) select * from entidad where cod_tabla =3D 4;
                                                                     QUERY =
PLAN                                                                     =
=20
---------------------------------------------------------------------------=
--------------------------------------------------------------------------
 Index Only Scan using idx_entidad_tabla_4 on entidad  (cost=3D0.43..35033.=
31 rows=3D1664459 width=3D24) (actual time=3D0.039..211.644 rows=3D1720668 =
loops=3D1)
   Heap Fetches: 0
   Buffers: shared hit=3D7 read=3D10368
 Planning Time: 0.144 ms
 Execution Time: 256.304 ms
(5 rows)

So it's not really clear what's happening in the OP's case, we'd really
need more information to figure it out, it seems to me.

> Bottom line is that this choice is not nearly as open-and-shut as
> the OP seems to think.  In fact, it's fairly likely that this is a
> badly designed index, not a well-designed one that the planner is
> unaccountably failing to use.  Both covering indexes and partial
> indexes are easily-misused features that can make performance worse
> not better.

While I agree they can be mis-used, and that it's not open-and-shut,
it's not really clear to me what's going on that's causing us to avoid
that partial index in the OP's case when we'll certainly use it in
general.  The partial index in this particular case seems like it'd be
perfectly well suited to this query and that we should be using it (as
we are in the tests I did above).

I do wonder if we are maybe missing a bet at times though, considering
that I'm pretty sure we'll always go through the index in order, and
therefore randomly, even when we don't actually need the results in
order..?  Has there been much consideration for just opening an index
and sequentially scanning it in cases like this where we have to go
through all of the index anyway and don't need the results in order?  I
get that we'd still have to consider random access costs if the VM is
out of date, but if it's not, I would think we could give such an
approach a lower cost as we'd be going through the index sequentially
instead of the normal random access that we do.

Thanks,

Stephen

--/8JRgMfzn8PY/uxg
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJeoc4CAAoJEO1sijiDR2RVIdIP/iwv4RH/miZ7PIGq69t7jnKj
0h5LiWv2c0AleGa/SjnVBH3eVgtkYOgvKpjkIwAC8S5y0zUId5iiMlff+q0A2lDr
W0gqrizIM9fV/P7rx3KdXqk0PFJTGCn/eepf7nZgmNi/YlON4TIjsG0O1e1hwvkX
p3vGimr/Fup2yU0y6+1dG7z2nhtkJ53Fto7BRSfuicYVWD1MTlAYZN8JMgTQA7Qn
RdqgeuHC07LJcB/zm2+GevvfgPlJbnUixivZL2AUKCd39xXWp3/XMk3MTRS2h+KZ
Q5XCpM/9uuk6a6TFGlMYJvPkl38iirDyAAf5hPZtaUuB8lvO33KZEzhKFeg00un6
71CGVDwz5SsHENBePPDiKxpW4XdT5c7JZGpClBBClMEUXVKGz2XHPU6nXqola/t3
9bUx4nTIorBXDtWjbiPFB5CNO+1M87IbV+R5CRsa/p+2H8Gtgc7NobJccsfq2sIP
OsK7xJOb4h0seBTOJvX3WzEd4olSII+WxTVNzCAV6cmNQrnTirSqrVTxvCV/k/We
phh2tMs6f+47kLzozy+7oI9Mzqmt8mlZCpL3CFVSG61YIlmVZRQDhqIcF6dhf5OV
cVoR//RAlM4SuhSqn3TsvaVjFhnDyp1l3vbHc3qUbzHVjmysp3lFWG3xXOFZn1D3
QLJ91NrhzVKg2VR+KogA
=Tmos
-----END PGP SIGNATURE-----

--/8JRgMfzn8PY/uxg--