Re: question about seq scan and index scan

On 25 Oct 2002 at 18:18, Sébastien PALLEAU wrote:

>
> hi,
> I tried to execute the following request on a postgresql 7.2.3 system :
>
> select count(*) from table tablea a, tableb b, tablec c where a.id=b.id and
> a.id=c.id and a.mynumber > 5000;
> tablea, tableb and tablec have primaryon id that identify a unique member.
>
> tablea contains 471413 records tableb contains 471413 records tablec contains
> 471413 records
>
> a.id is tablea primary key
>
>
>
> b.id is tableb primary key c.id is tablec primary key
>
>
>
> An explain on the request provides the following results.
> seq scan on tableb.id (I dont agree)
> seq scan on tablec.id (i dont agree)
> seq scan on tablea.id (seems normal but why executed last ?)
>
> and the most strange is that for the following request :
> explain select count(*) from table tablea a, tableb b, tablec c where a.id=b.id
> and a.id=c.id and a.mynumber > 20000;
> provides :
> index scan using numberpoints_tablea_key
> index scan using tableb_pkey
> index scan using tablec_pkey
>
> why doesn't postgres uses indexes in the first case ?

umm. What happens if you phrase like

a.mynumber > 20000 and a.id=b.id and a.id=c.id

and BTW what's the difference between two queries? I failed to spot any..

Bye
Shridhar

--
Cynic, n.: One who looks through rose-colored glasses with a jaundiced eye.