We have two index's like so
l1_historical=# \d "N_intra_time_idx"
Index "N_intra_time_idx"
Column | Type
--------+-----------------------------
time | timestamp without time zone
btree
l1_historical=# \d "N_intra_pkey"
Index "N_intra_pkey"
Column | Type
--------+-----------------------------
symbol | text
time | timestamp without time zone
unique btree (primary key)
and on queries like this
select * from "N_intra" where symbol='SOMETHING WHICH DOESNT EXIST'
order by time desc limit 1;
PostgreSQL takes a very long time to complete, as it effectively
scans the entire table, backwards. And the table is huge, about 450
million rows. (btw, there are no triggers or any other exciting
things like that on our tables in this db.)
but on things where the symbol does exist in the table, it's more or
less fine, and nice and fast.
Whilst the option the planner has taken might be faster most of the
time, the worst case scenario is unacceptable for obvious reasons.
I've googled for trying to force the use of a specific index, but
can't find anything relevant. Does anyone have any suggestions on
getting it to use an index which hopefully will have better worst
case performance?