[PERFORM] ill-planned queries inside a stored procedure
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 ---(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
Re: [PERFORM] ill-planned queries inside a stored procedure
I use EXECUTE inside a stored procedure for just this purpose. This is not the same as PREPARE/EXECUTE, it lets you send an arbitrary string as SQL within the procedure. You have to write the query text on the fly in the procedure, which can be a little messy with quoting and escaping. Gaetano Mendola [EMAIL PROTECTED] wrote .. Hi all, do you know any clean workaround at ill-planned queries inside a stored procedure? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] ill-planned queries inside a stored procedure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: | Gaetano Mendola [EMAIL PROTECTED] wrote .. | |Hi all, |do you know any clean workaround at ill-planned queries inside a stored |procedure? | I use EXECUTE inside a stored procedure for just this purpose. This is | not the same as PREPARE/EXECUTE, it lets you send an arbitrary string as | SQL within the procedure. You have to write the query text on the fly in | the procedure, which can be a little messy with quoting and escaping. | Yes I knew, I wrote clean workaround :-) I hate write in function piece of code like this: ~ [...] ~ my_stm := ''SELECT '' || my_operation || ''( '' || a_id_transaction; ~ my_stm := my_stm || '', '' || a_id_contract; ~ my_stm := my_stm || '', '' || quote_literal(a_date) || '') AS res''; ~ FOR my_record IN EXECUTE my_stm LOOP ~ IF my_record.res 0 THEN ~ RETURN my_record.res; ~ END IF; ~ EXIT; ~ END LOOP; ~ [...] note also that useless loop that is needed to retrieve the value! Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBMLRE7UpzwH2SGd4RAv0TAJ9+IokZjaXIhgV5dOH86FCvzSnewQCgwqxD nuW9joHmPxOnlRWrvhsKaag= =Axb7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
