yes that improved the select statement allot from 18 to 20 to 6
seconds 3 fold improvement with no indexes. thanks
Now i tested your new and improved select statement with the indexes its
query time was taking from 90 to 140 milliseconds compared to the
original select statement of 94 to 120 milliseconds. I tested both
select statements 5 times to get an average and they both hover around
110 to 120
I attached the results from the explain that used the indexes and one
with no index
although in both case with and with out indexes the estimate still
failed to return the correct number by allot.
Stephen Denne wrote:
Justin wrote:
Then why are the estimates so far off?
Perhaps a really high correlation between those records where coitem_status =
'O' and those records in your date range. The planner estimates 1 row when
restricting by both restrictions, whereas there where 458. The 1 row was
probably a result of a small % for status='O' multiplied by a small % for the
date range.
If estimates where correct would it improve the performance that much.
Possibly, but a better performance gain might be obtained by rewriting the
query, changing the case expression to something along the lines of:
coalesce((
select wrkcnt_code || ' Operation Completed :' || cast(wooper_seqnumber as text)
from wooper inner join wrkcnt on wrkcnt_id = wooper_wrkcnt_id
where wooper_rncomplete = true and wooper_wo_id = coitem_order_id
order by wooper_seqnumber desc limit 1
),'No Operation Completed') as LastFinshedOp
regards,
Stephen Denne.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer
focus, and courage. This email with any attachments is confidential and may be
subject to legal privilege. If it is not intended for you please advise by
reply immediately, destroy it and do not copy, disclose or use it in any way.
__________________________________________________________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
"Sort (cost=1161.97..1161.98 rows=1 width=83) (actual time=6458.955..6459.166
rows=458 loops=1)"
" Sort Key: to_char((coitem.coitem_scheddate)::timestamp with time zone,
'Month Dy DD YYYY'::text), cohead.cohead_number, coitem.coitem_linenumber"
" -> Nested Loop Left Join (cost=0.00..1161.96 rows=1 width=83) (actual
time=14.638..6453.589 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..191.54 rows=1 width=75) (actual
time=0.121..31.942 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..191.22 rows=1 width=44) (actual
time=0.111..24.863 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..182.94 rows=1 width=40)
(actual time=0.102..18.215 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..182.53 rows=1 width=30)
(actual time=0.091..11.299 rows=458 loops=1)"
" -> Index Scan using coitem_status_key on
coitem (cost=0.00..174.25 rows=1 width=30) (actual time=0.077..2.314 rows=458
loops=1)"
" Index Cond: (coitem_status =
'O'::bpchar)"
" Filter: ((coitem_scheddate >=
'2008-01-01'::date) AND (coitem_scheddate <= '2008-12-31'::date))"
" -> Index Scan using itemsite_pkey on itemsite
(cost=0.00..8.27 rows=1 width=8) (actual time=0.012..0.014 rows=1 loops=458)"
" Index Cond: (coitem.coitem_itemsite_id =
itemsite.itemsite_id)"
" -> Index Scan using item_pkey on item
(cost=0.00..0.40 rows=1 width=18) (actual time=0.008..0.010 rows=1 loops=458)"
" Index Cond: (itemsite.itemsite_item_id =
item.item_id)"
" -> Index Scan using cohead_pkey on cohead
(cost=0.00..8.27 rows=1 width=12) (actual time=0.008..0.010 rows=1 loops=458)"
" Index Cond: (cohead.cohead_id =
coitem.coitem_cohead_id)"
" -> Index Scan using cust_pkey on custinfo (cost=0.00..0.31
rows=1 width=39) (actual time=0.007..0.010 rows=1 loops=458)"
" Index Cond: (cohead.cohead_cust_id = custinfo.cust_id)"
" -> Index Scan using wo_pkey on wo (cost=0.00..8.27 rows=1 width=12)
(actual time=0.007..0.011 rows=1 loops=458)"
" Index Cond: (wo.wo_id = coitem.coitem_order_id)"
" SubPlan"
" -> Limit (cost=962.12..962.12 rows=1 width=36) (actual
time=13.977..13.978 rows=1 loops=458)"
" -> Sort (cost=962.12..962.12 rows=1 width=36) (actual
time=13.970..13.970 rows=1 loops=458)"
" Sort Key: wooper.wooper_seqnumber"
" -> Nested Loop (cost=0.00..962.11 rows=1 width=36)
(actual time=13.515..13.951 rows=1 loops=458)"
" Join Filter: (wrkcnt.wrkcnt_id =
wooper.wooper_wrkcnt_id)"
" -> Seq Scan on wooper (cost=0.00..958.41 rows=1
width=8) (actual time=13.469..13.838 rows=1 loops=458)"
" Filter: (wooper_rncomplete AND (wooper_wo_id
= $0))"
" -> Seq Scan on wrkcnt (cost=0.00..2.75 rows=75
width=36) (actual time=0.008..0.049 rows=75 loops=419)"
"Total runtime: 6460.009 ms"
"Sort (cost=113.62..113.62 rows=1 width=83) (actual time=90.390..90.611
rows=458 loops=1)"
" Sort Key: to_char((coitem.coitem_scheddate)::timestamp with time zone,
'Month Dy DD YYYY'::text), cohead.cohead_number, coitem.coitem_linenumber"
" -> Nested Loop (cost=16.40..113.61 rows=1 width=83) (actual
time=0.561..86.175 rows=458 loops=1)"
" -> Nested Loop (cost=16.40..45.67 rows=1 width=52) (actual
time=0.483..17.671 rows=458 loops=1)"
" -> Nested Loop Left Join (cost=16.40..45.27 rows=1 width=42)
(actual time=0.473..13.722 rows=458 loops=1)"
" -> Nested Loop (cost=16.40..36.99 rows=1 width=34)
(actual time=0.463..9.884 rows=458 loops=1)"
" -> Nested Loop (cost=16.40..28.70 rows=1 width=34)
(actual time=0.453..5.823 rows=458 loops=1)"
" -> Bitmap Heap Scan on coitem
(cost=16.40..20.42 rows=1 width=30) (actual time=0.436..0.847 rows=458 loops=1)"
" Recheck Cond: ((coitem_scheddate >=
'2008-01-01'::date) AND (coitem_scheddate <= '2008-12-31'::date) AND
(coitem_status = 'O'::bpchar))"
" -> BitmapAnd (cost=16.40..16.40 rows=1
width=0) (actual time=0.419..0.419 rows=0 loops=1)"
" -> Bitmap Index Scan on
coitem_scheddate_index (cost=0.00..4.85 rows=59 width=0) (actual
time=0.240..0.240 rows=1027 loops=1)"
" Index Cond:
((coitem_scheddate >= '2008-01-01'::date) AND (coitem_scheddate <=
'2008-12-31'::date))"
" -> Bitmap Index Scan on
coitem_status_key (cost=0.00..11.31 rows=407 width=0) (actual
time=0.161..0.161 rows=464 loops=1)"
" Index Cond: (coitem_status =
'O'::bpchar)"
" -> Index Scan using cohead_pkey on cohead
(cost=0.00..8.27 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=458)"
" Index Cond: (cohead.cohead_id =
coitem.coitem_cohead_id)"
" -> Index Scan using itemsite_pkey on itemsite
(cost=0.00..8.27 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=458)"
" Index Cond: (coitem.coitem_itemsite_id =
itemsite.itemsite_id)"
" -> Index Scan using wo_pkey on wo (cost=0.00..8.27
rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=458)"
" Index Cond: (wo.wo_id = coitem.coitem_order_id)"
" -> Index Scan using item_pkey on item (cost=0.00..0.40 rows=1
width=18) (actual time=0.005..0.006 rows=1 loops=458)"
" Index Cond: (itemsite.itemsite_item_id = item.item_id)"
" -> Index Scan using cust_pkey on custinfo (cost=0.00..0.31 rows=1
width=39) (actual time=0.004..0.006 rows=1 loops=458)"
" Index Cond: (cohead.cohead_cust_id = custinfo.cust_id)"
" SubPlan"
" -> Limit (cost=67.59..67.60 rows=1 width=36) (actual
time=0.126..0.127 rows=1 loops=458)"
" -> Sort (cost=67.59..67.60 rows=1 width=36) (actual
time=0.121..0.121 rows=1 loops=458)"
" Sort Key: wooper.wooper_seqnumber"
" -> Nested Loop (cost=0.00..67.58 rows=1 width=36)
(actual time=0.044..0.110 rows=1 loops=458)"
" Join Filter: (wrkcnt.wrkcnt_id =
wooper.wooper_wrkcnt_id)"
" -> Index Scan using wooper_wo_id_index on wooper
(cost=0.00..63.89 rows=1 width=8) (actual time=0.023..0.025 rows=1 loops=458)"
" Index Cond: (wooper_wo_id = $0)"
" Filter: wooper_rncomplete"
" -> Seq Scan on wrkcnt (cost=0.00..2.75 rows=75
width=36) (actual time=0.003..0.042 rows=75 loops=419)"
"Total runtime: 91.475 ms"
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
