Re: PostgreSQL using the wrong Index

Alex Stapleton <alexs(at)advfn(dot)com> writes:
> 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;

This was just covered in excruciating detail yesterday ...

You need to write
order by symbol desc, time desc limit 1
to get the planner to recognize the connection to the sort order
of this index. Since you're only selecting one value of symbol,
the actual output doesn't change.

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

I'll bet lunch that that is a bad selection of random_page_cost,
unless your database is so small that it all fits in RAM.

regards, tom lane