MIME-Version: 1.0
References: <26113e89-fc44-e0bd-7c4a-b899d5bc591e@useunix.net>
In-Reply-To: <26113e89-fc44-e0bd-7c4a-b899d5bc591e@useunix.net>
From: Erik Brandsberg <erik@heimdalldata.com>
Date: Fri, 11 Aug 2023 21:47:38 -0400
Message-ID: 
 <CAFcck8HjNuVE33+q0HXhdkTp_c-tPZ0RNAj0pgtauYNgbooP7A@mail.gmail.com>
Subject: Re: index not being used
To: lists-pgsql@useunix.net
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005c8e740602b0041c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFcck8HjNuVE33%2Bq0HXhdkTp_c-tPZ0RNAj0pgtauYNgbooP7A%40mail.gmail.com>
Precedence: bulk

--0000000000005c8e740602b0041c
Content-Type: text/plain; charset="UTF-8"

If there are only a few values of sat, then a sequential scan may in fact
be the optimal path.

On Fri, Aug 11, 2023, 9:16 PM <lists-pgsql@useunix.net> wrote:

> I'm running an older PostgreSQL 9.1 database. I know it's old... an
> upgrade is planned.
>
> I have a table with the following columns.
>
>   Column |  Type   | Modifiers | Storage  | Description
> --------+---------+-----------+----------+-------------
>   sat    | text    | not null  | extended |
>   ts     | bigint  | not null  | plain    |
>   apid   | integer | not null  | plain    |
>   bin    | integer | not null  | plain    |
>   value  | bigint  | not null  | plain    |
>
> A unique index on (sat, ts, apid, bin).
>
> There are only a handful of unique sat values but there are about 20
> million rows in the table as there are many apid values per unit time.
>
> This query is fast and uses the index:
>
> select max(ts)
> from table
> where sat = 'XX';
>
> While this query results in sequential scans and long execution times:
>
> select sat, max(ts)
> from histograms
> where sat in ('A1', 'A2', 'S1', 'S2')
> group by 1;
>
> Is there any way to formulate this query to make it faster without
> adding an additional index?
>
> Thank you in advance,
> Wayne
>
>
>

--0000000000005c8e740602b0041c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">If there are only a few values of sat, then a sequential =
scan may in fact be the optimal path.</div><br><div class=3D"gmail_quote"><=
div dir=3D"ltr" class=3D"gmail_attr">On Fri, Aug 11, 2023, 9:16 PM  &lt;<a =
href=3D"mailto:lists-pgsql@useunix.net">lists-pgsql@useunix.net</a>&gt; wro=
te:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;b=
order-left:1px #ccc solid;padding-left:1ex">I&#39;m running an older Postgr=
eSQL 9.1 database. I know it&#39;s old... an <br>
upgrade is planned.<br>
<br>
I have a table with the following columns.<br>
<br>
=C2=A0 Column |=C2=A0 Type=C2=A0 =C2=A0| Modifiers | Storage=C2=A0 | Descri=
ption<br>
--------+---------+-----------+----------+-------------<br>
=C2=A0 sat=C2=A0 =C2=A0 | text=C2=A0 =C2=A0 | not null=C2=A0 | extended |<b=
r>
=C2=A0 ts=C2=A0 =C2=A0 =C2=A0| bigint=C2=A0 | not null=C2=A0 | plain=C2=A0 =
=C2=A0 |<br>
=C2=A0 apid=C2=A0 =C2=A0| integer | not null=C2=A0 | plain=C2=A0 =C2=A0 |<b=
r>
=C2=A0 bin=C2=A0 =C2=A0 | integer | not null=C2=A0 | plain=C2=A0 =C2=A0 |<b=
r>
=C2=A0 value=C2=A0 | bigint=C2=A0 | not null=C2=A0 | plain=C2=A0 =C2=A0 |<b=
r>
<br>
A unique index on (sat, ts, apid, bin).<br>
<br>
There are only a handful of unique sat values but there are about 20 <br>
million rows in the table as there are many apid values per unit time.<br>
<br>
This query is fast and uses the index:<br>
<br>
select max(ts)<br>
from table<br>
where sat =3D &#39;XX&#39;;<br>
<br>
While this query results in sequential scans and long execution times:<br>
<br>
select sat, max(ts)<br>
from histograms<br>
where sat in (&#39;A1&#39;, &#39;A2&#39;, &#39;S1&#39;, &#39;S2&#39;)<br>
group by 1;<br>
<br>
Is there any way to formulate this query to make it faster without <br>
adding an additional index?<br>
<br>
Thank you in advance,<br>
Wayne<br>
<br>
<br>
</blockquote></div>

--0000000000005c8e740602b0041c--