Re: [SQL] problem with the Index

On Wed, 9 Oct 2002, Jose Antonio Leo wrote:

> I have a problem with the index of 1 table.
>
> I hava a table created :
>     CREATE TABLE "acucliart" (
>    "cod_pto" numeric(8,0) NOT NULL,
>    "cod_cli" varchar(9) NOT NULL,
>    "mes" numeric(2,0) NOT NULL,
>    "ano" numeric(4,0) NOT NULL,
>    "int_art" numeric(5,0) NOT NULL,
>    "cantidad" numeric(12,2),
>    "ven_siv_to" numeric(14,2),
>    "ven_civ_to" numeric(14,2),
>    "tic_siv_to" numeric(14,2),
>    "tic_civ_to" numeric(14,2),
>    "visitas" numeric(2,0),
>    "ult_vis" date,
>    "ven_cos" numeric(12,2),
>    "ven_ofe" numeric(12,2),
>    "cos_ofe" numeric(12,2),
>    CONSTRAINT "acucliart_pkey"
>    PRIMARY KEY ("cod_cli")
> );
>
> if i do this select:
>     explain select * from acucliart where cod_cli=10000;
>         postgres use the index
>         NOTICE:  QUERY PLAN:
>         Index Scan using cod_cli_ukey on acucliart  (cost=0.00..4.82 rows=1
> width=478)
>
> and this select
>         explain select * from acucliart where cod_cli>10000;
>         Postgres don't use the index:
>         NOTICE:  QUERY PLAN:
>         Seq Scan on acucliart  (cost=0.00..22.50 rows=333 width=478)
>
> why?

Well, how many rows are in the table?  In the first case it estimates 1
row will be returned, in the second 333. Index scans are not always faster
than sequential scans as the percentage of the table to scan becomes
larger.  If you haven't analyzed recently, you probably should do so and
if you want to compare, set enable_seqscan=off and try an explain there
and see what it gives you.

Also, why are you comparing a varchar(9) column with an integer?