public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erik Brandsberg <[email protected]>
To: [email protected]
Cc: pgsql-sql <[email protected]>
Subject: Re: index not being used
Date: Fri, 11 Aug 2023 21:47:38 -0400
Message-ID: <CAFcck8HjNuVE33+q0HXhdkTp_c-tPZ0RNAj0pgtauYNgbooP7A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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 <[email protected]> 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
>
>
>

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: index not being used
  In-Reply-To: <CAFcck8HjNuVE33+q0HXhdkTp_c-tPZ0RNAj0pgtauYNgbooP7A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox