Sample Header Ad - 728x90

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