pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gaetano Mendola <mendola@bigfoot.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Andriy Tkachuk <ant@imt.com.ua>
Subject: Re: runtime of the same query in function differs on 2 degree!
Date: Fri, 03 Oct 2003 01:52:46 +0200
Message-ID: <3F7CBA4E.6020302@bigfoot.com> (raw)
In-Reply-To: <20031002163005.N53420-100000@pool.imt.com.ua>
References: <20031002163005.N53420-100000@pool.imt.com.ua>

Andriy Tkachuk wrote:

> Hi folks.
> 
> What's wrong with planner that executes my query in function?:
> (i mean no explanation but runtime)
> 
> 
> tele=# EXPLAIN analyze select calc_total(6916799, 1062363600, 1064955599);
>                                         QUERY PLAN
> ------------------------------------------------------------------------------------------
>  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=36919.37..36919.37 rows=1 loops=1)
>  Total runtime: 36919.40 msec
>  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> 
> tele=# \df+ calc_total
> ...
> declare
>         usr alias for $1;
>         d1 alias for $2;
>         d2   alias for $3;
>         res integer;
> begin
>         select sum(cost) into res
>                 from bills where
>                         (parent(user_id) = usr or user_id = usr)
>                         and dat >= d1 and dat < d2;
>         if res is not null then
>                 return res;
>         else
>                 return 0;
>         end if;
> end;

You didn't wrote the type of d1 and d2, I had your same problem:

declare
    a_user alias for $1;
    res INTEGER;
begin
          select cost into res
          from my_table
          where login = a_user;

	......
end;

the problem was that login was a VARCHAR and a_user was a TEXT so
the index was not used, was enough cast a_user::varchar;


I believe that your dat, d1, d2 are not "index" comparable.


Gaetano













view thread (4+ messages)  latest in thread

Message-ID: <3F7CBA4E.6020302@bigfoot.com>
Permalink:  ../3F7CBA4E.6020302@bigfoot.com/
Also on:    postgresql.org/message-id/3F7CBA4E.6020302@bigfoot.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mendola@bigfoot.com, ant@imt.com.ua
  Subject: Re: runtime of the same query in function differs on 2 degree!
  In-Reply-To: <3F7CBA4E.6020302@bigfoot.com>
  References: <20031002163005.N53420-100000@pool.imt.com.ua> <3F7CBA4E.6020302@bigfoot.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