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
