Oh, we are running 7.4.2 btw. And our random_page_cost = 1

On 13 Jun 2005, at 14:02, Alex Stapleton wrote:

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?

---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to