pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Meinel <john@johnmeinel.com>
To: patrick ~ <sidsrr@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: vacuum analyze slows sql query
Date: Mon, 08 Nov 2004 13:29:39 -0600
Message-ID: <418FC923.2090704@johnmeinel.com> (raw)
In-Reply-To: <20041108185702.99883.qmail@web52103.mail.yahoo.com>
References: <20041108185702.99883.qmail@web52103.mail.yahoo.com>

patrick ~ wrote:
[...]
> pkk=# explain analyze execute pkk_01(    241 );
>       QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Result  (cost=2.66..2.67 rows=1 width=0) (actual time=2872.211..2872.213
> rows=1 loops=1)
>    InitPlan
>      ->  Limit  (cost=0.00..2.66 rows=1 width=4) (actual
> time=2872.189..2872.189 rows=0 loops=1)
>            ->  Seq Scan on pkk_purchase p0  (cost=0.00..37225.83 rows=13983
> width=4) (actual time=2872.180..2872.180 rows=0 loops=1)
>                  Filter: ((offer_id = $1) AND (((expire_time)::timestamp with
> time zone > now()) OR (expire_time IS NULL) OR (pending = true)) AND
> ((cancel_date IS NULL) OR (pending = true)))
>  Total runtime: 2872.339 ms
> (6 rows)
> 
> Time: 2873.479 ms
> 

[...]

> So, is this the ultimate solution to this issue?
> 
> --patrick

It's not so much that correlation is < 0.5. It sounds like you're 
running into the same issue that I ran into in the past. You have a 
column with lots of repeated values, and a few exceptional ones. Notice 
this part of the query:
->  Seq Scan on pkk_purchase p0  (cost rows=13983) (actual rows=0)

For a general number, it thinks it might return 14,000 rows, hence the 
sequential scan. Before you do ANALYZE, it uses whatever defaults exist, 
which are probably closer to reality.

The problem is that you probably have some values for pkk_purchase where 
it could return 14,000 rows (possibly much much more). And for those, 
seq scan is the best plan. However, for the particular value that you 
are testing, there are very few (no) entries in the table.

With a prepared statement (or a function) it has to determine ahead of 
time what the best query is without knowing what value you are going to 
  ask for.

Lets say for a second that you manage to trick it into using index scan, 
and then you actually call the function with one of the values that 
returns 1,000s of rows. Probably it will take 10-100 times longer than 
if it used a seq scan.

So what is the solution? The only one I'm aware of is to turn your 
static function into a dynamic one.

So somewhere within the function you build up a SQL query string and 
call EXECUTE str. This forces the query planner to be run every time you 
call the function. This means that if you call it will a "nice" value, 
you will get the fast index scan, and if you call it with a "bad" value, 
it will switch back to seq scan.

The downside is you don't get much of a benefit from using as stored 
procedure, as it has to run the query planner all the time (as though 
you issue the query manually each time.) But it still might be better 
for you in the long run.

Example:

instead of

create function test(int) returns int as '
declare
   x alias for $1;
   int y;
begin
   select into y ... from ... where id=x limit ...;
   return y;
end
';

use this format

create function test(int) returns int as '
declare
   x alias for $1;
   int y;
begin
   EXECUTE ''select into y ... from ... where id=''
	||quote_literal(x)
	|| '' limit ...'';
   return y;
end;
';

I think that will point you in the right direction.

John
=:->

Attachments:

  signature.asc (application/pgp-signature, 255B) download

view thread (17+ messages)  latest in thread

Message-ID: <418FC923.2090704@johnmeinel.com>
Permalink:  ../418FC923.2090704@johnmeinel.com/
Also on:    postgresql.org/message-id/418FC923.2090704@johnmeinel.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: john@johnmeinel.com, sidsrr@yahoo.com, tgl@sss.pgh.pa.us
  Subject: Re: vacuum analyze slows sql query
  In-Reply-To: <418FC923.2090704@johnmeinel.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