PostgreSQL using the wrong Index

From: Alex Stapleton <alexs(at)advfn(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL using the wrong Index
Date: 2005-06-13 13:02:30
Message-ID: 2D1A68BF-B3A4-490E-8053-3CF6626B8DAF@advfn.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jona 2005-06-13 13:04:15 How to enhance the chance that data is in disk cache
Previous Message Yves Vindevogel 2005-06-13 12:17:35 Re: Updates on large tables are extremely slow