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@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
>
>
>