Sequencial scan over primary keys 
Author Message
 Sequencial scan over primary keys

Quote:

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

--

Sochi, Russia
http://www.***.com/



Tue, 29 Apr 2003 20:45:51 GMT
 Sequencial scan over primary keys

Quote:

> 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



Tue, 29 Apr 2003 23:25:12 GMT
 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

Quote:

> 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



Wed, 30 Apr 2003 02:43:03 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Sequencial scan over primary keys 2

2. No Luck -> Re: [GENERAL] Sequencial scan over primary keys

3. Query for sequencial scan

4. sequencial scans

5. DB2 UDB V7.1 primary key constraint and allow reverse scans

6. using big primary keys v/s small primary keys

7. Diff between Primary key and non primary key select

8. Composite Primary key or 1 primary key column with all values

9. Primary key or no primary key

10. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)

11. Primary Key / Working Code to get primary key of a table using ADO and schemas

12. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)


 
Powered by phpBB® Forum Software