Re: question about seq scan and index scan

Hi Seb,

You omit to say one thing ...

We have an index on tablea on mynumber (integer).
Those tables are vacuumed every nights.

If we do :
explain select count(*) from tablea a, tableb b, tablec c where a.id=b.id and
a.id=c.id and a.mynumber>23000;
NOTICE: QUERY PLAN:

Aggregate (cost=51898.63..51898.63 rows=1 width=48)
-> Hash Join (cost=33905.40..51887.68 rows=4379 width=48)
-> Seq Scan on tableb b (cost=0.00..14391.60 rows=471460 width=16)
-> Hash (cost=33894.45..33894.45 rows=4379 width=32)
-> Hash Join (cost=13522.17..33894.45 rows=4379 width=32)
-> Seq Scan on tablec c (cost=0.00..16781.60
rows=471460 width=16)
-> Hash (cost=13511.23..13511.23 rows=4379 width=16)
-> Seq Scan on tablea a (cost=0.00..13511.23
rows=4379 width=16)

EXPLAIN

And If we do an :
explain select count(*) from tablea a, tableb b, tablec c where a.id=b.id and
a.id=c.id and a.mynumber>23350;
NOTICE: QUERY PLAN:

Aggregate (cost=50522.53..50522.53 rows=1 width=48)
-> Nested Loop (cost=13521.19..50512.57 rows=3987 width=48)
-> Hash Join (cost=13521.19..32709.93 rows=3987 width=32)
-> Seq Scan on tableb b (cost=0.00..16781.60 rows=471460
width=16)
-> Hash (cost=13511.23..13511.23 rows=3987 width=16)
-> Seq Scan on tablea a (cost=0.00..13511.23 rows=3987
width=16)
-> Index Scan using tablec_pkey on tablec c (cost=0.00..4.45 rows=1
width=16)

EXPLAIN

But If I do :
explain select count(*) from tablea a, tableb b, tablec c where a.id=b.id and
a.id=c.id and a.mynumber>24000;
NOTICE: QUERY PLAN:

Aggregate (cost=414.72..414.72 rows=1 width=48)
-> Nested Loop (cost=0.00..414.65 rows=29 width=48)
-> Nested Loop (cost=0.00..284.62 rows=29 width=32)
-> Index Scan using ix_mynumber_key on tablea a
(cost=0.00..116.44 rows=29 width=16)
-> Index Scan using tableb_pkey on tableb b (cost=0.00..5.76
rows=1 width=16)
-> Index Scan using tablec_pkey on tablec c (cost=0.00..4.45 rows=1
width=16)

EXPLAIN

Thanks for you help ... to "explain" us why Postgresql do not use all the
time the index scan for this kind of request for 3 tables with the same
primary key id.

regards,

Le Vendredi 25 Octobre 2002 18:18, Sébastien PALLEAU a écrit :
> 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 primary on
> 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 ?
> thanks for your anwers.

--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Tel. 33-144949901
fax. 33-144949902