Two different execution plan for the same request

From: "JOUANIN Nicolas (44)" <nicolas(dot)jouanin(at)dgfip(dot)finances(dot)gouv(dot)fr>
To: pgsql-performance(at)postgresql(dot)org
Subject: Two different execution plan for the same request
Date: 2010-07-07 07:55:28
Message-ID: 4C3432F0.1090004@dgfip.finances.gouv.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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>
&nbsp;- both databases contains the same data (pg_dump/pg_restore between
servers)<br>
&nbsp;- instances have the same configuration parameters<br>
&nbsp;- 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&nbsp; T_OPERATION ConstantesTableOperation<br>
&nbsp;&nbsp;&nbsp; ON ConstantesTableDemande.id_tech =
ConstantesTableOperation.id_demande<br>
LEFT OUTER JOIN T_BIEN_SERVICE ConstantesTableBienService<br>
&nbsp;&nbsp;&nbsp; ON&nbsp; ConstantesTableBienService.id_operation =
ConstantesTableOperation.id_tech<br>
LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService<br>
&nbsp;&nbsp;&nbsp; ON ConstantesTableBienService.bs_code =
ConstantesTableNBienService.id<br>
WHERE<br>
&nbsp;&nbsp;&nbsp; ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A=='<br>
&nbsp;&nbsp;&nbsp; AND ConstantesTableOperation.type_operation = 'acq'<br>
&nbsp;&nbsp;&nbsp; AND ConstantesTableNBienService.parent is null<br>
ORDER BY ConstantesTableNBienService.code ASC;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
QUERY PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
&nbsp;Unique&nbsp; (cost=3586307.73..3586341.94 rows=205 width=123) (actual
time=440.626..440.875 rows=1 loops=1)<br>
&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=3586307.73..3586316.28 rows=3421 width=123)
(actual time=440.624..440.723 rows=187 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: constantestablenbienservice.code,
constantestablenbienservice.id, constantestablenbienservice.lib_code<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Method:&nbsp; quicksort&nbsp; Memory: 24kB<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop Left Join&nbsp; (cost=40.38..3586106.91
rows=3421 width=123) (actual time=71.696..440.240 rows=187 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (constantestablenbienservice.parent IS NULL)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop Left Join&nbsp; (cost=40.38..3554085.80
rows=6842 width=4) (actual time=66.576..433.797 rows=187 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp; (cost=0.00..5041.46 rows=1246
width=25) (actual time=22.923..23.054 rows=30 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using t_demande_pkey on
t_demande constantestabledemande&nbsp; (cost=0.00..8.32 rows=1 width=25)
(actual time=5.534..5.537 rows=1 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: ((id_tech)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using
idx_operation_demande on t_operation constantestableoperation&nbsp;
(cost=0.00..5020.68 rows=1246 width=50) (actual time=17.382..17.460
rows=30 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond:
((constantestableoperation.id_demande)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter:
((constantestableoperation.type_operation)::text = 'acq'::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on t_bien_service
constantestablebienservice&nbsp; (cost=40.38..2836.96 rows=911 width=29)
(actual time=13.511..13.677 rows=6 loops=30)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond:
((constantestablebienservice.id_operation)::text =
(constantestableoperation.id_tech)::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on
idx_bien_service_operation&nbsp; (cost=0.00..40.15 rows=911 width=0) (actual
time=13.144..13.144 rows=6 loops=30)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond:
((constantestablebienservice.id_operation)::text =
(constantestableoperation.id_tech)::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using n_bien_service_pkey on
n_bien_service constantestablenbienservice&nbsp; (cost=0.00..4.67 rows=1
width=127) (actual time=0.030..0.031 rows=1 loops=187)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (constantestablebienservice.bs_code =
constantestablenbienservice.id)<br>
<b><font color="#ff0000">&nbsp;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&nbsp; T_OPERATION ConstantesTableOperation<br>
&nbsp;&nbsp;&nbsp; ON ConstantesTableDemande.id_tech =
ConstantesTableOperation.id_demande<br>
LEFT OUTER JOIN T_BIEN_SERVICE ConstantesTableBienService<br>
&nbsp;&nbsp;&nbsp; ON&nbsp; ConstantesTableBienService.id_operation =
ConstantesTableOperation.id_tech<br>
LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService<br>
&nbsp;&nbsp;&nbsp; ON ConstantesTableBienService.bs_code =
ConstantesTableNBienService.id<br>
WHERE<br>
&nbsp;&nbsp;&nbsp; ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A=='<br>
&nbsp;&nbsp;&nbsp; AND ConstantesTableOperation.type_operation = 'acq'<br>
&nbsp;&nbsp;&nbsp; AND ConstantesTableNBienService.parent is null<br>
ORDER BY ConstantesTableNBienService.code ASC;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
QUERY PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
&nbsp;Unique&nbsp; (cost=2679729.52..2679763.24 rows=205 width=123) (actual
time=740448.007..740448.269 rows=1 loops=1)<br>
&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=2679729.52..2679737.95 rows=3372 width=123)
(actual time=740448.004..740448.111 rows=187 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: constantestablenbienservice.code,
constantestablenbienservice.id, constantestablenbienservice.lib_code<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Method:&nbsp; quicksort&nbsp; Memory: 24kB<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Hash Left Join&nbsp; (cost=2315662.87..2679531.93 rows=3372
width=123) (actual time=723479.640..740447.597 rows=187 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hash Cond: (constantestablebienservice.bs_code =
constantestablenbienservice.id)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (constantestablenbienservice.parent IS NULL)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Hash Left Join&nbsp; (cost=2315640.98..2679417.33
rows=6743 width=4) (actual time=723464.693..740432.218 rows=187 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hash Cond:
((constantestableoperation.id_tech)::text =
(constantestablebienservice.id_operation)::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp; (cost=39.49..4659.51 rows=1228
width=25) (actual time=0.131..0.309 rows=30 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using t_demande_pkey on
t_demande constantestabledemande&nbsp; (cost=0.00..8.32 rows=1 width=25)
(actual time=0.047..0.050 rows=1 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: ((id_tech)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on t_operation
constantestableoperation&nbsp; (cost=39.49..4638.90 rows=1228 width=50)
(actual time=0.079..0.192 rows=30 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond:
((constantestableoperation.id_demande)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter:
((constantestableoperation.type_operation)::text = 'acq'::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on
idx_operation_demande&nbsp; (cost=0.00..39.18 rows=1228 width=0) (actual
time=0.061..0.061 rows=30 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond:
((constantestableoperation.id_demande)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Hash&nbsp; (cost=1486192.10..1486192.10
rows=42894672 width=29) (actual time=723119.538..723119.538
rows=42894671 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using
idx_bien_service_code on t_bien_service constantestablebienservice&nbsp;
(cost=0.00..1486192.10 rows=42894672 width=29) (actual
time=21.546..671603.500 rows=42894671 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Hash&nbsp; (cost=19.33..19.33 rows=205 width=127)
(actual time=14.706..14.706 rows=205 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using n_bien_service_pkey on
n_bien_service constantestablenbienservice&nbsp; (cost=0.00..19.33 rows=205
width=127) (actual time=10.262..14.401 rows=205 loops=1)<br>
<b><font color="#ff0000">&nbsp;Total runtime: 740465.922 ms</font></b><br>
(22 lignes)<br>
</tt></font><font size="-1"><font face="Arial"><br>
</font></font>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 15.1 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Yeb Havinga 2010-07-07 08:27:05 Re: Two different execution plan for the same request
Previous Message Gourish Singbal 2010-07-07 06:33:01 Invitation to connect on LinkedIn