Richard Rowell <[EMAIL PROTECTED]> writes: > I'm trying to port our application from MS-SQL to Postgres. We have > implemented all of our rather complicated application security in the > database. The query that follows takes a half of a second or less on > MS-SQL server and around 5 seconds on Postgres.
The EXPLAIN shows that most of the time is going into repeated executions of svp_getparentproviderids() in the first UNION arm: > -> Seq Scan on da_answer a > (cost=0.00..63928.75 rows=10540 width=24) (actual time=279.080..4418.808 > rows=161 loops=1) > Filter: ((date_effective <= > 9999999999::double precision) AND (inactive <> 1) AND (subplan)) > SubPlan > -> Function Scan on > svp_getparentproviderids (cost=0.00..15.00 rows=5 width=4) (actual > time=0.203..0.203 rows=0 loops=21089) > Filter: > (svp_getparentproviderids = $1) I'd suggest replacing the EXISTS coding by IN: (EXISTS (SELECT * FROM svp_getparentproviderids(1) WHERE svp_getparentproviderids = a.provider_id)) to (a.provider_id IN (SELECT * FROM svp_getparentproviderids(1))) The latter form is likely to be significantly faster in PG 7.4. It's also possible that the speed loss compared to MSSQL is really inside the svp_getparentproviderids function; you should look into that rather than assuming this query per se is at fault. Also, do you actually need UNION as opposed to UNION ALL? The duplicate-elimination behavior of UNION is a bit expensive if not needed. It looks from the EXPLAIN output that some of the unions aren't actually eliminating any rows. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org