public inbox for [email protected]
help / color / mirror / Atom feedindex not being used
2+ messages / 2 participants
[nested] [flat]
* index not being used
@ 2023-08-12 01:15 [email protected]
2023-08-12 01:47 ` Re: index not being used Erik Brandsberg <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: [email protected] @ 2023-08-12 01:15 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: index not being used
2023-08-12 01:15 index not being used [email protected]
@ 2023-08-12 01:47 ` Erik Brandsberg <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Erik Brandsberg @ 2023-08-12 01:47 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-sql <[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
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-08-12 01:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-12 01:15 index not being used [email protected]
2023-08-12 01:47 ` Erik Brandsberg <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox