Hi Noel,
How do I get the explain plan?
This is probably not what you meant:
Runnnig with 'EXPLAIN'
SELECT
TLC.CAPTION AS FIELDNAME,
REGEXP_REPLACE(RCF.CODE, '^([rR][^aA]+)[aA]?$', '\1') AS CODE,
RCF.NAME AS NAME,
RCF.TASK_TYPE AS ACTION,
SRL.DESCRIPTION AS DESCRIPTION,
VISIBLE AS ISDISPLAY,
FALSE 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,
CAST(((CHT.DOMAIN_TYPE = 'java.lang.Double')
AND ((RCF.ENTITY_NAME = 'tsk_normenkader')
AND (LOWER(CHT.COLUMN_NAME) LIKE 'actie_impact%'))) AS BOOLEAN) AS
ISCURRENCY
FROM PUBLIC.VCP_TASKLIST_COLUMN TLC
/* PUBLIC.VCP_TASKLIST_COLUMN.tableScan */
/* WHERE UPPER(TLC.CAPTION) <> 'OPLOSSER'
*/
LEFT OUTER JOIN PUBLIC.VCP_TASKLIST TLT
/* PUBLIC.VCP_TASKLIST.tableScan */
ON TLC.TLT_ID = TLT.ID
LEFT OUTER JOIN PUBLIC.VCP_RULE_CONFIG RCF
/* PUBLIC.VCP_RULE_CONFIG.tableScan */
ON RCF.TASKLIST = TLT.CODE
LEFT OUTER JOIN PUBLIC.VCP_SITE_RULE SRL
/* PUBLIC.VCP_SITE_RULE.tableScan */
ON RCF.CODE = SRL.CODE
LEFT OUTER JOIN PUBLIC.VCP_CHARACTERISTIC_SET CHS
/* PUBLIC.VCP_CHARACTERISTIC_SET.tableScan */
ON TLC.CHS_ID = CHS.ID
LEFT OUTER JOIN PUBLIC.VCP_CHARACTERISTIC_TYPE CHT
/* PUBLIC.VCP_CHARACTERISTIC_TYPE.tableScan */
ON CHS.CHT_ID = CHT.ID
WHERE (RCF.CODE = 'R06408')
AND (UPPER(TLC.CAPTION) <> 'OPLOSSER')
ORDER BY =COLUMN_INDEX
On Friday, March 27, 2015 at 7:19:35 PM UTC+1, Noel Grandin wrote:
>
> What does explain plan say about the query?
>
> On Friday, 27 March 2015, Rob Audenaerde <[email protected]
> <javascript:>> 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.
