Thread: Sequencial scan over primary keys

Sequencial scan over primary keys

From

"Vilson farias"

Date:

10 November 2000, 10:17:48

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

Sequencial scan over primary keys 2

From

"Vilson farias"

Date:

10 November 2000, 10:44:08

Just another question : Does foreign has a automatic created index, like
primary keys?

----- Original Message -----
From: Vilson farias <vilson.farias@digitro.com.br>
To: <pgsql-general@postgresql.org>
Cc: SIMONE Carla MOSENA <simone.mosena@digitro.com.br>
Sent: Sexta-feira, 10 de Novembro de 2000 10:16
Subject: [GENERAL] Sequencial scan over primary keys


:
: 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
:

Re: Sequencial scan over primary keys

From

Igor Roboul

Date:

10 November 2000, 10:44:18

On Fri, Nov 10, 2000 at 10:16:47AM -0200, Vilson farias wrote:
> result of execution is a sequencial scan, but this is a PRIMARY KEY, it does
> has an index. How can it be explained?
If you wish use index, then you need include ORDER BY clause. AFAIK
two SELECTs on same data without ORDER BY _CAN_ return records in
different order. This is part of relation theory AFAIK. So index is
not used. But, for example, sequencial scan can be faster than indexed
when you do not use WHERE.
So for example, 'SELECT * FROM t1' will be faster then 'SELECT * FROM
t1 ORDER BY primary_key_field'. I think.

--
Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga",
Sochi, Russia
http://www.brainbench.com/transcript.jsp?pid=304744

Re: Sequencial scan over primary keys

From

Tom Lane

Date:

10 November 2000, 13:24:37

"Vilson farias" <vilson.farias@digitro.com.br> writes:
> 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(# );

> 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)

Try it with
  select * from tipo_categoria where cod_categoria = 1::smallint;

An unadorned literal "1" is an int, not a smallint, and the planner is
not currently very smart about indexing cross-datatype comparisons.

Alternatively, just declare your table with column type int.  Because of
alignment requirements for the varchar column, you're not actually
saving any space by using the smallint declaration anyway.

            regards, tom lane

No Luck -> Re: Sequencial scan over primary keys

From

"Vilson farias"

Date:

10 November 2000, 15:57:41

Tom,

  I've tried like you said (::integer) but doesn't work. I tried another
way, using GROUP BY at the end, but no luck again. What now?

            Table "prog_teste"
    Attribute    |     Type     | Modifier
-----------------+--------------+----------
 cod_teste       | integer      | not null
...
Index: xpkprog_teste


Index "xpkprog_teste"
 Attribute |  Type
-----------+---------
 cod_teste | integer
unique btree (primary key)

sitest=# explain select * from prog_teste where cod_teste=90::integer;
NOTICE:  QUERY PLAN:

Seq Scan on prog_teste  (cost=0.00..1.21 rows=1 width=138)

EXPLAIN

sitest=# explain select * from prog_teste where cod_teste=90::integer order
by cod_teste;
NOTICE:  QUERY PLAN:

Sort  (cost=1.22..1.22 rows=1 width=138)
  ->  Seq Scan on prog_teste  (cost=0.00..1.21 rows=1 width=138)


Best regards,

José Vilson de Mello de Farias
Dígitro Tecnologia - Brasil











----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vilson farias <vilson.farias@digitro.com.br>
Cc: <pgsql-general@postgresql.org>; SIMONE Carla MOSENA
<simone.mosena@digitro.com.br>
Sent: Sexta-feira, 10 de Novembro de 2000 13:11
Subject: Re: [GENERAL] Sequencial scan over primary keys


: "Vilson farias" <vilson.farias@digitro.com.br> writes:
: > 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(# );
:
: > 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)
:
: Try it with
:   select * from tipo_categoria where cod_categoria = 1::smallint;
:
: An unadorned literal "1" is an int, not a smallint, and the planner is
: not currently very smart about indexing cross-datatype comparisons.
:
: Alternatively, just declare your table with column type int.  Because of
: alignment requirements for the varchar column, you're not actually
: saving any space by using the smallint declaration anyway.
:
: regards, tom lane
:

Re: Sequencial scan over primary keys

From

Michael Fork

Date:

10 November 2000, 16:42:17

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
>