Re: [PERFORM] Two different execution plan for the same request
It seems to work fine (same execution plan and less duration) after : - setting default_statistics_target to 100 - full vacuum with analyze - reindexdb Thanks. Message original Sujet : Re: [PERFORM] Two different execution plan for the same request De : Guillaume Smet Pour : JOUANIN Nicolas (44) Copie à : Yeb Havinga , pgsql-performance@postgresql.org Date : 07/07/2010 10:59 Hi Nicolas, On Wed, Jul 7, 2010 at 10:47 AM, JOUANIN Nicolas (44) wrote: There were no modification made on the database except a restart yesterday evening and a vacuumdb --analyse ran at night. It's not really surprising considering you probably kept the default_statistics_target to 10 (it's the default in 8.3). Consider raising it to 100 in your postgresql.conf (100 is the default for newer versions), then reload, and run a new ANALYZE. You might need to set it higher on specific columns if you have a lot of data and your data distribution is weird. And, btw, please upgrade to the latest 8.3.x. HTH
Re: [PERFORM] Two different execution plan for the same request
s=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.
Nicolas Jouanin
Analyste - TVA8ième directive
Bureau SI-1C / DAP2
Tel: 02.51.88.50.18
Adoptez l'éco-attitude.
N'imprimez ce courriel que si c'est
vraiment nécessaire
Message original
Sujet : Re: [PERFORM] Two different execution plan for the same request
De : Yeb Havinga
Pour : JOUANIN Nicolas (44)
Copie à : pgsql-performance@postgresql.org
Date : 07/07/2010 10:27
JOUANIN
Nicolas (44) wrote:
Hi,
I've trouble with some SQL request which have different execution plans
when ran on two different servers. One server is the development
environment, the othe rone is th pre-production env.
Both servers run postgreSQL 8.3.0 on Linux and :
- both databases contains the same data (pg_dump/pg_restore between
servers)
- instances have the same configuration parameters
- vaccum and analyze is run every day.
The only difference I can see is the hardware. The pre-preoduction env.
has more RAM, more CPU and a RAID5 disk array through LVM where data
are stored.
Hello Jouanin,
Could you give some more information following the guidelines from
http://wiki.postgresql.org/wiki/SlowQueryQuestions ?
Essential are the contents from both conf files (comments may be
removed).
regards,
Yeb Havinga
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
[PERFORM] Two different execution plan for the same request
Hi, I've trouble with some SQL request which have different execution plans when ran on two different servers. One server is the development environment, the othe rone is th pre-production env. Both servers run postgreSQL 8.3.0 on Linux and : - both databases contains the same data (pg_dump/pg_restore between servers) - instances have the same configuration parameters - vaccum and analyze is run every day. The only difference I can see is the hardware. The pre-preoduction env. has more RAM, more CPU and a RAID5 disk array through LVM where data are stored. Performances should be better on the pre-production but unfortunatelly this is not the case. Below are the execution plan on development env and pre-production. As you can see pre-production performance are poor, far slower than dev. env ! For information, enable_seqscan is turned off (some DBA advice). Also I can get the same execution plan on both environment by turning off enable_mergejoin and enable_hashjoin on the pre-production. Then execution matches and performances are much better. Could anyone help to guess why both servers don't have the same execution plans ? Can this be due to hardware difference ? Let me know if you need more detailed informations on these configurations. Thanks. Dev. environment : 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=3586307.73..3586341.94 rows=205 width=123) (actual time=440.626..440.875 rows=1 loops=1) -> Sort (cost=3586307.73..3586316.28 rows=3421 width=123) (actual time=440.624..440.723 rows=187 loops=1) Sort Key: constantestablenbienservice.code, constantestablenbienservice.id, constantestablenbienservice.lib_code Sort Method: quicksort Memory: 24kB -> Nested Loop Left Join (cost=40.38..3586106.91 rows=3421 width=123) (actual time=71.696..440.240 rows=187 loops=1) Filter: (constantestablenbienservice.parent IS NULL) -> Nested Loop Left Join (cost=40.38..3554085.80 rows=6842 width=4) (actual time=66.576..433.797 rows=187 loops=1) -> Nested Loop (cost=0.00..5041.46 rows=1246 width=25) (actual time=22.923..23.054 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=5.534..5.537 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..5020.68 rows=1246 width=50) (actual time=17.382..17.460 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=40.38..2836.96 rows=911 width=29) (actual time=13.511..13.677 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..40.15 rows=911 width=0) (actual time=13.144..13.144 rows=6 loops=30) Index Cond: ((constantestablebienservice.id_operation)::text = (constantestableoperation.id_tech)::text) -> Index Scan using n_bien_service_pkey on n_bien_service constantestablenbienservice (cost=0.00..4.67 rows=1 width=127) (actual time=0.030..0.031 rows=1 loops=187) Index Cond: (constantestablebienservice.bs_code = constantestablenbienservice.id) Total runtime: 2.558 ms (20 lignes) Pre-production: EXPLAIN analyze SELECT DISTINCT ConstantesTableNBienService.id,C
