Re: index not being used

Поиск
Список
Период
Сортировка
От Erik Brandsberg
Тема Re: index not being used
Дата
Msg-id CAFcck8HjNuVE33+q0HXhdkTp_c-tPZ0RNAj0pgtauYNgbooP7A@mail.gmail.com
обсуждение исходный текст
Ответ на index not being used  (lists-pgsql@useunix.net)
Список pgsql-sql
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


В списке pgsql-sql по дате отправления:

Предыдущее
От: lists-pgsql@useunix.net
Дата:
Сообщение: index not being used
Следующее
От: kunwar singh
Дата:
Сообщение: How to handle application connectivity change when moving from Oracle to Postgres in GCP