X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 763485E46F4 for ; Sat, 28 Aug 2004 08:14:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48496-10 for ; Sat, 28 Aug 2004 11:14:48 +0000 (GMT) Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227]) by svr1.postgresql.org (Postfix) with ESMTP id 601415E3631 for ; Sat, 28 Aug 2004 08:14:42 -0300 (ADT) Received: from nts-03.mm.eutelsat.org (localhost [127.0.0.1]) by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i7SBDoX12211 for ; Sat, 28 Aug 2004 13:13:50 +0200 Received: from [127.0.0.1] (accesspoint.mm.eutelsat.org [194.214.173.4]) by nts-03.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i7SBEfV27430 for ; Sat, 28 Aug 2004 13:14:41 +0200 Message-ID: <4130691E.7090603@bigfoot.com> Date: Sat, 28 Aug 2004 13:14:38 +0200 From: Gaetano Mendola User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: ill-planned queries inside a stored procedure X-Enigmail-Version: 0.85.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=TO_ADDRESS_EQ_REAL X-Spam-Level: X-Archive-Number: 200408/411 X-Sequence-Number: 8047 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