From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Jose Antonio Leo <jaleo8@storelandia.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] problem with the Index
Date: Wed, 9 Oct 2002 10:31:12 -0700 (PDT)
Message-ID: <20021009102800.U4728-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <AEEGKNMMPPBJJDLEJDODEEEMCJAA.jaleo8@storelandia.com>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, jaleo8@storelandia.com, pgsql-general@postgresql.org
Subject: Re: [SQL] problem with the Index
In-Reply-To: <20021009102800.U4728-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox