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>
Subject: ill-planned queries inside a stored procedure
Date: Sat, 28 Aug 2004 13:14:38 +0200
Message-ID: <4130691E.7090603@bigfoot.com> (raw)

Hi all,
do you know any clean workaround at ill-planned queries inside a stored procedure?
Let me explain with an example:


empdb=# select count(*) from user_logs;
   count
---------
  5223837
(1 row)

empdb=# select count(*) from user_logs where id_user = 5024;
  count
--------
  239453
(1 row)

empdb=# explain analyze select login_time from user_logs where id_user = 5024 order by id_user_log desc limit 1;
                                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..22.62 rows=1 width=12) (actual time=3.921..3.922 rows=1 loops=1)
    ->  Index Scan Backward using user_logs_pkey on user_logs  (cost=0.00..5355619.65 rows=236790 width=12) (actual time=3.918..3.918 rows=1 loops=1)
          Filter: (id_user = 5024)
  Total runtime: 3.963 ms
(4 rows)


same select in a prepared query ( I guess the stored procedure use same plan ):

empdb=# explain analyze execute test(5024);
                                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=759.60..759.61 rows=1 width=12) (actual time=45065.755..45065.756 rows=1 loops=1)
    ->  Sort  (cost=759.60..760.78 rows=470 width=12) (actual time=45065.748..45065.748 rows=1 loops=1)
          Sort Key: id_user_log
          ->  Index Scan using idx_user_user_logs on user_logs  (cost=0.00..738.75 rows=470 width=12) (actual time=8.936..44268.087 rows=239453 loops=1)
                Index Cond: (id_user = $1)
  Total runtime: 45127.256 ms
(6 rows)


There is a way to say: replan this query at execution time ?


Regards
Gaetano Mendola








view thread (3+ messages)  latest in thread

Message-ID: <4130691E.7090603@bigfoot.com>
Permalink:  ../4130691E.7090603@bigfoot.com/
Also on:    postgresql.org/message-id/4130691E.7090603@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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mendola@bigfoot.com
  Subject: Re: ill-planned queries inside a stored procedure
  In-Reply-To: <4130691E.7090603@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