1

Topic: with the plan - What to twist?

SELECT dg2.dev_group_type_id, dg2.dev_group_id, dg2.dev_group_name, dg2.dev_group_l, dg2.dev_group_r;
CASE WHEN dg2.dev_group_parentid = 0 THEN (- 1 * dg2.dev_group_type_id) ELSE dg2.dev_group_parentid END AS dev_group_parentid, dg2.dev_group_date_created;
dev_group_auto
, map.map_id
FROM device_group dg1
JOIN device_group_type on dg1.dev_group_type_id = device_group_type.dev_group_type_id
JOIN device_group dg2 ON dg1.dev_group_type_id = dg2.dev_group_type_id AND dg1.dev_group_l> = dg2.dev_group_l AND
dg1.dev_group_l <= dg2.dev_group_r
left join map on dg2.dev_group_id = map.dev_group_id and dg2.dev_group_type_id = map.dev_group_type_id
-- where dg2.dev_group_parentid = 16
GROUP BY dg2.dev_group_type_id, dg2.dev_group_id, dg2.dev_group_name, dg2.dev_group_l, dg2.dev_group_r;
CASE WHEN dg2.dev_group_parentid = 0 THEN (- 1 * dg2.dev_group_type_id) ELSE dg2.dev_group_parentid END, dg2.dev_group_date_created, dev_group_auto, map_id

The plan with usage where is fulfilled 15 sec and produces 3000 lines
The plan without usage where is fulfilled 0 sec and produces 20000 lines
device_group_type - 13 rows
device_group - 20K
map - 0

2

Re: with the plan - What to twist?

To twist an index idx_device_group_tid - to add include (dev_group_parentid)

3

Re: with the plan - What to twist?

invm;
Thanks, really