pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with the Index
2+ messages / 2 participants
[nested] [flat]
* problem with the Index
@ 2002-10-09 16:56 Jose Antonio Leo <jaleo8@storelandia.com>
2002-10-09 17:31 ` Re: [SQL] problem with the Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jose Antonio Leo @ 2002-10-09 16:56 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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?
tk
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] problem with the Index
2002-10-09 16:56 problem with the Index Jose Antonio Leo <jaleo8@storelandia.com>
@ 2002-10-09 17:31 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2002-10-09 17:31 UTC (permalink / raw)
To: Jose Antonio Leo <jaleo8@storelandia.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-10-09 17:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-09 16:56 problem with the Index Jose Antonio Leo <jaleo8@storelandia.com>
2002-10-09 17:31 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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