Re: index not being used

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(at)useunix(dot)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
>
>
>