Skip site navigation (1) Skip section navigation (2)

Re: Improve performance of query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Rowell <richard(at)bowmansystems(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Improve performance of query
Date: 2004-12-16 17:19:11
Message-ID: 25884.1103217551@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Richard Rowell <richard(at)bowmansystems(dot)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

In response to

pgsql-performance by date

Next:From: John A MeinelDate: 2004-12-16 17:24:26
Subject: Re: Improve performance of query
Previous:From: Richard HuxtonDate: 2004-12-16 17:15:29
Subject: Re: Improve performance of query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group