query optimization

marcelo Cortez wrote:

hi guys

i need advice for query optimization,take too long
time.
any coments are welcomed :).

Comment 1 - you'll need to post the output of "EXPLAIN ANALYSE", not
just EXPLAIN, otherwise people can't see how long it took.

Comment 2 - you provide the definition of one table but there are about
20 involved in the query.

Comment 3 - likewise for the size of tables.

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

Comment 5 - you don't show any INDEX definitions, so it's difficult to
know whether they are being used.

Comment 6 - Do you know about the genetic query optimiser and thq
geqo_xxx configuration settings?

HTH - come back with the EXPLAIN ANALYSE etc.

--
Richard Huxton
Archonet Ltd

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