Обсуждение: help with query...

Folks

I am confused  ,
way planer it does not use the partial index?
query:

  SELECT cliente_base.* FROM cliente_base
 WHERE (cliente_base.inst_class_ = 'Cliente' ) ORDER
BY cliente_base.nombre ASC


plan:

 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Sort  (cost=821.08..837.04 rows=6387 width=378)
(actual time=46.809..53.077 rows=6463 loops=1)
   Sort Key: nombre
   ->  Seq Scan on cliente_base  (cost=0.00..417.39
rows=6387 width=378) (actual time=0.033..19.080
rows=6463 loops=1)
         Filter: ((inst_class_)::text =
'Cliente'::text)
 Total runtime: 58.280 ms
(5 rows)




any help be appreciated

pd: sorry for my english.

data:
table definition:
-- Table: cliente_base

CREATE TABLE cliente_base
(
  id_ integer NOT NULL,
  activo boolean,
  numero integer,
  categ character varying(1),
  nombre character varying(40),
  renglon_1 character varying(40),
  renglon_2 character varying(25),
  renglon_3 character varying(15),
  pobox character varying(7),
  pais integer,
  estado character varying(2),
  att character varying(15),
  telefono_1 character varying(15),
  telefono_2 character varying(15),
  telex_1 character varying(10),
  web character varying(254),
  dominio character varying(30),
  email character varying(255),
  telecop character varying(15),
  tarifa character varying(1),
  doc_clase character varying(2),
  doc_nro character varying(8),
  caja_cod character varying(4),
  caja_nro character varying(10),
  fecha date,
  soc_nro integer,
  ganancia character varying(11),
  iva character varying(15),
  folio character varying(3),
  libro character varying(2),
  tomo character varying(2),
  reg_ind character varying(20),
  cuit character varying(15),
  carpeta_sn character varying(1),
  version_ integer,
  inst_class_ character varying(128),
  CONSTRAINT cliente_base_pkey PRIMARY KEY (id_)
)
WITHOUT OIDS;
ALTER TABLE cliente_base OWNER TO postgres;


-- Index: i_cliente

-- DROP INDEX i_cliente;

CREATE INDEX i_cliente
  ON cliente_base
  USING btree
  (activo);

-- Index: ipartialagente

-- DROP INDEX ipartialagente;

CREATE INDEX ipartialagente
  ON cliente_base
  USING btree
  (numero)
  WHERE inst_class_::text = 'Agente'::text AND activo
= true;

-- Index: ipartialcliente

-- DROP INDEX ipartialcliente;

CREATE INDEX ipartialcliente
  ON cliente_base
  USING btree
  (numero)
  WHERE inst_class_::text = 'Cliente'::text AND activo
= true;

-- Index: ixclientebase

-- DROP INDEX ixclientebase;

CREATE INDEX ixclientebase
  ON cliente_base
  USING btree
  (nombre)
  WHERE inst_class_::text = 'Agente'::text;

-- Index: ixclientebase1

-- DROP INDEX ixclientebase1;

CREATE INDEX ixclientebase1
  ON cliente_base
  USING btree
  (nombre)
  WHERE inst_class_::text = 'Cliente'::text;

-- Index: ixclientebase2

-- DROP INDEX ixclientebase2;

CREATE INDEX ixclientebase2
  ON cliente_base
  USING btree
  (id_)
  WHERE inst_class_::text = 'Cliente'::text;

-- Index: ixmnombre

-- DROP INDEX ixmnombre;

CREATE INDEX ixmnombre
  ON cliente_base
  USING btree
  (activo, nombre);

-- Index: ixmnumero

-- DROP INDEX ixmnumero;

CREATE INDEX ixmnumero
  ON cliente_base
  USING btree
  (activo, numero);




For the record , i made some queries with statistics
proppuses

 select count(*) from cliente_base;
 count
-------
 11791
(1 row)


 select distinct activo  , count(*) from cliente_base
group by activo ;
 activo | count
--------+-------
 f      |   310
 t      | 11481
(2 rows)


select distinct cliente_base.inst_class_ , count(*)
from cliente_base group by cliente_base.inst_class_
postgres-# ;
 inst_class_ | count
-------------+-------
 Agente      |  5328
 Cliente     |  6463
(2 rows)


      __________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

marcelo Cortez <jmdc_marcelo@yahoo.com.ar> writes:
> I am confused  ,
> way planer it does not use the partial index?
> query:

>   SELECT cliente_base.* FROM cliente_base
>  WHERE (cliente_base.inst_class_ = 'Cliente' ) ORDER
> BY cliente_base.nombre ASC

Because it thinks the seqscan is cheaper.  It might be right, depending
on how selective the index predicate is and how close the index order
matches the physical heap order.  Have you tried forcing an indexscan
(eg, by setting enable_sort = off) and comparing estimated and actual
costs for that case?

            regards, tom lane