Re: query optimization

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