Re: [PERFORM] Current query of the PL/pgsql procedure.
On Mon, 2013-12-16 at 11:42 +, Yuri Levinsky wrote: Dear Depesz, This is very problematic solution: I have to change whole!!! my code to put appropriate comment with query text before any query execution. In addition I would like to know current execution plan, that seems to be impossible. This is very hard limitation let's say. In case of production issue I'll just unable to do it: the issue already happening, I can't stop procedure and start code change. James, I saw your reply: I see the function is running, it's just not clear that exactly and how this function doing. This blog post (http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Profiling-PL/pgsql-functions) can probably help you profiling your PL/pgsql functions without modifying them. I'm interested in any comments you can have on the log_functions hook function. Regards. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Re: Problem with slow query with WHERE conditions with OR clause on primary keys
kolsze...@gmail.com wrote Thanx for your answer My example is trivial because i want to show strange (for me) postgres behavior with dealing with primary keys (extreme example), in real situation user put search condition e.g. Panas and this generates query ... where gd.other_code like 'Panas%' OR g.code like 'Panas%' .. both columns has very good indexes and selectivity for like 'Panas%' ... I have experience from Oracle with this type of queries, and Oracle have no problem with it, executes select on index on other_code from gd and join g in next step executes select on index on code from g and join gd and this two results are connected in last step (like union) very fast on minimal cost and in my opinion read whole huge tables only for 10 rows in result where conditions are very good ... is strange I suppose the equivalent query that you'd want would be: SELECT ... FROM gd JOIN gd_data USING (id_gd) WHERE id_gd IN ( SELECT id_gd FROM gd WHERE ... UNION ALL -distinct not required in this situation SELECT id_gd FROM gd_data WHERE ... ) --ignoring NULL implications It does make sense conceptually... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-slow-query-with-WHERE-conditions-with-OR-clause-on-primary-keys-tp5782803p5783942.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Problem with slow query with WHERE conditions with OR clause on primary keys
On 12/11/2013 12:30 AM, Krzysztof Olszewski wrote: select g.gd_index, gd.full_name from gd g join gd_data gd on (g.id_gd = gd.id_gd) where gd.id_gd_data = OR g.id_gd = ; Have you tried writing the query to filter on gd.id_gd rather than g.id_gd? I am not sure if the query planner will realize that it can replace g.id_gd with gd.id_gd in the where clause. select g.gd_index, gd.full_name from gd g join gd_data gd on (g.id_gd = gd.id_gd) where gd.id_gd_data = OR gd.id_gd = ; -- Andreas Karlsson -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
