index not being used

From: lists-pgsql(at)useunix(dot)net
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: index not being used
Date: 2023-08-12 01:15:51
Message-ID: 26113e89-fc44-e0bd-7c4a-b899d5bc591e@useunix.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Erik Brandsberg 2023-08-12 01:47:38 Re: index not being used
Previous Message Guillaume Lelarge 2023-08-09 14:17:56 Re: variable intermédiaires