Re: any way to use indexscan to get last X values with "order by Y limit X" clause? - Mailing list pgsql-performance

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