|
Please find attached schema of
tables and indexes involved. Here is the pre-prod. environment config file: listen_addresses = '*' max_connections = 200 shared_buffers = 1024MB work_mem = 24MB maintenance_work_mem = 128MB max_stack_depth = 6MB max_fsm_pages = 204800 wal_buffers = 921MB checkpoint_segments = 10 checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'cp -i %p /postgres/INST1/backup_xlog/%f' enable_seqscan = off random_page_cost = 4.0 effective_cache_size = 1536MB log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_min_duration_statement = 5000 log_duration = on log_line_prefix='%t - user=%u,db=%d,sess=%c,proc=%p ' log_statement = 'all' datestyle = 'iso, dmy' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' The development environment has : listen_addresses = '*' max_connections = 200 shared_buffers = 1024MB work_mem = 24MB maintenance_work_mem = 128MB max_stack_depth = 6MB max_fsm_pages = 204800 wal_buffers = 921MB checkpoint_segments = 10 checkpoint_completion_target = 0.9 enable_seqscan = off random_page_cost = 4.0 effective_cache_size = 1536MB log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 100MB syslog_facility = 'LOCAL0' syslog_ident = 'postgres' silent_mode = on log_duration = on log_line_prefix = '%t - user=%u,db=%d ' log_statement = 'all' datestyle = 'iso, mdy' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' The strange thing is that this morning explain analyze now gives a much better duration : EXPLAIN analyze SELECT DISTINCT ConstantesTableNBienService.id,ConstantesTableNBienService.code,ConstantesTableNBienService.lib_code FROM T_DEMANDE ConstantesTableDemande LEFT OUTER JOIN T_OPERATION ConstantesTableOperation ON ConstantesTableDemande.id_tech = ConstantesTableOperation.id_demande LEFT OUTER JOIN T_BIEN_SERVICE ConstantesTableBienService ON ConstantesTableBienService.id_operation = ConstantesTableOperation.id_tech LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService ON ConstantesTableBienService.bs_code = ConstantesTableNBienService.id WHERE ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A==' AND ConstantesTableOperation.type_operation = 'acq' AND ConstantesTableNBienService.parent is null ORDER BY ConstantesTableNBienService.code ASC; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=3186430.11..3186466.21 rows=205 width=123) (actual time=3.075..3.325 rows=1 loops=1) -> Sort (cost=3186430.11..3186439.13 rows=3610 width=123) (actual time=3.073..3.176 rows=187 loops=1) Sort Key: constantestablenbienservice.code, constantestablenbienservice.id, constantestablenbienservice.lib_code Sort Method: quicksort Memory: 24kB -> Hash Left Join (cost=57.27..3186216.80 rows=3610 width=123) (actual time=0.913..2.795 rows=187 loops=1) Hash Cond: (constantestablebienservice.bs_code = constantestablenbienservice.id) Filter: (constantestablenbienservice.parent IS NULL) -> Nested Loop Left Join (cost=35.39..3186095.62 rows=7221 width=4) (actual time=0.308..1.896 rows=187 loops=1) -> Nested Loop (cost=0.00..5315.38 rows=1315 width=25) (actual time=0.164..0.250 rows=30 loops=1) -> Index Scan using t_demande_pkey on t_demande constantestabledemande (cost=0.00..8.32 rows=1 width=25) (actual time=0.107..0.108 rows=1 loops=1) Index Cond: ((id_tech)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text) -> Index Scan using idx_operation_demande on t_operation constantestableoperation (cost=0.00..5293.91 rows=1315 width=50) (actual time=0.053..0.107 rows=30 loops=1) Index Cond: ((constantestableoperation.id_demande)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text) Filter: ((constantestableoperation.type_operation)::text = 'acq'::text) -> Bitmap Heap Scan on t_bien_service constantestablebienservice (cost=35.39..2409.22 rows=770 width=29) (actual time=0.040..0.044 rows=6 loops=30) Recheck Cond: ((constantestablebienservice.id_operation)::text = (constantestableoperation.id_tech)::text) -> Bitmap Index Scan on idx_bien_service_operation (cost=0.00..35.19 rows=770 width=0) (actual time=0.037..0.037 rows=6 loops=30) Index Cond: ((constantestablebienservice.id_operation)::text = (constantestableoperation.id_tech)::text) -> Hash (cost=19.33..19.33 rows=205 width=127) (actual time=0.566..0.566 rows=205 loops=1) -> Index Scan using n_bien_service_pkey on n_bien_service constantestablenbienservice (cost=0.00..19.33 rows=205 width=127) (actual time=0.045..0.294 rows=205 loops=1) Total runtime: 3.518 ms (21 lignes) There were no modification made on the database except a restart yesterday evening and a vacuumdb --analyse ran at night.
-------- Message original -------- Sujet : Re: Two different execution plan for the same request De : Yeb Havinga <yebhavinga@xxxxxxxxx> Pour : JOUANIN Nicolas (44) <nicolas.jouanin@xxxxxxxxxxxxxxxxxxxxxx> Copie à : pgsql-performance@xxxxxxxxxxxxxx Date : 07/07/2010 10:27 JOUANIN Nicolas (44) wrote: | ||||||||
CREATE TABLE t_demande
(
id_tech character varying(24) NOT NULL,
id_requerant character varying(24),
dem_ref_demande_pays integer,
dem_ref_demande_suite character varying(16),
dem_pays_remb integer,
dem_version character varying(19),
dem_dt_deb_rbt timestamp without time zone,
dem_dt_fin_rbt timestamp without time zone,
dem_iban character varying(35),
dem_bic character varying(25),
dem_nom_titulaire character varying,
dem_type_titulaire character varying(14),
dem_code_monnaie integer,
dem_montant_import double precision,
dem_montant_acqui double precision,
dem_num_4000 character varying,
CONSTRAINT t_demande_pkey PRIMARY KEY (id_tech),
CONSTRAINT fk_code_monnaie_demande FOREIGN KEY (dem_code_monnaie)
REFERENCES n_monnaie (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_pays_remb_demande FOREIGN KEY (dem_pays_remb)
REFERENCES n_code_pays (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_ref_pays_demande FOREIGN KEY (dem_ref_demande_pays)
REFERENCES n_code_pays (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_t_demande_t_fiscalis FOREIGN KEY (id_tech)
REFERENCES t_fiscalis (id_tech) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_t_demande_t_operateur FOREIGN KEY (id_requerant)
REFERENCES t_operateur (id_tech) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
CREATE INDEX idx_dem_ref
ON t_demande
USING btree
(dem_ref_demande_pays, dem_ref_demande_suite);
CREATE INDEX idx_dem_req
ON t_demande
USING btree
(id_requerant);
-- Table: t_operation
CREATE TABLE t_operation
(
id_tech character varying(24) NOT NULL,
id_demande character varying(24) NOT NULL,
id_fournisseur character varying(24),
type_operation character varying(10),
op_num_seq integer NOT NULL,
op_num_ref character varying,
op_dt_delivrance timestamp without time zone NOT NULL,
op_code_monnaie integer,
op_montant_imposable double precision,
op_montant_tva double precision,
op_prorata_deduction double precision,
op_montant_tva_deductible double precision,
op_num_seq_rebeca character varying(50),
imp_info_ref_importation character varying,
acq_facture_simplifiee boolean,
CONSTRAINT t_operation_pkey PRIMARY KEY (id_tech),
CONSTRAINT fk_code_monnaie_operation FOREIGN KEY (op_code_monnaie)
REFERENCES n_monnaie (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_t_operation_t_demande FOREIGN KEY (id_demande)
REFERENCES t_demande (id_tech) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_t_operation_t_operateur FOREIGN KEY (id_fournisseur)
REFERENCES t_operateur (id_tech) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE INDEX idx_op_fournisseur
ON t_operation
USING btree
(id_fournisseur);
CREATE INDEX idx_operation_demande
ON t_operation
USING btree
(id_demande);
-- Table: t_bien_service
CREATE TABLE t_bien_service
(
id_tech character varying(24) NOT NULL,
id_operation character varying(24) NOT NULL,
bs_code integer,
bs_sous_code integer,
bs_description character varying,
bs_code_langue integer,
CONSTRAINT t_bien_service_pkey PRIMARY KEY (id_tech),
CONSTRAINT fk_code_bien_service FOREIGN KEY (bs_code)
REFERENCES n_bien_service (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_code_langue_bien_service FOREIGN KEY (bs_code_langue)
REFERENCES n_langue (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_sous_code_bien_service FOREIGN KEY (bs_sous_code)
REFERENCES n_bien_service (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_t_bien_service_t_operation FOREIGN KEY (id_operation)
REFERENCES t_operation (id_tech) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
CREATE INDEX idx_bien_service_code
ON t_bien_service
USING btree
(bs_code);
CREATE INDEX idx_bien_service_operation
ON t_bien_service
USING btree
(id_operation);
CREATE INDEX idx_bien_service_sous_code
ON t_bien_service
USING btree
(bs_sous_code);
-- Table: n_bien_service
CREATE TABLE n_bien_service
(
id integer NOT NULL,
parent integer,
code character varying(20) NOT NULL,
lib_code character varying(512),
debut_validite timestamp without time zone NOT NULL,
fin_validite timestamp without time zone NOT NULL,
CONSTRAINT n_bien_service_pkey PRIMARY KEY (id),
CONSTRAINT e FOREIGN KEY (parent)
REFERENCES n_bien_service (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
