Re: PostgreSQL using the wrong Index

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
> majordomo@postgresql.org
>
>