Re: Optimizer not using index on 120M row table

On Mon, 2003-04-07 at 23:29, Jim C. Nasby wrote:
>       Table "public.email_contrib"
>    Column   |     Type      | Modifiers
> ------------+---------------+-----------
>  id         | integer       | not null
>  team_id    | integer       | not null
>  date       | date          | not null
>  project_id | smallint      | not null
>  work_units | numeric(20,0) | not null
> Indexes: email_contrib_pkey primary key btree (project_id, id, date)

>  explain select * from email_contrib where project_id=8 and id=39622 and
> date='3/1/03';

Since project_id is an int2, you need to add single quotes to the
integer literal or cast it to int2 explicitly:

select * from email_contrib where project_id='8' ...

or

select * from email_contrib where project_id=8::int ...

BTW, this is a (well) known bug -- search the archives for more
information.

> On a related note, will pgsql do 'index covering', reading only the
> index if it contains all the information a query needs?

No -- in PostgreSQL, tuple visibility information is only stored in the
heap, not in the index. So the heap tuple always needs to be examined,
to determine if the specified tuple has been updated/deleted by some
transaction.

Cheers,

Neil