Re: Ad Hoc Indexes

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
>