From: Andrew Dunstan <andrew@dunslane.net>
To: David Greco <David_Greco@harte-hanks.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Poor performance using CTE
Date: Wed, 14 Nov 2012 10:50:58 -0500
Message-ID: <50A3BDE2.1050201@dunslane.net> (raw)
In-Reply-To: <187F6C10D2931A4386EE8E58E13857F61291C600@BY2PRD0811MB415.namprd08.prod.outlook.com>
References: <187F6C10D2931A4386EE8E58E13857F61291C600@BY2PRD0811MB415.namprd08.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@dunslane.net, David_Greco@harte-hanks.com
Subject: Re: Poor performance using CTE
In-Reply-To: <50A3BDE2.1050201@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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