pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery planning around one key of a multi-column index
5+ messages / 4 participants
[nested] [flat]
* Query planning around one key of a multi-column index
@ 2019-12-19 00:42 Wells Oliver <wells.oliver@gmail.com>
2019-12-19 03:02 ` Re: Query planning around one key of a multi-column index Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 5+ messages in thread
From: Wells Oliver @ 2019-12-19 00:42 UTC (permalink / raw)
To: pgsql-admin
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.
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query planning around one key of a multi-column index
2019-12-19 00:42 Query planning around one key of a multi-column index Wells Oliver <wells.oliver@gmail.com>
@ 2019-12-19 03:02 ` Craig James <cjames@emolecules.com>
2019-12-19 04:18 ` Re: Query planning around one key of a multi-column index Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Craig James @ 2019-12-19 03:02 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Google is your friend:
http://www.postgresqltutorial.com/postgresql-indexes/postgresql-multicolumn-indexes/
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.
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
3430 Carmel Mountain Road, Suite 250
San Diego, CA 92121
---------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query planning around one key of a multi-column index
2019-12-19 00:42 Query planning around one key of a multi-column index Wells Oliver <wells.oliver@gmail.com>
2019-12-19 03:02 ` Re: Query planning around one key of a multi-column index Craig James <cjames@emolecules.com>
@ 2019-12-19 04:18 ` Wells Oliver <wells.oliver@gmail.com>
2019-12-19 05:47 ` Re: Query planning around one key of a multi-column index Tom Lane <tgl@sss.pgh.pa.us>
2019-12-19 13:55 ` Re: Query planning around one key of a multi-column index Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Wells Oliver @ 2019-12-19 04:18 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-admin
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:
>
> http://www.postgresqltutorial.com/postgresql-indexes/postgresql-multicolumn-indexes/
>
> 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.
>>
>> --
>> Wells Oliver
>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>
>
>
> --
> ---------------------------------
> Craig A. James
> Chief Technology Officer
> eMolecules, Inc.
> 3430 Carmel Mountain Road, Suite 250
> San Diego, CA 92121
> ---------------------------------
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query planning around one key of a multi-column index
2019-12-19 00:42 Query planning around one key of a multi-column index Wells Oliver <wells.oliver@gmail.com>
2019-12-19 03:02 ` Re: Query planning around one key of a multi-column index Craig James <cjames@emolecules.com>
2019-12-19 04:18 ` Re: Query planning around one key of a multi-column index Wells Oliver <wells.oliver@gmail.com>
@ 2019-12-19 05:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2019-12-19 05:47 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Craig James <cjames@emolecules.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query planning around one key of a multi-column index
2019-12-19 00:42 Query planning around one key of a multi-column index Wells Oliver <wells.oliver@gmail.com>
2019-12-19 03:02 ` Re: Query planning around one key of a multi-column index Craig James <cjames@emolecules.com>
2019-12-19 04:18 ` Re: Query planning around one key of a multi-column index Wells Oliver <wells.oliver@gmail.com>
@ 2019-12-19 13:55 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jeff Janes @ 2019-12-19 13:55 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Craig James <cjames@emolecules.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-12-19 13:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-19 00:42 Query planning around one key of a multi-column index Wells Oliver <wells.oliver@gmail.com>
2019-12-19 03:02 ` Craig James <cjames@emolecules.com>
2019-12-19 04:18 ` Wells Oliver <wells.oliver@gmail.com>
2019-12-19 05:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-12-19 13:55 ` Jeff Janes <jeff.janes@gmail.com>
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