Re: Poor performance using CTE

On 11/14/2012 10:23 AM, David Greco wrote:
>
> Have a query using a CTE that is performing very poorly. The
> equivalent query against the same data in an Oracle database runs in
> under 1 second, in Postgres  it takes 2000 seconds.
>
> The smp_pkg.get_invoice_charges queries fedexinvoices for some data
> and normalizes it into a SETOF some record type. It is declared to be
> STABLE. Fedexinvoices consists of about 1.3M rows of medium width.
> Fedexinvoices.id is the primary key on that table, and
> trim(fedexinvoices.trackno) is indexed via the function trim.
>
> The plan for the equivalent query in Oracle is much smaller and
> simpler. No sequential (or full table) scans on fedexinvoices.
>
> WITH charges as (
>
>                 SELECT fi2.id, smp_pkg.get_invoice_charges(fi2.id)
> charge_info from fedexinvoices fi2
>
> )
>
> select fedexinvoices.* from
>
> fedexinvoices
>
> inner join charges on charges.id = fedexinvoices.id AND
> (charges.charge_info).charge_name IN ('ADDRESS CORRECTION
> CHARGE','ADDRESS CORRECTION')
>
> where
>
> trim(fedexinvoices.trackno)='799159791643'
>
> ;
>


Can you explain what you're actually trying to do here? The query looks
rather odd. Why are you joining this table (or an extract from it) to
itself?


In any case, you could almost certainly recast it and have it run fast
by first filtering on the tracking number.


cheers

andrew