Slow queries in PL/PGSQL function

From: Jim Crate <jcrate(at)deepskytech(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Slow queries in PL/PGSQL function
Date: 2004-02-19 23:00:09
Message-ID: r02010100-1032-5E4696A2632F11D8B8E90003939CD378@[63.175.177.240]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a couple queries in a PL/PGSQL function which execute very slowly (around
one minute each) which execute in .5 second when not executed from within the
function. Is there any way to determine why this is happening? I couldn't
figure out how to run EXPLAIN ANALYZE from within the function. The queries
aren't very complicated.

Here is an example query and plan. About 240K rows in x_rbl_ips, 7.5M rows in
filter_ips.

explain analyze SELECT DISTINCT i_ip
FROM x_rbl_ips
LEFT JOIN filter_ips ON x_rbl_ips.i_ip = filter_ips.i_filter_ip
WHERE x_rbl_ips.dts_last_modified > '2004-02-18 22:24:15.901689+00'
AND filter_ips.i_filter_ip IS NOT NULL
AND (i_filter_type_flags & X'02000000'::integer) <> X'02000000'::integer

QUERY PLAN
Unique (cost=5703.70..5703.81 rows=23 width=4) (actual time=524.439..524.439
rows=0 loops=1)
-> Sort (cost=5703.70..5703.75 rows=23 width=4) (actual
time=524.438..524.438 rows=0 loops=1)
Sort Key: x_rbl_ips.i_ip
-> Nested Loop (cost=0.00..5703.18 rows=23 width=4) (actual
time=524.028..524.028 rows=0 loops=1)
-> Seq Scan on x_rbl_ips (cost=0.00..5616.56 rows=23 width=4)
(actual time=412.738..415.842 rows=1738 loops=1)
Filter: (dts_last_modified > '2004-02-18
22:44:15.901689'::timestamp without time zone)
-> Index Scan using filter_ips_source_ip on filter_ips
(cost=0.00..3.75 rows=1 width=4) (actual time=0.061..0.061 rows=0 loops=1738)
Index Cond: ("outer".i_ip = filter_ips.i_filter_ip)
Filter: ((i_filter_ip IS NOT NULL) AND ((i_filter_type_flags
& 33554432) <> 33554432))
Total runtime: 524.868 ms

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Crate 2004-02-19 23:06:42 Slow queries in PL/PGSQL function
Previous Message Tom Lane 2004-02-19 22:42:25 Re: pg_restore problem