Also have a look at the performance tuning section of our documentation. Would be interesting to have a stack profile of that query.
On Friday, 27 March 2015, Noel Grandin <[email protected]> wrote: > What does explain plan say about the query? > > On Friday, 27 March 2015, Rob Audenaerde <[email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>> wrote: > >> Hi all, >> >> I have a big join with some regexp_replace which seems to take 100% cpu >> without printing any debug info. It takes over 30 minutes to run (then I >> killed the process) >> >> I create an in-memory database like this to get the system out info: >> >> >> jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=PostgreSQL;TRACE_LEVEL_SYSTEM_OUT=3" >> >> I load a set of tables (that I got from pg_dump). I have a rather >> difficult query: >> >> ----- >> select tlc.caption as fieldname, >> regexp_replace(rcf.code, '^([rR]\d+)\D*$', '\1') as code, >> rcf.name as "name", >> rcf.task_type as "action", >> srl.description as "description", >> visible as isdisplay, >> 'false'::boolean as iskey, >> tlc.column_format as format, >> (case rcf.status when '5. Geaccepteerd' then 'ACCEPTED' when '4. Nieuwe >> controle' then 'NEW' else 'TEST' end) as "rule_status", >> (rcf.entity_name='tsk_normenkader' and lower(cht.column_name) like >> 'actie_impact%' and cht.domain_type = 'java.lang.Double')::boolean as >> iscurrency >> from >> vcp_tasklist_column tlc >> left join >> vcp_tasklist tlt on (tlc.tlt_id = tlt.id) >> left join >> vcp_rule_config rcf on rcf.tasklist = tlt.code >> left join >> vcp_site_rule srl on rcf.code = srl.code >> left join >> vcp_characteristic_set chs on (tlc.chs_id = chs.id) >> left join >> vcp_characteristic_type cht on (chs.cht_id = cht.id) >> where >> rcf.code = 'R00010' >> AND >> UPPER(tlc.caption) != UPPER('oplosser') >> order by >> column_index >> ----- >> >> H2 version is 1.4.186 >> >> This query runs in Postgresql in under a few seconds (cold from start). >> In H2 it takes forever. Is there anything I can do to find out where this >> query is going awry? I cannot post all data here, but if any devs are >> interested in the data, please contact me, and I figure out a way to get it >> to you. >> >> Thanks! >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
