From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>,
        MingJu Wu <mingjuwu0505@gmail.com>,
        pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
In-reply-to: <20200216155919.GI31889@telsasoft.com>
References: <20200216155919.GI31889@telsasoft.com>
Comments: In-reply-to Justin Pryzby <pryzby@telsasoft.com>
	message dated "Sun, 16 Feb 2020 09:59:19 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <23935.1581870943.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 16 Feb 2020 11:35:43 -0500
Message-ID: <23936.1581870943@sss.pgh.pa.us>
Precedence: bulk

Justin Pryzby <pryzby@telsasoft.com> writes:
> I was reminded of reading this, but I think it's a pretty different case=
.
> https://heap.io/blog/engineering/running-10-million-postgresql-indexes-i=
n-production

Yeah, the critical paragraph in that is =


    This isn=E2=80=99t as scary as it sounds for a two main reasons. First=
, we
    shard all of our data by customer. Each table in our database holds
    only one customer=E2=80=99s data, so each table has a only a few thous=
and
    indexes at most. Second, these events are relatively rare. The most
    common defined events make up only a few percent of a customer=E2=80=99=
s raw
    events, and most are much more rare. This means that we perform
    relatively little I/O maintaining this schema, because most incoming
    events match no event definitions and therefore don=E2=80=99t need to =
be
    written to any of the indexes. Similarly, the indexes don=E2=80=99t ta=
ke up
    much space on disk.

A set of partial indexes that cover a small part of the total data
can be sensible.  If you're trying to cover most/all of the data,
you're doing it wrong --- basically, you're reinventing partitioning
using the wrong tools.

			regards, tom lane