Re: Sequencial scan over primary keys

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
>