From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Rowell <richard@bowmansystems.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improve performance of query
Date: Thu, 16 Dec 2004 12:19:11 -0500
Message-ID: <25884.1103217551@sss.pgh.pa.us> (raw)
In-Reply-To: <1103213467.9908.65.camel@richard>
References: <1103213467.9908.65.camel@richard>
Richard Rowell <richard@bowmansystems.com> 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
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: tgl@sss.pgh.pa.us, richard@bowmansystems.com
Subject: Re: Improve performance of query
In-Reply-To: <25884.1103217551@sss.pgh.pa.us>
* 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