From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mike Sofen <msofen@runbox.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 20:09:00 -0500
Message-ID: <22504.1581901740@sss.pgh.pa.us> (raw)
In-Reply-To: <05fc01d5e52b$3c3d1c50$b4b754f0$@runbox.com>
References: <CAKVFrvFY-f7kgwMRMiPLbPYMmgjc8Y2jjUGK_Y0HVcYAmU6ymg@mail.gmail.com>
<43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at>
<19549.1581867005@sss.pgh.pa.us>
<05fc01d5e52b$3c3d1c50$b4b754f0$@runbox.com>
"Mike Sofen" <msofen@runbox.com> writes:
>> From: Tom Lane <tgl@sss.pgh.pa.us> Sent: Sunday, February 16, 2020 7:30 AM
>>> I've seen people try to do this before. I wonder if the manual page about
>>> partial indexes should explicitly say "don't do that".
> Yes please (seriously). The utter beauty of Postgres is the flexibility and
> power that its evolutionary path has allowed/created. The tragic danger is
> that the beauty is fairly easy to misapply/misuse. Caveats in the
> documentation would be very beneficial to both seasoned practitioners and
> newcomers - it is quite challenging to keep up with everything Postgres and
> the documentation is where most of us turn for guidance.
OK, so how about something like this added to section 11.8
(no pretty markup as yet):
Example 11.4. Do Not use Partial Indexes as a Substitute for Partitioning
You might be tempted to create a large set of non-overlapping partial
indexes, for example
CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;
...
This is a bad idea! Almost certainly, you'll be better off with a single
non-partial index, declared like
CREATE INDEX mytable_cat_data ON mytable (category, data);
(Put the category column first, for the reasons described in section 11.3
Multicolumn Indexes.) While a search in this larger index might have to
descend through a couple more tree levels than a search in a smaller
index, that's almost certainly going to be cheaper than the planner effort
needed to select the appropriate one of the partial indexes. The core of
the problem is that the system does not understand the relationship among
the partial indexes, and will laboriously test each one to see if it's
applicable to the current query.
If your table is large enough that a single index really is a bad idea,
you should look into using partitioning instead (section whatever-it-is).
With that mechanism, the system does understand that the tables and
indexes are non-overlapping, so much better performance is possible.
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, msofen@runbox.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: <22504.1581901740@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