[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: Single column vs composite partial index
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2020-09-18 20:54:04
Message-ID: CAHOFxGqpPij+tC4qOO6ijJjax_PtYmQuJ_0T=Z-WpqYE9gS51Q () mail ! gmail ! com
[Download RAW message or body]

Index Cond: ((load_dttm)::date >= (CURRENT_DATE - 7))"

There is no need to cast the load_dttm field to a date in the query. The
plain index on the field would be usable if you skipped that. In your
example, you show creating the single column index but it isn't getting
used because of the type cast. The second index is both partial, and
multi-column. If your data statistics show that ((actv_code)::text = ANY
('{NAC,CAN,RSP,RCL}'::text[])) only 1% of the time, then it would certainly
be helpful to have a partial index if those are the rows you want to find
often and do so quickly. If the rows with those values for actv_code is
more like 75% of the total rows, then there'd be no reason to make it
partial IMO.

If you are often/constantly querying for only the last 7-7.999 days of data
based on load_dttm, I would put that as the first column of the index since
then you would be scanning a contiguous part rather than scanning 3
different parts of the composite index where actv_code = each of those
three values, and then finding the rows that are recent based on the
timestamp(tz?) field.

[Attachment #3 (text/html)]

<div dir="ltr">Index Cond: ((load_dttm)::date &gt;= (CURRENT_DATE - \
7))&quot;<br><div><span style="color:rgb(80,0,80);font-family:&quot;Helvetica \
Neue&quot;,Helvetica,Arial,sans-serif;font-size:16px"><br></span></div><div>There is \
no need to cast the load_dttm field to a date in the query. The plain index on the \
field would be usable if you skipped that. In your example, you show creating the \
single column index but it isn&#39;t getting used because of the type cast. The \
second index is both partial, and multi-column. If your data statistics show that  \
((actv_code)::text = ANY (&#39;{NAC,CAN,RSP,RCL}&#39;::text[])) only 1% of the time, \
then it would certainly be helpful to have a partial index if those are the rows you \
want to find often and do so quickly. If the rows with those values for  actv_code is \
more like 75% of the total rows, then there&#39;d be no reason to make  it partial \
IMO.<br></div><br>If you are often/constantly querying for only the last 7-7.999 days \
of data based on  load_dttm, I would put that as the first column of the index since \
then you would be scanning a contiguous part rather than scanning 3 different parts \
of the  composite index where actv_code = each of those three values, and then \
finding the rows that are recent based on the timestamp(tz?) field.</div>



[prev in list] [next in list] [prev in thread] [next in thread]