From: | Gaetano Mendola <mendola(at)bigfoot(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | ill-planned queries inside a stored procedure |
Date: | 2004-08-28 11:14:38 |
Message-ID: | 4130691E.7090603@bigfoot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gaetano Mendola | 2004-08-28 11:39:31 | Re: Equivalent praxis to CLUSTERED INDEX? |
Previous Message | Christopher Kings-Lynne | 2004-08-28 08:50:26 | Re: Why those queries do not utilize indexes? |