Re: Sequencial scan over primary keys - Mailing list pgsql-general

Just a few suggestions

(1) Make sure you have run the VACUUM ANALYZE command on the table
(i.e. VACUUM ANALYZE tipo_categoria)

(2) Try running the following 3 commands, and comparing the total costs to
see which is cheaper (an index scan is *not* always best).  If the
sequential scan is cheaper, then it should be the fastest, and vice versa.

explain select * from tipo_categoria where cod_categoria = 1;
set enableseqscan=off;
explain select * from tipo_categoria where cod_categoria = 1;

Here is example output

radius=# explain select * from tiacct where ti_username = 'admin';
NOTICE:   QUERY PLAN:
Seq Scan on tiacct (cost=0.00..178.70 rows=96 width=44)
                               ^^^^^^
EXPLAIN
radius=# set enable_seqscan=off;
SET VARIABLE
radius=# explain select * from tiacct where ti_username = 'admin';
NOTICE:  QUERY PLAN:
Index Scan using idx_tiacct on tiacct (cost=0.00..253.88 rows=96 width=44
                                                  ^^^^^^
In this situation Postgres will use the seq scan, rather than the index
scan, due to its cheaper cost.

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Fri, 10 Nov 2000, Vilson farias wrote:

>
> Hello,
>
>     I need help in case below. My table tipo_categoria has a primary key,
> called cod_categoria When I use this key as parameter for my sql script, the
> result of execution is a sequencial scan, but this is a PRIMARY KEY, it does
> has an index. How can it be explained?
>
>
>
> sitest=# CREATE TABLE tipo_categoria (
> sitest(#        cod_categoria        smallint NOT NULL,
> sitest(#        descricao            varchar(40),
> sitest(#        CONSTRAINT XPKtipo_categoria PRIMARY KEY (cod_categoria)
> sitest(#
> sitest(# );
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
> 'xpktipo_categoria' for table 'tipo_categoria'
> CREATE
> sitest=# copy tipo_categoria from '/home/postgres/categ.txt';
> COPY
> sitest=# explain select * from tipo_categoria where cod_categoria = 1;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on tipo_categoria  (cost=0.00..22.50 rows=10 width=14)
>
> EXPLAIN
> sitest=# \di
>            List of relations
>         Name        | Type  |  Owner
> --------------------+-------+----------
> ...
>  xpktipo_categoria  | index | postgres
> ...
> (26 rows)
> sitest=# select * from tipo_categoria;
>  cod_categoria |               descricao
> ---------------+---------------------------------------
>              0 | Categoria chamador desconhecida
>              1 | Reserva
>              2 | Reserva
> ..
>            224 | Assinante com tarifacao especial
>            226 | Telefone publico interurbano
> (20 rows)
>
>
> Thanks.
>
> Jos� Vilson de Mello de Farias
> Digitro Tecnologia Ltda - Brasil
>