What does explain plan say about the query?
On Friday, 27 March 2015, Rob Audenaerde <[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]
> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
> .
> To post to this group, send email to [email protected]
> <javascript:_e(%7B%7D,'cvml','[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.
