pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTwo different execution plan for the same request
7+ messages / 4 participants
[nested] [flat]
* Two different execution plan for the same request
@ 2010-07-07 07:55 JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
0 siblings, 1 reply; 7+ messages in thread
From: JOUANIN Nicolas (44) @ 2010-07-07 07:55 UTC (permalink / raw)
To: pgsql-performance
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<font size="-1"><font face="Arial">Hi,<br>
<br>
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.<br>
Both servers run postgreSQL 8.3.0 on Linux and :<br>
- both databases contains the same data (pg_dump/pg_restore between
servers)<br>
- instances have the same configuration parameters<br>
- vaccum and analyze is run every day.<br>
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. <br>
Performances should be better on the pre-production but unfortunatelly
this is not the case.<br>
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 !<br>
For information, enable_seqscan is turned off (some DBA advice). <br>
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.<br>
Could anyone help to guess why both servers don't have the same
execution plans ? Can this be due to hardware difference ?<br>
<br>
Let me know if you need more detailed informations on these
configurations.<br>
<br>
Thanks.<br>
<br>
Dev. environment :<br>
</font></font><font size="-1"><tt>EXPLAIN analyze SELECT DISTINCT
ConstantesTableNBienService.id,ConstantesTableNBienService.code,ConstantesTableNBienService.lib_code<br>
FROM T_DEMANDE ConstantesTableDemande<br>
LEFT OUTER JOIN T_OPERATION ConstantesTableOperation<br>
ON ConstantesTableDemande.id_tech =
ConstantesTableOperation.id_demande<br>
LEFT OUTER JOIN T_BIEN_SERVICE ConstantesTableBienService<br>
ON ConstantesTableBienService.id_operation =
ConstantesTableOperation.id_tech<br>
LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService<br>
ON ConstantesTableBienService.bs_code =
ConstantesTableNBienService.id<br>
WHERE<br>
ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A=='<br>
AND ConstantesTableOperation.type_operation = 'acq'<br>
AND ConstantesTableNBienService.parent is null<br>
ORDER BY ConstantesTableNBienService.code ASC;<br>
QUERY PLAN <br>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
Unique (cost=3586307.73..3586341.94 rows=205 width=123) (actual
time=440.626..440.875 rows=1 loops=1)<br>
-> Sort (cost=3586307.73..3586316.28 rows=3421 width=123)
(actual time=440.624..440.723 rows=187 loops=1)<br>
Sort Key: constantestablenbienservice.code,
constantestablenbienservice.id, constantestablenbienservice.lib_code<br>
Sort Method: quicksort Memory: 24kB<br>
-> Nested Loop Left Join (cost=40.38..3586106.91
rows=3421 width=123) (actual time=71.696..440.240 rows=187 loops=1)<br>
Filter: (constantestablenbienservice.parent IS NULL)<br>
-> Nested Loop Left Join (cost=40.38..3554085.80
rows=6842 width=4) (actual time=66.576..433.797 rows=187 loops=1)<br>
-> Nested Loop (cost=0.00..5041.46 rows=1246
width=25) (actual time=22.923..23.054 rows=30 loops=1)<br>
-> 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)<br>
Index Cond: ((id_tech)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
-> 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)<br>
Index Cond:
((constantestableoperation.id_demande)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
Filter:
((constantestableoperation.type_operation)::text = 'acq'::text)<br>
-> 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)<br>
Recheck Cond:
((constantestablebienservice.id_operation)::text =
(constantestableoperation.id_tech)::text)<br>
-> 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)<br>
Index Cond:
((constantestablebienservice.id_operation)::text =
(constantestableoperation.id_tech)::text)<br>
-> 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)<br>
Index Cond: (constantestablebienservice.bs_code =
constantestablenbienservice.id)<br>
<b><font color="#ff0000"> Total runtime: 2.558 ms<br>
</font></b>(20 lignes)</tt></font><br>
<font size="-1"><font face="Arial"><br>
<br>
Pre-production:<br>
</font></font><font size="-1"><tt>EXPLAIN analyze
SELECT DISTINCT
ConstantesTableNBienService.id,ConstantesTableNBienService.code,ConstantesTableNBienService.lib_code<br>
FROM T_DEMANDE ConstantesTableDemande<br>
LEFT OUTER JOIN T_OPERATION ConstantesTableOperation<br>
ON ConstantesTableDemande.id_tech =
ConstantesTableOperation.id_demande<br>
LEFT OUTER JOIN T_BIEN_SERVICE ConstantesTableBienService<br>
ON ConstantesTableBienService.id_operation =
ConstantesTableOperation.id_tech<br>
LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService<br>
ON ConstantesTableBienService.bs_code =
ConstantesTableNBienService.id<br>
WHERE<br>
ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A=='<br>
AND ConstantesTableOperation.type_operation = 'acq'<br>
AND ConstantesTableNBienService.parent is null<br>
ORDER BY ConstantesTableNBienService.code ASC;<br>
QUERY PLAN <br>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
Unique (cost=2679729.52..2679763.24 rows=205 width=123) (actual
time=740448.007..740448.269 rows=1 loops=1)<br>
-> Sort (cost=2679729.52..2679737.95 rows=3372 width=123)
(actual time=740448.004..740448.111 rows=187 loops=1)<br>
Sort Key: constantestablenbienservice.code,
constantestablenbienservice.id, constantestablenbienservice.lib_code<br>
Sort Method: quicksort Memory: 24kB<br>
-> Hash Left Join (cost=2315662.87..2679531.93 rows=3372
width=123) (actual time=723479.640..740447.597 rows=187 loops=1)<br>
Hash Cond: (constantestablebienservice.bs_code =
constantestablenbienservice.id)<br>
Filter: (constantestablenbienservice.parent IS NULL)<br>
-> Hash Left Join (cost=2315640.98..2679417.33
rows=6743 width=4) (actual time=723464.693..740432.218 rows=187 loops=1)<br>
Hash Cond:
((constantestableoperation.id_tech)::text =
(constantestablebienservice.id_operation)::text)<br>
-> Nested Loop (cost=39.49..4659.51 rows=1228
width=25) (actual time=0.131..0.309 rows=30 loops=1)<br>
-> Index Scan using t_demande_pkey on
t_demande constantestabledemande (cost=0.00..8.32 rows=1 width=25)
(actual time=0.047..0.050 rows=1 loops=1)<br>
Index Cond: ((id_tech)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
-> Bitmap Heap Scan on t_operation
constantestableoperation (cost=39.49..4638.90 rows=1228 width=50)
(actual time=0.079..0.192 rows=30 loops=1)<br>
Recheck Cond:
((constantestableoperation.id_demande)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
Filter:
((constantestableoperation.type_operation)::text = 'acq'::text)<br>
-> Bitmap Index Scan on
idx_operation_demande (cost=0.00..39.18 rows=1228 width=0) (actual
time=0.061..0.061 rows=30 loops=1)<br>
Index Cond:
((constantestableoperation.id_demande)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
-> Hash (cost=1486192.10..1486192.10
rows=42894672 width=29) (actual time=723119.538..723119.538
rows=42894671 loops=1)<br>
-> Index Scan using
idx_bien_service_code on t_bien_service constantestablebienservice
(cost=0.00..1486192.10 rows=42894672 width=29) (actual
time=21.546..671603.500 rows=42894671 loops=1)<br>
-> Hash (cost=19.33..19.33 rows=205 width=127)
(actual time=14.706..14.706 rows=205 loops=1)<br>
-> Index Scan using n_bien_service_pkey on
n_bien_service constantestablenbienservice (cost=0.00..19.33 rows=205
width=127) (actual time=10.262..14.401 rows=205 loops=1)<br>
<b><font color="#ff0000"> Total runtime: 740465.922 ms</font></b><br>
(22 lignes)<br>
</tt></font><font size="-1"><font face="Arial"><br>
</font></font>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Two different execution plan for the same request
@ 2010-07-07 08:27 Yeb Havinga <yebhavinga@gmail.com>
parent: JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
0 siblings, 1 reply; 7+ messages in thread
From: Yeb Havinga @ 2010-07-07 08:27 UTC (permalink / raw)
To: JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Two different execution plan for the same request
@ 2010-07-07 08:47 JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
parent: Yeb Havinga <yebhavinga@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: JOUANIN Nicolas (44) @ 2010-07-07 08:47 UTC (permalink / raw)
To: Yeb Havinga <yebhavinga@gmail.com>; +Cc: pgsql-performance
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
);
Attachments:
Image1 (image/jpeg, 1017B) download
view image
eco-logo.gif (image/gif, 1.2K) download
view image
schema.sql (text/plain, 4.7K) download
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
);
* Re: Two different execution plan for the same request
@ 2010-07-07 08:59 Yeb Havinga <yebhavinga@gmail.com>
parent: JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
1 sibling, 0 replies; 7+ messages in thread
From: Yeb Havinga @ 2010-07-07 08:59 UTC (permalink / raw)
To: JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>; +Cc: pgsql-performance
JOUANIN Nicolas (44) wrote:
>
> The strange thing is that this morning explain analyze now gives a
> much better duration :
>
> There were no modification made on the database except a restart
> yesterday evening and a vacuumdb --analyse ran at night.
If the earlier bad query was run on a fresh imported database that was
not ANALYZEd, it would explain the different and likely bad plan. If you
want to know for sure this is the cause, instead of e.g. faulty
hardware, you could verify redoing the import + query without analyze.
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Two different execution plan for the same request
@ 2010-07-07 08:59 Guillaume Smet <guillaume.smet@gmail.com>
parent: JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
1 sibling, 1 reply; 7+ messages in thread
From: Guillaume Smet @ 2010-07-07 08:59 UTC (permalink / raw)
To: JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>; +Cc: Yeb Havinga <yebhavinga@gmail.com>; pgsql-performance
Hi Nicolas,
On Wed, Jul 7, 2010 at 10:47 AM, JOUANIN Nicolas (44)
<nicolas.jouanin@dgfip.finances.gouv.fr> 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
--
Guillaume
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Two different execution plan for the same request
@ 2010-07-07 11:27 JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: JOUANIN Nicolas (44) @ 2010-07-07 11:27 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: Yeb Havinga <yebhavinga@gmail.com>; pgsql-performance
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<font size="-1"><font face="Arial">It seems to work fine (same
execution plan and less duration) after :<br>
- setting default_statistics_target to 100<br>
- full vacuum with analyze<br>
- reindexdb<br>
<br>
Thanks.<br>
</font></font><br>
<br>
-------- Message original --------<br>
Sujet : Re: [PERFORM] Two different execution plan for the same request<br>
De : Guillaume Smet <a class="moz-txt-link-rfc2396E" href="mailto:guillaume.smet@gmail.com"><guillaume.smet@gmail.com></a><br>
Pour : JOUANIN Nicolas (44)
<a class="moz-txt-link-rfc2396E" href="mailto:nicolas.jouanin@dgfip.finances.gouv.fr"><nicolas.jouanin@dgfip.finances.gouv.fr></a><br>
Copie à : Yeb Havinga <a class="moz-txt-link-rfc2396E" href="mailto:yebhavinga@gmail.com"><yebhavinga@gmail.com></a>,
<a class="moz-txt-link-abbreviated" href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a><br>
Date : 07/07/2010 10:59<br>
<blockquote
cite="mid:AANLkTimrF-p7o2iFzRcRFhH1hPbLT85SVoR6gxynKeRp@mail.gmail.com"
type="cite">
<pre wrap="">Hi Nicolas,
On Wed, Jul 7, 2010 at 10:47 AM, JOUANIN Nicolas (44)
<a class="moz-txt-link-rfc2396E" href="mailto:nicolas.jouanin@dgfip.finances.gouv.fr"><nicolas.jouanin@dgfip.finances.gouv.fr></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">There were no modification made on the database except a restart yesterday evening and a vacuumdb --analyse ran at night.
</pre>
</blockquote>
<pre wrap=""><!---->
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
</pre>
</blockquote>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Two different execution plan for the same request
@ 2010-07-07 12:34 Matthew Wakeling <matthew@flymine.org>
parent: JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
0 siblings, 0 replies; 7+ messages in thread
From: Matthew Wakeling @ 2010-07-07 12:34 UTC (permalink / raw)
To: JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>; +Cc: Guillaume Smet <guillaume.smet@gmail.com>; Yeb Havinga <yebhavinga@gmail.com>; pgsql-performance
On Wed, 7 Jul 2010, JOUANIN Nicolas (44) wrote:
> It seems to work fine (same execution plan and less duration) after :
> - setting default_statistics_target to 100
> - full vacuum with analyze
Don't do VACUUM FULL.
Matthew
--
I suppose some of you have done a Continuous Maths course. Yes? Continuous
Maths? <menacing stares from audience> Whoah, it was like that, was it!
-- Computer Science Lecturer
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-07-07 12:34 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-07 07:55 Two different execution plan for the same request JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
2010-07-07 08:27 ` Yeb Havinga <yebhavinga@gmail.com>
2010-07-07 08:47 ` JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
2010-07-07 08:59 ` Yeb Havinga <yebhavinga@gmail.com>
2010-07-07 08:59 ` Guillaume Smet <guillaume.smet@gmail.com>
2010-07-07 11:27 ` JOUANIN Nicolas (44) <nicolas.jouanin@dgfip.finances.gouv.fr>
2010-07-07 12:34 ` Matthew Wakeling <matthew@flymine.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox