From: | Erik Brandsberg <erik(at)heimdalldata(dot)com> |
---|---|
To: | lists-pgsql(at)useunix(dot)net |
Cc: | pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: index not being used |
Date: | 2023-08-12 01:47:38 |
Message-ID: | CAFcck8HjNuVE33+q0HXhdkTp_c-tPZ0RNAj0pgtauYNgbooP7A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | kunwar singh | 2023-09-10 17:43:18 | How to handle application connectivity change when moving from Oracle to Postgres in GCP |
Previous Message | lists-pgsql | 2023-08-12 01:15:51 | index not being used |