Re: query optimization - Mailing list pgsql-general

hi guys , Richad

 you are right!, my apologies

profile data:
postgres:  "PostgreSQL 7.4.3 on i386-pc-linux-gnu,
compiled by GCC 2.95.4"
HP proliant
processor: Dual Xeon 3.40 Ghz.
HD:        250 Gigabytes hard disk storage.


responses:
 > Comment 4 - you apply a LIMIT without an ORDER BY.
> Are you sure this is
> what you want?

 yes i think is to truncate result size on the client
side , one message is send to user.

Comment 6 - Do you know about the genetic query
> optimiser and thq
> geqo_xxx configuration settings?
question about this ,my posgresl.conf is

# - Genetic Query Optimizer -

#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0             # default based on
tables in statement,
                                # range 128-1024
#geqo_selection_bias = 2.0      # range 1.5-2.0
is 11 join tables the threshold for activate Genetic
Query Optimizer?



> Comment 5 - you don't show any INDEX definitions, so
> it's difficult to
> know whether they are being used.
 ok there is too much data sorry.


table repartit 8000 rows aprox.


CREATE TABLE repartit
(
  id_reparticion int4 NOT NULL DEFAULT
nextval('reparticiones'::text),
  codigo_reparticion char(9) NOT NULL,
  codigo_repar_inter char(9),
  nombre_reparticion char(60),
  vigencia_desde date NOT NULL,
  vigencia_hasta date NOT NULL,
  id_calle_repar int4 NOT NULL,
  numero char(10) NOT NULL,
  piso char(10),
  oficina char(10),
  telefono char(30),
  fax char(30),
  email char(30),
  codigo_estructura numeric(2) NOT NULL,
  repart_presentismo char(16),
  id_reparticion_ext int4,
  proximo_remito numeric(6) NOT NULL DEFAULT 0,
  en_red char(1) NOT NULL DEFAULT ''::bpchar,
  sector_mesa char(1) NOT NULL DEFAULT ''::bpchar,
  CONSTRAINT pk_repartit PRIMARY KEY (id_reparticion),
  CONSTRAINT fx_callest FOREIGN KEY (id_calle_repar)
      REFERENCES callest (id_calle) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_estructura FOREIGN KEY
(codigo_estructura)
      REFERENCES estructt (codigo_estructura) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT ix1_repartit UNIQUE (codigo_reparticion,
codigo_repar_inter)
)
WITHOUT OIDS;
ALTER TABLE repartit OWNER TO postgres;
GRANT ALL ON TABLE repartit TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE repartit TO GROUP devel;

table pasest huge one 20 millions of records


cREATE TABLE pasest
(
  act_principal char(24) NOT NULL,
  fecha_inicio timestamp NOT NULL,
  act_incorporada char(24) NOT NULL,
  codigo_incorporado char(1) NOT NULL,
  fecha_fin timestamp,
  id_repart_origen int4 NOT NULL,
  id_repart_destino int4 NOT NULL,
  fojas numeric(4) NOT NULL,
  recibo_suelto char(1),
  ficha_tramite numeric(6),
  numer_remito int4,
  id_reparticion_u int4 NOT NULL,
  observaciones1 varchar(250),
  observaciones2 varchar(250),
  cod_permanencia char(2),
  estado_pase char(1),
  paq_actua_anterior char(1) NOT NULL DEFAULT
''::bpchar,
  actua_caratulacion char(1) NOT NULL DEFAULT
''::bpchar,
  param_01 char(1) NOT NULL DEFAULT ''::bpchar,
  param_02 char(1) NOT NULL DEFAULT ''::bpchar,
  param_03 char(1) NOT NULL DEFAULT ''::bpchar,
  fts_observaciones tsvector,
  comp_ano int4,
  comp_nro int4,
  comp_imp float4,
  cuerpos_anexos varchar,
  orden_pago varchar,
  comp_tipo varchar,
  CONSTRAINT pk_pasest PRIMARY KEY (act_principal,
fecha_inicio, act_incorporada),
  CONSTRAINT fk_permanencia FOREIGN KEY
(cod_permanencia)
      REFERENCES permanet (codigo_permanencia) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_caratult FOREIGN KEY (act_principal)
      REFERENCES caratult (actuacion_car) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_reparticion_destino FOREIGN KEY
(id_repart_destino)
      REFERENCES repartit (id_reparticion) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_reparticion_id_reparticion_u FOREIGN
KEY (id_reparticion_u)
      REFERENCES repartit (id_reparticion) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_reparticion_origen FOREIGN KEY
(id_repart_origen)
      REFERENCES repartit (id_reparticion) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE pasest OWNER TO postgres;
GRANT ALL ON TABLE pasest TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE pasest TO GROUP devel;

indexes

"CREATE INDEX ix2_pasest ON pasest USING btree
(act_incorporada)"
"CREATE INDEX ix3_pasest ON pasest USING btree
(id_repart_origen, numer_remito, fecha_inicio)"
"CREATE INDEX ix4_pasest ON pasest USING btree
(id_repart_destino, fecha_fin)"
"CREATE INDEX ix5_pasest ON pasest USING btree
(id_repart_origen, fecha_inicio)"
"CREATE INDEX ix6_pasest ON pasest USING btree
(cod_permanencia)"
"CREATE INDEX ix7_pasest ON pasest USING btree
(id_reparticion_u)"
"CREATE INDEX ix8_pasest ON pasest USING btree
(numer_remito)"
"CREATE INDEX ix9_pasest ON pasest USING btree
(fecha_inicio)"
"CREATE INDEX ix10_fts_observaciones ON pasest USING
gist (fts_observaciones)"
"CREATE INDEX idx_act_principal_letra ON pasest USING
btree (letra((act_principal)::text))"
"CREATE INDEX idx_act_principal_anio ON pasest USING
btree (anio((act_principal)::text))"
"CREATE INDEX idx_act_principal_secuencia ON pasest
USING btree (secuencia((act_principal)::text))"
"CREATE INDEX idx_act_principal_numero ON pasest USING
btree (numero((act_principal)::text))"
"CREATE INDEX idx_act_principal_reparticion ON pasest
USING btree (repart((act_principal)::text))"
"CREATE INDEX idx_act_principal_all ON pasest USING
btree (letra((act_principal)::text),
anio((act_principal)::text),
numero((act_principal)::text),
secuencia((act_principal)::text),
repart((act_principal)::text))"
"CREATE UNIQUE INDEX pk_pasest ON pasest USING btree
(act_principal, fecha_inicio, act_incorporada)"



table caratult

OP TABLE caratult;

CREATE TABLE caratult 6 millions of records

(
  actuacion_car char(24) NOT NULL,
  id_reparticion_uc int4 NOT NULL,
  fecha_inicio timestamp NOT NULL,
  tipo_actuacion char(1) NOT NULL,
  id_extracto_car int4,
  act_extramunicipal char(35),
  observaciones varchar(250),
  comentario1 varchar(250) NOT NULL,
  comentario2 varchar(250),
  comentario3 varchar(250),
  si_calle char(1) NOT NULL,
  verdadera char(1) NOT NULL,
  orden_pago char(10),
  fac_tipo char(2),
  fac_anio numeric(4),
  fac_nro numeric(8),
  fac_importe numeric(13),
  anexos varchar(50),
  recibo_suelto char(1) NOT NULL,
  id_actuacion_car int4 NOT NULL,
  id_reparticion_car int4 NOT NULL,
  id_secuencia_car int4 NOT NULL,
  fecha_inicio_real date NOT NULL,
  fts_comentario tsvector,
  fts_observaciones tsvector,
  CONSTRAINT pk_caratult PRIMARY KEY (actuacion_car),
  CONSTRAINT fx_actuacit FOREIGN KEY
(id_actuacion_car)
      REFERENCES actuacit (id_actuacion) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_extracto FOREIGN KEY (id_extracto_car)
      REFERENCES extractt (id_extracto) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_reparticion_car FOREIGN KEY
(id_reparticion_car)
      REFERENCES repartit (id_reparticion) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_reparticion_uc FOREIGN KEY
(id_reparticion_uc)
      REFERENCES repartit (id_reparticion) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_secuencia1 FOREIGN KEY
(id_secuencia_car)
      REFERENCES secuenct (id_secuencia) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE caratult OWNER TO postgres;
GRANT ALL ON TABLE caratult TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE caratult TO GROUP devel;

indexes:
"CREATE INDEX ix2_caratult ON caratult USING btree
(id_reparticion_uc)"
"CREATE INDEX ix3_caratult ON caratult USING btree
(id_extracto_car)"
"CREATE INDEX ix4_caratult ON caratult USING btree
(id_reparticion_car)"
"CREATE INDEX ix5_caratult ON caratult USING btree
(verdadera, tipo_actuacion)"
"CREATE INDEX ix6_caratult ON caratult USING btree
(fecha_inicio_real, actuacion_car)"
"CREATE INDEX ix7_caratult ON caratult USING btree
(id_actuacion_car)"
"CREATE INDEX ix8_caratult ON caratult USING btree
(orden_pago)"
"CREATE INDEX ix9_caratult ON caratult USING btree
(fac_tipo, fac_anio, fac_nro)"
"CREATE INDEX ix_fts_comentario ON caratult USING gist
(fts_comentario)"
"CREATE INDEX ""IX10_caratult"" ON caratult USING
btree (actuacion_car)"
"CREATE INDEX ix_fts_observaciones ON caratult USING
gist (fts_observaciones)"
"CREATE INDEX actuacion_car_all ON caratult USING
btree (letra((actuacion_car)::text),
anio((actuacion_car)::text),
numero((actuacion_car)::text),
secuencia((actuacion_car)::text),
repart((actuacion_car)::text))"
"CREATE UNIQUE INDEX pk_caratult ON caratult USING
btree (actuacion_car)"
"CREATE INDEX fki_fx_secuencia1 ON caratult USING
btree (id_secuencia_car)"
"CREATE INDEX ix1_caratult ON caratult USING btree
(actuacion_car)"
"CREATE INDEX ix11_caratult ON caratult USING btree
(id_extracto_car)"


table carcalt 400.000 records

CREATE TABLE carcallt
(
  actuacion_cal char(24) NOT NULL,
  id_calle_cal int4 NOT NULL,
  numero char(10),
  edificio char(10),
  torre char(10),
  piso char(10),
  departamento char(10),
  escalera char(10),
  id_calle1_cal int4,
  id_calle2_cal int4,
  id_esquina_cal int4,
  altura char(10),
  parcela char(12),
  partida numeric(7),
  CONSTRAINT pk_carcallt PRIMARY KEY (actuacion_cal),
  CONSTRAINT fx_calle0 FOREIGN KEY (id_calle_cal)
      REFERENCES callest (id_calle) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_calle1 FOREIGN KEY (id_calle1_cal)
      REFERENCES callest (id_calle) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_calle3 FOREIGN KEY (id_esquina_cal)
      REFERENCES callest (id_calle) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_caratult FOREIGN KEY (actuacion_cal)
      REFERENCES caratult (actuacion_car) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT id_calle2 FOREIGN KEY (id_calle2_cal)
      REFERENCES callest (id_calle) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE carcallt OWNER TO postgres;
GRANT ALL ON TABLE carcallt TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE carcallt TO GROUP devel;

indexes

"CREATE INDEX ix2_carcallt ON carcallt USING btree
(id_calle_cal)"
"CREATE INDEX ix3_carcallt ON carcallt USING btree
(id_calle1_cal)"
"CREATE INDEX ix4_carcallt ON carcallt USING btree
(id_calle2_cal)"
"CREATE INDEX ix5_carcallt ON carcallt USING btree
(id_esquina_cal)"
"CREATE UNIQUE INDEX pk_carcallt ON carcallt USING
btree (actuacion_cal)"
 best
  MDC






__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar