From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: MingJu Wu <mingjuwu0505@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
Date: Sun, 16 Feb 2020 11:35:43 -0500
Message-ID: <23936.1581870943@sss.pgh.pa.us> (raw)
In-Reply-To: <20200216155919.GI31889@telsasoft.com>
References: <20200216155919.GI31889@telsasoft.com>
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-in-production
Yeah, the critical paragraph in that is
This isn’t 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’s data, so each table has a only a few thousand
indexes at most. Second, these events are relatively rare. The most
common defined events make up only a few percent of a customer’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’t need to be
written to any of the indexes. Similarly, the indexes don’t take 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
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, pryzby@telsasoft.com, laurenz.albe@cybertec.at, mingjuwu0505@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
In-Reply-To: <23936.1581870943@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox