[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Multi-column index vs index on individual columns
From: Guyren Howe <guyren () gmail ! com>
Date: 2020-12-26 22:15:36
Message-ID: 36104373-38ae-44da-bd2a-680922429632 () Spark
[Download RAW message or body]
In a typical web storefront or catalog app, there are a fairly small number of \
queries that are run often that are more complex and slower. It is perfectly \
appropriate to consider creating compound/partial indexes to speed up those queries. \
Just be aware that indexes trade off slower insert/update time against faster search \
time. I would read use-the-index-luke.com for detailed advice about using compound \
and partial indexes effectively. On Dec 26, 2020, 12:18 -0800, Shantanu Shekhar \
<shekharshan@yahoo.com>, wrote:
> Hi,
>
> I was going through Postgres documentation on multi-column indexes and came across \
> the recommendation at the bottom states: "Multicolumn indexes should be used \
> sparingly. In most situations, an index on a single column is sufficient and saves \
> space and time".
> In my experience typical webapps the queries are known ahead of time and lot of the \
> times the queries are restricted to a single table (no joins). Does this \
> recommendation that still apply in this use-case? I was under the impression that \
> instead of scanning multiple indexes and then combining the results, it may be more \
> performant to simply use a single multi-index column.
> Thanks,
>
> Shantanu
[Attachment #3 (text/html)]
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<div name="messageBodySection">
<div dir="auto">In a typical web storefront or catalog app, there are a fairly small \
number of queries that are run often that are more complex and slower. It is \
perfectly appropriate to consider creating compound/partial indexes to speed up those \
queries. Just be aware that indexes trade off slower insert/update time against \
faster search time. I would read use-the-index-luke.com for detailed advice about \
using compound and partial indexes effectively.</div> </div>
<div name="messageReplySection">On Dec 26, 2020, 12:18 -0800, Shantanu Shekhar \
<shekharshan@yahoo.com>, wrote:<br /> <blockquote type="cite" \
style="border-left-color: grey; border-left-width: thin; border-left-style: solid; \
margin: 5px 5px;padding-left: 10px;"> <div class="yahoo-style-wrap" \
style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px;"> \
<div dir="ltr" data-setdir="false">Hi,</div> <div dir="ltr" data-setdir="false"><br \
/></div> <div dir="ltr" data-setdir="false">I was going through Postgres \
documentation on multi-column indexes and came across the recommendation at the \
bottom states: "<span style="color: rgb(13, 10, 11); font-family: "Open \
Sans", sans-serif; font-size: 14.4px;">Multicolumn indexes should be used \
sparingly. In most situations, an index on a single column is sufficient and saves \
space and time". </span></div> <div dir="ltr" data-setdir="false"><span \
style="color: rgb(13, 10, 11); font-family: "Open Sans", sans-serif; \
font-size: 14.4px;"><br /></span></div> <div dir="ltr" data-setdir="false"><span \
style="color: rgb(13, 10, 11); font-family: "Open Sans", sans-serif; \
font-size: 14.4px;">In my experience typical webapps the queries are known ahead of \
time and lot of the times the queries are restricted to a single table (no joins). \
Does this recommendation that still apply in this use-case? I was under the \
impression that instead of scanning multiple indexes and then combining the results, \
it may be more performant to simply use a single multi-index \
column. </span></div> <div dir="ltr" data-setdir="false"><span style="color: \
rgb(13, 10, 11); font-family: "Open Sans", sans-serif; font-size: \
14.4px;"><br /></span></div> <div dir="ltr" data-setdir="false"><span style="color: \
rgb(13, 10, 11); font-family: "Open Sans", sans-serif; font-size: \
14.4px;">Thanks,</span></div> <div dir="ltr" data-setdir="false"><span style="color: \
rgb(13, 10, 11); font-family: "Open Sans", sans-serif; font-size: \
14.4px;"><br /></span></div> <div dir="ltr" data-setdir="false"><span style="color: \
rgb(13, 10, 11); font-family: "Open Sans", sans-serif; font-size: \
14.4px;">Shantanu</span></div> </div>
</blockquote>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic