X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D284E3B2547 for ; Thu, 16 Dec 2004 17:19:34 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60263-07 for ; Thu, 16 Dec 2004 17:19:24 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 468173B6630 for ; Thu, 16 Dec 2004 17:19:23 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iBGHJCwY025885; Thu, 16 Dec 2004 12:19:12 -0500 (EST) To: Richard Rowell Cc: pgsql-performance@postgresql.org Subject: Re: Improve performance of query In-reply-to: <1103213467.9908.65.camel@richard> References: <1103213467.9908.65.camel@richard> Comments: In-reply-to Richard Rowell message dated "Thu, 16 Dec 2004 10:11:07 -0600" Date: Thu, 16 Dec 2004 12:19:11 -0500 Message-ID: <25884.1103217551@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/206 X-Sequence-Number: 9581 Richard Rowell 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