pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Richard Rowell <richard@bowmansystems.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improve performance of query
Date: Thu, 16 Dec 2004 17:15:29 +0000
Message-ID: <41C1C2B1.90000@archonet.com> (raw)
In-Reply-To: <1103213467.9908.65.camel@richard>
References: <1103213467.9908.65.camel@richard>

Richard Rowell wrote:
> 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.  My concern is that this
> data set is rather "small" by our applications standards.  It is not
> unusual for the da_answer table to have 2-4 million records.  I'm
> worried that if this very small data set is taking 5 seconds, then a
> "regular sized" data set will take far too long.
> 
> I originally thought the NOT EXISTS on the
> "da_data_restrict_except_open" table was killing performance, but the
> query took the exact same amount of time after I deleted all rows from
> this table.  Note that the hard-coded 999999999.0, and 4000 parameters,
> as well as the parameter to svp_getparentproviders are the three
> variables that change from one run of this query to the next.
> 
> I'm using Postgres 7.4.5 as packaged in Debian.  shared_buffers is set
> to 57344 and sort_mem=4096.

That shared_buffers value sounds too large for 1GB RAM - rewind to 10000 
say. Also make sure you've read the "performance tuning" article at:
   http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

> I have included an EXPLAIN ANALYZE, relevant table counts, and relevant
> indexing information.  If anyone has any suggestions on how to improve
> performance....  TIA!

I think it's the function call(s).

> SELECT tab.answer_id, client_id, question_id, recordset_id,
> date_effective, virt_field_name
> FROM
> (
>     SELECT a.uid AS answer_id, a.client_id, a.question_id, recordset_id,
> date_effective
>     FROM da_answer a
>     WHERE a.date_effective <= 9999999999.0
>     AND a.inactive != 1
>     AND
>     (
>             5000 = 4000 
>             OR
>             (EXISTS (SELECT * FROM svp_getparentproviderids(1) WHERE
> svp_getparentproviderids = a.provider_id))
>     )
...
>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)

Here it's running 21,089 loops around your function. Each one isn't 
costing much, but it's the total that's killing you I think. It might be 
possible to mark the function STABLE or such, depending on what it does 
- see http://www.postgresql.org/docs/7.4/static/sql-createfunction.html

-- 
   Richard Huxton
   Archonet Ltd



view thread (6+ messages)  latest in thread

Message-ID: <41C1C2B1.90000@archonet.com>
Permalink:  ../41C1C2B1.90000@archonet.com/
Also on:    postgresql.org/message-id/41C1C2B1.90000@archonet.com

 · 

reply

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: dev@archonet.com, richard@bowmansystems.com
  Subject: Re: Improve performance of query
  In-Reply-To: <41C1C2B1.90000@archonet.com>

* 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