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'
;
Explain Analyze output, I abbreviated some of the column lists for brevity:
Nested Loop (cost=457380.38..487940.77 rows=1 width=1024) (actual time=1978019.858..1978019.858 rows=0 loops=1)
Output: fedexinvoices.id, ………
Join Filter: (fedexinvoices.id = charges.id)
Buffers: shared hit=20387611, temp written=94071
CTE charges
-> Seq Scan on hits.fedexinvoices fi2 (cost=0.00..457380.38 rows=1350513 width=8) (actual time=0.613..1964632.763 rows=9007863 loops=1)
Output: fi2.id, smp_pkg.get_invoice_charges(fi2.id, NULL::character varying)
Buffers: shared hit=20387606
-> Index Scan using fedexinvoices_trim_track_idx on hits.fedexinvoices (cost=0.00..5.46 rows=1 width=1024) (actual time=0.024..0.026 rows=1 loops=1)
Output: fedexinvoices.id, ………
Index Cond: (btrim((fedexinvoices.trackno)::text) = '799159791643'::text)
Buffers: shared hit=5
-> CTE Scan on charges (cost=0.00..30386.54 rows=13471 width=8) (actual time=1978019.827..1978019.827 rows=0 loops=1)
Output: charges.id, charges.charge_info
Filter: (((charges.charge_info).charge_name)::text = ANY ('{"ADDRESS CORRECTION CHARGE","ADDRESS CORRECTION"}'::text[]))
Buffers: shared hit=20387606, temp written=94071
Total runtime: 1978214.743 ms
