Re: question about seq scan and index scan - Mailing list pgsql-general

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