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;
(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 
   ->  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

---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Reply via email to