Обсуждение: Query planning around one key of a multi-column index
I've been using PG now for so many years and I don't have a solid concrete answer on this in my head, and I feel like I should: if I have a table with a multi-column index and I query on one of those columns (or some subset of those columns), will it use that index?
Is there more reading on this topic? I've always felt it's a little more complicated but I'm not quite sure.
Google is your friend:
On Wed, Dec 18, 2019 at 4:43 PM Wells Oliver <wells.oliver@gmail.com> wrote:
I've been using PG now for so many years and I don't have a solid concrete answer on this in my head, and I feel like I should: if I have a table with a multi-column index and I query on one of those columns (or some subset of those columns), will it use that index?Is there more reading on this topic? I've always felt it's a little more complicated but I'm not quite sure.
---------------------------------
Craig A. James
Chief Technology OfficerCraig A. James
3430 Carmel Mountain Road, Suite 250
San Diego, CA 92121
---------------------------------But so are you guys!
Thanks, this is helpful.
Is it really always true that querying off any column that's not the first column of a multi-column index will NOT use that index?
On Wed, Dec 18, 2019 at 7:02 PM Craig James <cjames@emolecules.com> wrote:
Google is your friend:On Wed, Dec 18, 2019 at 4:43 PM Wells Oliver <wells.oliver@gmail.com> wrote:I've been using PG now for so many years and I don't have a solid concrete answer on this in my head, and I feel like I should: if I have a table with a multi-column index and I query on one of those columns (or some subset of those columns), will it use that index?Is there more reading on this topic? I've always felt it's a little more complicated but I'm not quite sure.--eMolecules, Inc.---------------------------------Chief Technology Officer
Craig A. James3430 Carmel Mountain Road, Suite 250San Diego, CA 92121---------------------------------
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Wells Oliver <wells.oliver@gmail.com> writes: > Is it really always true that querying off any column that's not the first > column of a multi-column index will NOT use that index? Nope. The planner will consider that, though in most cases it's likely to estimate that there's no benefit to using the index in such cases. Try reading the *actual* docs, not some third-party site: https://www.postgresql.org/docs/current/indexes-multicolumn.html regards, tom lane
On Wed, Dec 18, 2019 at 11:19 PM Wells Oliver <wells.oliver@gmail.com> wrote:
But so are you guys!Thanks, this is helpful.Is it really always true that querying off any column that's not the first column of a multi-column index will NOT use that index?
Are you talking only about btree indexes? Those are certainly the most common, but other index types exist and have other properties.
Anyway, it is not always true for btree indexes. The planner might decide to use the index as a skinny version of the table, and scan the full index to extract some non-leading column from it. How likely it is to do this will depend mainly on how much smaller the index is than the table, and your relative settings of seq_page_cost and random_page_cost. (Unlike some other products, PostgreSQL will scan the index in logical order, not physical order, even though it doesn't care about the order and this generates a more random IO pattern, because this is the only practical way to protect against concurrent page splits while it scans). Unfortunately, the EXPLAIN output does not make it clear when a index is being used in this way, rather than the more traditional way.
Cheers,
Jeff