Postgres index not used when select includes timestamp::text conversion
6
votes
2
answers
388
views
I have the following table (in PostgreSQL 14.6):
create table waste_trajectory
(
id uuid default uuid_generate_v4() not null primary key,
observation_id uuid not null,
tank_id varchar(30),
stored_on timestamp with time zone default now(),
score numeric(14, 10)
);
... with this index:
CREATE INDEX IF NOT EXISTS idx_wt_stored_on_desc
ON waste_trajectory (stored_on desc);
The table contains a large amount of data. If I run the following query:
SELECT
id,
observation_id,
tank_id,
-- stored_on::text,
score
FROM waste_trajectory
ORDER BY stored_on DESC
LIMIT 1;
I get results in a reasonable amount of time:
[2025-05-12 14:02:46] completed in 969 ms
But if I un-comment that stored_on::text
line, the query takes over 10 minutes:
[2025-05-12 14:14:39] completed in 11 m 39 s 250 ms
The EXPLAIN ANALYSE
for the un-commented version of the query shows:
|Limit (cost=24469520.63..24469520.75 rows=1 width=1035) (actual time=736266.363..736286.177 rows=1 loops=1) |
| -> Gather Merge (cost=24469520.63..46550572.58 rows=184416572 width=1035) (actual time=736211.570..736231.382 rows=1 loops=1) |
| Workers Planned: 4 |
| Workers Launched: 4 |
| -> Sort (cost=24468520.57..24583780.93 rows=46104143 width=1035) (actual time=736147.303..736147.305 rows=1 loops=5) |
| Sort Key: ((stored_on)::text) DESC |
| Sort Method: top-N heapsort Memory: 31kB |
| Worker 0: Sort Method: top-N heapsort Memory: 26kB |
| Worker 1: Sort Method: top-N heapsort Memory: 26kB |
| Worker 2: Sort Method: top-N heapsort Memory: 28kB |
| Worker 3: Sort Method: top-N heapsort Memory: 26kB |
| -> Parallel Seq Scan on waste_trajectory (cost=0.00..24237999.86 rows=46104143 width=1035) (actual time=1032.453..712735.895 rows=36882681 loops=5)|
|Planning Time: 0.104 ms |
|JIT: |
| Functions: 11 |
| Options: Inlining true, Optimization true, Expressions true, Deforming true |
| Timing: Generation 2.080 ms, Inlining 397.283 ms, Optimization 186.396 ms, Emission 124.416 ms, Total 710.175 ms |
|Execution Time: 736286.614 ms
Unfortunately, I don't have the liberty of changing the query, since it's in a component I don't control. Is there a way to handle this strictly using a different index?
I tried:
CREATE INDEX IF NOT EXISTS idx_wt_stored_on_desc_text
ON waste_trajectory (cast(stored_on as text) desc);
... but this returns:
[42P17] ERROR: functions in index expression must be marked IMMUTABLE
Can I improve the performance of this query without resorting to a custom IMMUTABLE function for converting the timestamp to text?
Asked by 6006604
(173 rep)
May 12, 2025, 08:04 PM
Last activity: Jun 2, 2025, 01:04 PM
Last activity: Jun 2, 2025, 01:04 PM