pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting estimated cost to agree with actual
4+ messages / 3 participants
[nested] [flat]
* getting estimated cost to agree with actual
@ 2008-06-02 21:43 Justin <justin@emproshunts.com>
2008-06-02 23:38 ` Re: getting estimated cost to agree with actual Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Justin @ 2008-06-02 21:43 UTC (permalink / raw)
To: pgsql-performance
As i've been looking over the more complicated queries that i have
written and gotten allot of help in redoing the quires from you all,
thanks again.
I have noticed that estimated Cost to do the query is way off from
Actual. The queries don't run slow at least not to me.
The Estimated Cost is way higher than the actual time on Hash joins but
on the scan through the tables the Estimate Cost to Actual flips where
Actual is way higher than Estimated Cost
I have tried increasing and decreasing the Stats on the important
columns with no changes
Changed the stats from 10 to 50, 100 and 150, 200 and 250.
The Estimated Cost always stays the same. What is the process to track
down what is going on why the estimate cost is off
----------------Query/View----------------
SELECT (wo.wo_number::text || '-'::text) || wo.wo_subnumber::text AS
wo_number, wo.wo_qtyord, 'Labor' AS valuetype, item.item_number AS
parentitem, wooper.wooper_descrip1 AS wooper_des,
wooperpost.wooperpost_seqnumber AS wooperpost, wooperpost.wooperpost_qty
AS qty, wooperpost.wooperpost_sutime AS setuptime_matcost,
wooperpost.wooperpost_sucost AS setupcost_issuecost,
wooperpost.wooperpost_rntime AS runtime_scrapqty,
wooperpost.wooperpost_rncost AS runcost_scrapcost, wo.wo_status,
wooperpost.wooperpost_timestamp::date AS opposteddate,
wo.wo_completed_date::date AS wocompletedate,
wo.wo_processstart_date::date AS wostarteddated
FROM wo, wooper, wooperpost, itemsite, item
WHERE wo.wo_id = wooper.wooper_wo_id AND wooper.wooper_id =
wooperpost.wooperpost_wooper_id AND wo.wo_itemsite_id =
itemsite.itemsite_id AND itemsite.itemsite_item_id = item.item_id
UNION
SELECT (wo.wo_number::text || '-'::text) || wo.wo_subnumber::text AS
wo_number, wo.wo_qtyord, 'Material' AS valuetype, pitem.item_number AS
parentitem,
CASE
WHEN womatl.womatl_type = 'I'::bpchar THEN citem.item_number
ELSE ( SELECT costelem.costelem_type
FROM costelem, itemcost, womatl
WHERE womatl.womatl_itemcost_id = itemcost.itemcost_id AND
itemcost.itemcost_costelem_id = costelem.costelem_id
LIMIT 1)
END AS wooper_des, 0 AS wooperpost,
womatlpost.womatlpost_qtyposted AS qty, round(( SELECT
sum(womatlpost.womatlpost_cost) / sum(womatlpost.womatlpost_qtyposted)
AS unitcost
FROM womatlpost
WHERE womatlpost.womatlpost_womatl_id = womatl.womatl_id AND
womatlpost.womatlpost_qtyposted > 0::numeric), 4) AS setuptime_matcost,
womatlpost.womatlpost_cost AS setupcost_issuecost, 0.0 AS
runtime_scrapqty, 0.0 AS runcost_scrapcost, wo.wo_status,
womatlpost.womatlpost_dateposted::date AS opposteddate,
wo.wo_completed_date::date AS wocompletedate,
wo.wo_processstart_date::date AS wostarteddated
FROM womatl, wo, itemsite citemsite, item citem, itemsite pitemsite,
item pitem, womatlpost
WHERE wo.wo_id = womatl.womatl_wo_id AND citemsite.itemsite_id =
womatl.womatl_itemsite_id AND citem.item_id = citemsite.itemsite_item_id
AND pitemsite.itemsite_id = wo.wo_itemsite_id AND pitem.item_id =
pitemsite.itemsite_item_id AND womatlpost.womatlpost_womatl_id =
womatl.womatl_id
ORDER BY 1;
-------------End Query-----------
-------------Begin Analyze---------
"Unique (cost=76456.48..77934.64 rows=36954 width=115) (actual
time=1618.244..1729.004 rows=36747 loops=1)"
" -> Sort (cost=76456.48..76548.86 rows=36954 width=115) (actual
time=1618.241..1641.059 rows=36966 loops=1)"
" Sort Key: "*SELECT* 1".wo_number, "*SELECT* 1".wo_qtyord,
('Labor'::text), "*SELECT* 1".parentitem, "*SELECT* 1".wooper_des,
"*SELECT* 1".wooperpost, "*SELECT* 1".qty, "*SELECT*
1".setuptime_matcost, "*SELECT* 1".setupcost_issuecost, "*SELECT*
1".runtime_scrapqty, "*SELECT* 1".runcost_scrapcost, "*SELECT*
1".wo_status, "*SELECT* 1".opposteddate, "*SELECT* 1".wocompletedate,
"*SELECT* 1".wostarteddated"
" Sort Method: quicksort Memory: 8358kB"
" -> Append (cost=2844.41..73652.88 rows=36954 width=115)
(actual time=117.263..809.691 rows=36966 loops=1)"
" -> Subquery Scan "*SELECT* 1" (cost=2844.41..4916.09
rows=21835 width=115) (actual time=117.261..311.658 rows=21847 loops=1)"
" -> Hash Join (cost=2844.41..4697.74 rows=21835
width=115) (actual time=117.250..277.481 rows=21847 loops=1)"
" Hash Cond: (wooper.wooper_wo_id =
public.wo.wo_id)"
" -> Hash Join (cost=2090.82..3125.34
rows=21835 width=75) (actual time=83.903..156.356 rows=21847 loops=1)"
" Hash Cond:
(wooperpost.wooperpost_wooper_id = wooper.wooper_id)"
" -> Seq Scan on wooperpost
(cost=0.00..596.08 rows=22008 width=45) (actual time=0.024..17.068
rows=22020 loops=1)"
" -> Hash (cost=1503.70..1503.70
rows=46970 width=38) (actual time=83.793..83.793 rows=46936 loops=1)"
" -> Seq Scan on wooper
(cost=0.00..1503.70 rows=46970 width=38) (actual time=0.024..42.876
rows=46936 loops=1)"
" -> Hash (cost=723.91..723.91 rows=2374
width=48) (actual time=33.265..33.265 rows=2328 loops=1)"
" -> Hash Join (cost=434.74..723.91
rows=2374 width=48) (actual time=19.562..30.708 rows=2328 loops=1)"
" Hash Cond: (item.item_id =
itemsite.itemsite_item_id)"
" -> Seq Scan on item
(cost=0.00..196.38 rows=6138 width=15) (actual time=0.024..4.672
rows=6140 loops=1)"
" -> Hash (cost=405.07..405.07
rows=2374 width=41) (actual time=19.522..19.522 rows=2328 loops=1)"
" -> Hash Join
(cost=264.85..405.07 rows=2374 width=41) (actual time=10.300..17.043
rows=2328 loops=1)"
" Hash Cond:
(public.wo.wo_itemsite_id = itemsite.itemsite_id)"
" -> Seq Scan on wo
(cost=0.00..92.74 rows=2374 width=41) (actual time=0.019..1.988
rows=2328 loops=1)"
" -> Hash
(cost=188.82..188.82 rows=6082 width=8) (actual time=10.259..10.259
rows=6084 loops=1)"
" -> Seq Scan on
itemsite (cost=0.00..188.82 rows=6082 width=8) (actual
time=0.021..5.469 rows=6084 loops=1)"
" -> Subquery Scan "*SELECT* 2" (cost=2081.69..68736.79
rows=15119 width=83) (actual time=96.372..456.864 rows=15119 loops=1)"
" -> Hash Join (cost=2081.69..68585.60 rows=15119
width=83) (actual time=96.365..429.660 rows=15119 loops=1)"
" Hash Cond: (public.womatl.womatl_itemsite_id
= citemsite.itemsite_id)"
" InitPlan"
" -> Limit (cost=0.00..0.60 rows=1
width=12) (never executed)"
" -> Nested Loop (cost=0.00..10306.58
rows=17196 width=12) (never executed)"
" -> Nested Loop
(cost=0.00..5478.44 rows=17196 width=4) (never executed)"
" -> Seq Scan on womatl
(cost=0.00..452.96 rows=17196 width=4) (never executed)"
" -> Index Scan using
itemcost_pkey on itemcost (cost=0.00..0.28 rows=1 width=8) (never
executed)"
" Index Cond:
(itemcost.itemcost_id = public.womatl.womatl_itemcost_id)"
" -> Index Scan using
costelem_pkey on costelem (cost=0.00..0.27 rows=1 width=16) (never
executed)"
" Index Cond:
(costelem.costelem_id = itemcost.itemcost_costelem_id)"
" -> Hash Join (cost=1421.50..2295.65
rows=15119 width=76) (actual time=67.342..141.405 rows=15119 loops=1)"
" Hash Cond: (public.womatl.womatl_wo_id
= public.wo.wo_id)"
" -> Hash Join (cost=667.91..1315.28
rows=15119 width=36) (actual time=35.971..82.704 rows=15119 loops=1)"
" Hash Cond:
(public.womatlpost.womatlpost_womatl_id = public.womatl.womatl_id)"
" -> Seq Scan on womatlpost
(cost=0.00..307.19 rows=15119 width=26) (actual time=0.026..12.373
rows=15119 loops=1)"
" -> Hash (cost=452.96..452.96
rows=17196 width=14) (actual time=35.911..35.911 rows=17199 loops=1)"
" -> Seq Scan on womatl
(cost=0.00..452.96 rows=17196 width=14) (actual time=0.017..21.804
rows=17199 loops=1)"
" -> Hash (cost=723.91..723.91
rows=2374 width=48) (actual time=31.340..31.340 rows=2328 loops=1)"
" -> Hash Join
(cost=434.74..723.91 rows=2374 width=48) (actual time=18.197..28.794
rows=2328 loops=1)"
" Hash Cond: (pitem.item_id =
pitemsite.itemsite_item_id)"
" -> Seq Scan on item pitem
(cost=0.00..196.38 rows=6138 width=15) (actual time=0.006..4.172
rows=6140 loops=1)"
" -> Hash
(cost=405.07..405.07 rows=2374 width=41) (actual time=18.172..18.172
rows=2328 loops=1)"
" -> Hash Join
(cost=264.85..405.07 rows=2374 width=41) (actual time=9.441..15.807
rows=2328 loops=1)"
" Hash Cond:
(public.wo.wo_itemsite_id = pitemsite.itemsite_id)"
" -> Seq Scan on
wo (cost=0.00..92.74 rows=2374 width=41) (actual time=0.007..1.668
rows=2328 loops=1)"
" -> Hash
(cost=188.82..188.82 rows=6082 width=8) (actual time=9.410..9.410
rows=6084 loops=1)"
" -> Seq
Scan on itemsite pitemsite (cost=0.00..188.82 rows=6082 width=8)
(actual time=0.013..4.726 rows=6084 loops=1)"
" -> Hash (cost=583.57..583.57 rows=6082
width=15) (actual time=28.856..28.856 rows=6084 loops=1)"
" -> Hash Join (cost=273.11..583.57
rows=6082 width=15) (actual time=10.017..23.614 rows=6084 loops=1)"
" Hash Cond:
(citemsite.itemsite_item_id = citem.item_id)"
" -> Seq Scan on itemsite
citemsite (cost=0.00..188.82 rows=6082 width=8) (actual
time=0.008..3.992 rows=6084 loops=1)"
" -> Hash (cost=196.38..196.38
rows=6138 width=15) (actual time=9.987..9.987 rows=6140 loops=1)"
" -> Seq Scan on item citem
(cost=0.00..196.38 rows=6138 width=15) (actual time=0.009..4.928
rows=6140 loops=1)"
" SubPlan"
" -> Aggregate (cost=4.28..4.29 rows=1
width=14) (actual time=0.009..0.009 rows=1 loops=15119)"
" -> Index Scan using
womatlpost_womatl_id_index on womatlpost (cost=0.00..4.27 rows=1
width=14) (actual time=0.004..0.005 rows=1 loops=15119)"
" Index Cond:
(womatlpost_womatl_id = $1)"
" Filter: (womatlpost_qtyposted >
0::numeric)"
"Total runtime: 1751.218 ms"
-------------End Analyze ------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting estimated cost to agree with actual
2008-06-02 21:43 getting estimated cost to agree with actual Justin <justin@emproshunts.com>
@ 2008-06-02 23:38 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-06-03 01:20 ` Re: getting estimated cost to agree with actual Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2008-06-02 23:38 UTC (permalink / raw)
To: Justin <justin@emproshunts.com>; +Cc: pgsql-performance
On Mon, Jun 2, 2008 at 3:43 PM, Justin <justin@emproshunts.com> wrote:
> As i've been looking over the more complicated queries that i have written
> and gotten allot of help in redoing the quires from you all, thanks again.
>
> I have noticed that estimated Cost to do the query is way off from Actual.
> The queries don't run slow at least not to me. The Estimated Cost is way
> higher than the actual time on Hash joins but on the scan through the tables
> the Estimate Cost to Actual flips where Actual is way higher than Estimated
> Cost
>
> I have tried increasing and decreasing the Stats on the important columns
> with no changes
Well, they're not measured in the same units. estimated costs are in
terms of the cost to sequentially scan a single tuple, while actual
costs are in milliseconds.
You might be able to change the cost of sequential scan from 1 to
something else and everything else to reflect that change to get them
close. But they aren't supposed to match directly up.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting estimated cost to agree with actual
2008-06-02 21:43 getting estimated cost to agree with actual Justin <justin@emproshunts.com>
2008-06-02 23:38 ` Re: getting estimated cost to agree with actual Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-06-03 01:20 ` Gregory Stark <stark@enterprisedb.com>
2008-06-03 03:06 ` Re: getting estimated cost to agree with actual Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Gregory Stark @ 2008-06-03 01:20 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Justin <justin@emproshunts.com>; pgsql-performance
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On Mon, Jun 2, 2008 at 3:43 PM, Justin <justin@emproshunts.com> wrote:
>>
>> I have noticed that estimated Cost to do the query is way off from Actual.
>
> Well, they're not measured in the same units. estimated costs are in
> terms of the cost to sequentially scan a single tuple, while actual
> costs are in milliseconds.
s/tuple/page/
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting estimated cost to agree with actual
2008-06-02 21:43 getting estimated cost to agree with actual Justin <justin@emproshunts.com>
2008-06-02 23:38 ` Re: getting estimated cost to agree with actual Scott Marlowe <scott.marlowe@gmail.com>
2008-06-03 01:20 ` Re: getting estimated cost to agree with actual Gregory Stark <stark@enterprisedb.com>
@ 2008-06-03 03:06 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2008-06-03 03:06 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Justin <justin@emproshunts.com>; pgsql-performance
On Mon, Jun 2, 2008 at 7:20 PM, Gregory Stark <stark@enterprisedb.com> wrote:
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
>
>> On Mon, Jun 2, 2008 at 3:43 PM, Justin <justin@emproshunts.com> wrote:
>>>
>>> I have noticed that estimated Cost to do the query is way off from Actual.
>>
>> Well, they're not measured in the same units. estimated costs are in
>> terms of the cost to sequentially scan a single tuple, while actual
>> costs are in milliseconds.
>
> s/tuple/page/
Dangit! I knew that too. time for some sleep I guess. :)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-06-03 03:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-02 21:43 getting estimated cost to agree with actual Justin <justin@emproshunts.com>
2008-06-02 23:38 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-06-03 01:20 ` Gregory Stark <stark@enterprisedb.com>
2008-06-03 03:06 ` Scott Marlowe <scott.marlowe@gmail.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