Re: PostgreSQL using the wrong Index - Mailing list pgsql-performance

Alex Stapleton <alexs@advfn.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