Message-ID: <50A3BDE2.1050201@dunslane.net>
Date: Wed, 14 Nov 2012 10:50:58 -0500
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:15.0) Gecko/20120827 Thunderbird/15.0
MIME-Version: 1.0
To: David Greco <David_Greco@harte-hanks.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Poor performance using CTE
References: 
 <187F6C10D2931A4386EE8E58E13857F61291C600@BY2PRD0811MB415.namprd08.prod.outlook.com>
In-Reply-To: 
 <187F6C10D2931A4386EE8E58E13857F61291C600@BY2PRD0811MB415.namprd08.prod.outlook.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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