pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBig diference in response time (query plan question)
4+ messages / 2 participants
[nested] [flat]
* Big diference in response time (query plan question)
@ 2006-08-16 00:39 Luiz K. Matsumura <luiz@planit.com.br>
2006-08-16 13:34 ` Re: Big diference in response time (query plan question) Dave Dutcher <dave@tridecap.com>
0 siblings, 1 reply; 4+ messages in thread
From: Luiz K. Matsumura @ 2006-08-16 00:39 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have PostgreSQL 8.1.4 running on a P 4 2.8 GHz , 512 MB with Linux
(Fedora Core 3)
The SQL comands below have a performance diference that I think is not
so much acceptable ( 1035.427 ms vs 7.209 ms ), since the tables isn´t
so much big ( contrato have 1907 rows and prog have 40.002 rows )
Can I make some optimization here ?
EXPLAIN ANALYZE
SELECT Contrato.Id
, Min( prog.dtsemeio ) AS DtSemIni
, Max( prog.dtsemeio ) AS DtSemFim
, Min( prog.dtembarque ) AS DtEmbIni
, Max( prog.dtembarque ) AS DtEmbFim
, Min( prog.dtentrega ) AS DtEntIni
, Max( prog.dtentrega ) AS DtEntFim
, COUNT(prog.*) AS QtSem
, SUM( CASE WHEN Prog.DtSemeio >= '20060814' THEN 1 ELSE 0 END )
AS QtSemAb
FROM bvz.Contrato
LEFT OUTER JOIN bvz.Prog ON prog.Fk_Contrato = Contrato.Id
WHERE Contrato.Fk_Clifor = 243
GROUP BY 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=5477.34..5706.84 rows=41 width=48) (actual
time=883.721..1031.159 rows=41 loops=1)
-> Merge Left Join (cost=5477.34..5686.15 rows=860 width=48)
(actual time=868.038..1026.988 rows=1366 loops=1)
Merge Cond: ("outer".id = "inner".fk_contrato)
-> Sort (cost=50.39..50.49 rows=41 width=4) (actual
time=0.614..0.683 rows=41 loops=1)
Sort Key: contrato.id
-> Bitmap Heap Scan on contrato (cost=2.14..49.29
rows=41 width=4) (actual time=0.163..0.508 rows=41 loops=1)
Recheck Cond: (fk_clifor = 243)
-> Bitmap Index Scan on fki_contrato_clifor
(cost=0.00..2.14 rows=41 width=0) (actual time=0.146..0.146 rows=41 loops=1)
Index Cond: (fk_clifor = 243)
-> Sort (cost=5426.95..5526.95 rows=40002 width=48) (actual
time=862.192..956.903 rows=38914 loops=1)
Sort Key: prog.fk_contrato
-> Seq Scan on prog (cost=0.00..1548.02 rows=40002
width=48) (actual time=0.044..169.795 rows=40002 loops=1)
Total runtime: 1035.427 ms
EXPLAIN ANALYZE
SELECT Contrato.Id
, Min( prog.dtsemeio ) AS DtSemIni
, Max( prog.dtsemeio ) AS DtSemFim
, Min( prog.dtembarque ) AS DtEmbIni
, Max( prog.dtembarque ) AS DtEmbFim
, Min( prog.dtentrega ) AS DtEntIni
, Max( prog.dtentrega ) AS DtEntFim
, COUNT(prog.*) AS QtSem
, SUM( CASE WHEN Prog.DtSemeio >= '20060814' THEN 1 ELSE 0 END )
AS QtSemAb
FROM bvz.Contrato
LEFT OUTER JOIN bvz.Prog ON prog.Fk_Contrato = Contrato.Id
WHERE Contrato.Fk_Clifor = 352
GROUP BY 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=2.16..4588.74 rows=28 width=48) (actual
time=2.196..7.027 rows=28 loops=1)
-> Nested Loop Left Join (cost=2.16..4574.63 rows=587 width=48)
(actual time=2.042..6.154 rows=223 loops=1)
-> Index Scan using pk_contrato on contrato
(cost=0.00..100.92 rows=28 width=4) (actual time=1.842..3.045 rows=28
loops=1)
Filter: (fk_clifor = 352)
-> Bitmap Heap Scan on prog (cost=2.16..159.19 rows=47
width=48) (actual time=0.040..0.080 rows=8 loops=28)
Recheck Cond: (prog.fk_contrato = "outer".id)
-> Bitmap Index Scan on fki_prog_contrato
(cost=0.00..2.16 rows=47 width=0) (actual time=0.018..0.018 rows=8 loops=28)
Index Cond: (prog.fk_contrato = "outer".id)
Total runtime: 7.209 ms
I think that the problem is in "LEFT OUTER JOIN" because when I run the
queries with a inner join I have more consistent times,
although the query plan above is a champion :
EXPLAIN ANALYZE
SELECT Contrato.Id
, Min( prog.dtsemeio ) AS DtSemIni
, Max( prog.dtsemeio ) AS DtSemFim
, Min( prog.dtembarque ) AS DtEmbIni
, Max( prog.dtembarque ) AS DtEmbFim
, Min( prog.dtentrega ) AS DtEntIni
, Max( prog.dtentrega ) AS DtEntFim
, COUNT(prog.*) AS QtSem
, SUM( CASE WHEN Prog.DtSemeio >= '20060814' THEN 1 ELSE 0 END )
AS QtSemAb
FROM bvz.Contrato
JOIN bvz.Prog ON prog.Fk_Contrato = Contrato.Id
WHERE Contrato.Fk_Clifor = 243
GROUP BY 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1825.38..1826.71 rows=41 width=48) (actual
time=222.671..222.788 rows=41 loops=1)
-> Hash Join (cost=49.40..1806.03 rows=860 width=48) (actual
time=2.040..217.963 rows=1366 loops=1)
Hash Cond: ("outer".fk_contrato = "inner".id)
-> Seq Scan on prog (cost=0.00..1548.02 rows=40002 width=48)
(actual time=0.047..150.636 rows=40002 loops=1)
-> Hash (cost=49.29..49.29 rows=41 width=4) (actual
time=0.766..0.766 rows=41 loops=1)
-> Bitmap Heap Scan on contrato (cost=2.14..49.29
rows=41 width=4) (actual time=0.146..0.669 rows=41 loops=1)
Recheck Cond: (fk_clifor = 243)
-> Bitmap Index Scan on fki_contrato_clifor
(cost=0.00..2.14 rows=41 width=0) (actual time=0.101..0.101 rows=41 loops=1)
Index Cond: (fk_clifor = 243)
Total runtime: 223.230 ms
EXPLAIN ANALYZE
SELECT Contrato.Id
, Min( prog.dtsemeio ) AS DtSemIni
, Max( prog.dtsemeio ) AS DtSemFim
, Min( prog.dtembarque ) AS DtEmbIni
, Max( prog.dtembarque ) AS DtEmbFim
, Min( prog.dtentrega ) AS DtEntIni
, Max( prog.dtentrega ) AS DtEntFim
, COUNT(prog.*) AS QtSem
, SUM( CASE WHEN Prog.DtSemeio >= '20060814' THEN 1 ELSE 0 END )
AS QtSemAb
FROM bvz.Contrato
JOIN bvz.Prog ON prog.Fk_Contrato = Contrato.Id
WHERE Contrato.Fk_Clifor = 352
GROUP BY 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1811.50..1812.41 rows=28 width=48) (actual
time=215.214..215.291 rows=28 loops=1)
-> Hash Join (cost=44.39..1798.29 rows=587 width=48) (actual
time=3.853..214.178 rows=223 loops=1)
Hash Cond: ("outer".fk_contrato = "inner".id)
-> Seq Scan on prog (cost=0.00..1548.02 rows=40002 width=48)
(actual time=0.075..150.701 rows=40002 loops=1)
-> Hash (cost=44.32..44.32 rows=28 width=4) (actual
time=0.248..0.248 rows=28 loops=1)
-> Bitmap Heap Scan on contrato (cost=2.10..44.32
rows=28 width=4) (actual time=0.111..0.187 rows=28 loops=1)
Recheck Cond: (fk_clifor = 352)
-> Bitmap Index Scan on fki_contrato_clifor
(cost=0.00..2.10 rows=28 width=0) (actual time=0.101..0.101 rows=28 loops=1)
Index Cond: (fk_clifor = 352)
Total runtime: 215.483 ms
Well, in this case the queries with LEFT OUTER join and with inner join
returns the same result set. I don´t have the sufficient knowledge to
affirm , but I suspect that if the query plan used for fk_clifor = 352
and with left outer join is applied for the first query (fk_clifor = 243
with left outer join)
we will have a better total runtime.
There are some manner to make this test ?
By the way (If this is a stupid idea, ignore this), this same (or a
similar) query plan cannot be used in the queries with inner join since
the difference in times ( 215.483 ms vs 7.209 ms) still significative ?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big diference in response time (query plan question)
2006-08-16 00:39 Big diference in response time (query plan question) Luiz K. Matsumura <luiz@planit.com.br>
@ 2006-08-16 13:34 ` Dave Dutcher <dave@tridecap.com>
2006-08-16 14:24 ` Re: Big diference in response time (query plan question) Luiz K. Matsumura <luiz@planit.com.br>
0 siblings, 1 reply; 4+ messages in thread
From: Dave Dutcher @ 2006-08-16 13:34 UTC (permalink / raw)
To: 'Luiz K. Matsumura' <luiz@planit.com.br>; pgsql-performance
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Luiz K. Matsumura
> Well, in this case the queries with LEFT OUTER join and with
> inner join
> returns the same result set. I don´t have the sufficient knowledge to
> affirm , but I suspect that if the query plan used for
> fk_clifor = 352
> and with left outer join is applied for the first query
> (fk_clifor = 243
> with left outer join)
> we will have a better total runtime.
> There are some manner to make this test ?
It looks like Postgres used a nested loop join for the fast query and a
merge join for the slow query. I don't think the left join is causing any
problems. On the slower query the cost estimate of the nested loop must
have been higher than the cost estimate of the merge join because of more
rows. You could try disabling merge joins with the command "set
enable_mergejoin=false". Then run the explain analyze again to see if it is
faster.
If it is faster without merge join, then you could try to change your
settings to make the planner prefer the nested loop. I'm not sure what the
best way to do that is. Maybe you could try reducing the random_page_cost,
which should make index scans cheaper.
Dave
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big diference in response time (query plan question)
2006-08-16 00:39 Big diference in response time (query plan question) Luiz K. Matsumura <luiz@planit.com.br>
2006-08-16 13:34 ` Re: Big diference in response time (query plan question) Dave Dutcher <dave@tridecap.com>
@ 2006-08-16 14:24 ` Luiz K. Matsumura <luiz@planit.com.br>
2006-08-16 14:51 ` Re: Big diference in response time (query plan question) Dave Dutcher <dave@tridecap.com>
0 siblings, 1 reply; 4+ messages in thread
From: Luiz K. Matsumura @ 2006-08-16 14:24 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance
Hi Dave,
Thanks to reply.
I run it now in a Postgres 8.1.4 my notebook (win XP) and the
performance is really much better:
EXPLAIN ANALYZE
SELECT Contrato.Id
, Min( prog.dtsemeio ) AS DtSemIni
, Max( prog.dtsemeio ) AS DtSemFim
, Min( prog.dtembarque ) AS DtEmbIni
, Max( prog.dtembarque ) AS DtEmbFim
, Min( prog.dtentrega ) AS DtEntIni
, Max( prog.dtentrega ) AS DtEntFim
, COUNT(prog.*) AS QtSem
, SUM( CASE WHEN Prog.DtSemeio >= '20060814' THEN 1 ELSE 0 END )
AS QtSemAb
FROM bvz.Contrato
LEFT OUTER JOIN bvz.Prog ON prog.Fk_Contrato = Contrato.Id
WHERE Contrato.Fk_Clifor = 243
GROUP BY 1;
GroupAggregate (cost=2.18..7312.45 rows=42 width=48) (actual
time=0.446..13.195 rows=42 loops=1)
-> Nested Loop Left Join (cost=2.18..7291.22 rows=883 width=48)
(actual time=0.103..10.518 rows=1536 loops=1)
-> Index Scan using pk_contrato on contrato (cost=0.00..100.29
rows=42 width=4) (actual time=0.048..3.163 rows=42 loops=1)
Filter: (fk_clifor = 243)
-> Bitmap Heap Scan on prog (cost=2.18..170.59 rows=50
width=48) (actual time=0.027..0.132 rows=37 loops=42)
Recheck Cond: (prog.fk_contrato = "outer".id)
-> Bitmap Index Scan on fki_prog_contrato
(cost=0.00..2.18 rows=50 width=0) (actual time=0.018..0.018 rows=37
loops=42)
Index Cond: (prog.fk_contrato = "outer".id)
Total runtime: 13.399 ms
Where I can see the current random_page_cost value ? There are some hint
about what value I must set ?
Thanks in advance.
Luiz
Dave Dutcher wrote:
>> Well, in this case the queries with LEFT OUTER join and with
>> inner join
>> returns the same result set. I don´t have the sufficient knowledge to
>> affirm , but I suspect that if the query plan used for
>> fk_clifor = 352
>> and with left outer join is applied for the first query
>> (fk_clifor = 243
>> with left outer join)
>> we will have a better total runtime.
>> There are some manner to make this test ?
>>
>
> It looks like Postgres used a nested loop join for the fast query and a
> merge join for the slow query. I don't think the left join is causing any
> problems. On the slower query the cost estimate of the nested loop must
> have been higher than the cost estimate of the merge join because of more
> rows. You could try disabling merge joins with the command "set
> enable_mergejoin=false". Then run the explain analyze again to see if it is
> faster.
>
> If it is faster without merge join, then you could try to change your
> settings to make the planner prefer the nested loop. I'm not sure what the
> best way to do that is. Maybe you could try reducing the random_page_cost,
> which should make index scans cheaper.
>
> Dave
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big diference in response time (query plan question)
2006-08-16 00:39 Big diference in response time (query plan question) Luiz K. Matsumura <luiz@planit.com.br>
2006-08-16 13:34 ` Re: Big diference in response time (query plan question) Dave Dutcher <dave@tridecap.com>
2006-08-16 14:24 ` Re: Big diference in response time (query plan question) Luiz K. Matsumura <luiz@planit.com.br>
@ 2006-08-16 14:51 ` Dave Dutcher <dave@tridecap.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dave Dutcher @ 2006-08-16 14:51 UTC (permalink / raw)
To: 'Luiz K. Matsumura' <luiz@planit.com.br>; +Cc: pgsql-performance
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Luiz K. Matsumura
>
>
> Where I can see the current random_page_cost value ? There
> are some hint
> about what value I must set ?
> Thanks in advance.
> Luiz
On Linux the random_page_cost is set in the postgresql.conf file. You can
see what it is set to by typing "show random_page_cost". This page has some
guidelines on random_page_cost and other server settings:
http://www.powerpostgresql.com/PerfList/
As it says on the page, make sure you test a variety of queries.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-08-16 14:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-16 00:39 Big diference in response time (query plan question) Luiz K. Matsumura <luiz@planit.com.br>
2006-08-16 13:34 ` Dave Dutcher <dave@tridecap.com>
2006-08-16 14:24 ` Luiz K. Matsumura <luiz@planit.com.br>
2006-08-16 14:51 ` Dave Dutcher <dave@tridecap.com>
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