Re: any way to use indexscan to get last X values with "order by Y limit X" clause?

On 15 Jun 2003 at 16:26, Tomaz Borstnar wrote:

>
> Here is the structure:
<snip>
> approved | character(1) | not null default 'N'
> msgid | character(100) | not null default ''
> modifystamp | integer | not null default '0'
> userid | integer | not null default '0'
> closed | smallint | default '0'
> Indexes: tjavendanpri_key primary key btree (id),
> tjavendan_approved btree (approved),

<snip>

> Here is the query:
> SELECT thread, modifystamp, count(id) AS tcount, abstime(modifystamp) AS
> latest, max(id) as maxid FROM tjavendan WHERE approved='Y' GROUP BY
> thread, modifystamp ORDER BY modifystamp desc, thread desc limit 40

Question. The field approved seems to have boolean values. If probability of
having either of value is 50%, I doubt planner will use index anyway.

Even assuming all possible values of a char variable, the choice isn't too
much, say if you have 1M row.

Correct me if I am wrong.

Bye
Shridhar

--
Either one of us, by himself, is expendable. Both of us are not. -- Kirk,
"The Devil in the Dark", stardate 3196.1