|
Hello All I am newbie in this forum that's why I will be very grateful if would be able to help me and if you need some additional information please let me know. I have a query that I would like decrease its duration SELECT s0.SEARCH AS c0, s0.id AS c0c1, s0.id AS c1, s1.name AS c2, s1.subtypeid AS c2c1, s1a3m1.SEARCH AS c3, s1a3m1.id AS c3c1, s2.SEARCH AS c4, s2.id AS c4c1, ( SELECT o.name FROM my_table_1 fd, my_table_2 o WHERE fd.typeid = o.id AND fd.id = s2.my_table_1_id ) AS c4c2, ( SELECT name FROM my_table_2 WHERE my_table_2.id = s3.id ) AS c5, s5.SEARCH AS c6, s5.id AS c6c1, s6.SEARCH AS c7, s6.id AS c7c1, ( SELECT o.name FROM my_table_1 fd, my_table_2 o WHERE fd.typeid = o.id AND fd.id = s6.my_table_1_id ) AS c7c2 FROM my_table_3 s0 JOIN ( SELECT o.name AS name, c.id, c.last_name, o.subtypeid AS subtypeid, c.spid, c.typeid FROM my_table_4 c, my_table_2 o WHERE c.id = o.id ) s1 ON s0.my_table_4_id = s1.id AND s1.last_name = '' JOIN my_table_5 s2 ON s1.id = z s2.my_table_2_id AND s2.my_table_1_id = 0 LEFT OUTER JOIN my_table_6 s3 LEFT OUTER JOIN mY_table_7 s3_a ON s3.id = s3_a.my_table_6_id JOIN ( SELECT o.name AS name, c.id, c.last_name, o.subtypeid AS subtypeid, c.spid, c.typeid FROM my_table_4 c, WHERE c.id = o.id ) s4 ON s3_a.my_table_7_id = s4.id AND ( NOT EXISTS ( SELECT * FROM my_table_8 ovd WHERE s4.id = ovd.my_table_2_id ) OR EXISTS ( SELECT * FROM my_table_8 ovd WHERE s4.id = ovd.my_table_2_id AND '' BETWEEN ovd.startdate AND ovd.enddate ) ) AND s4.last_name = '' JOIN my_table_3 s5 ON s4.id = s5.my_table_4_id JOIN my_table_2 f5objects ON s5.id = f5objects.id AND f5objects.contentsetid = '' AND ( NOT EXISTS ( SELECT * FROM my_table_8 ovd WHERE s5.id = ovd.my_table_2_id ) OR EXISTS ( SELECT * FROM my_table_8 ovd WHERE s5.id = ovd.my_table_2_id AND '' BETWEEN ovd.startdate AND ovd.enddate ) ) AND s5.last_name = '' JOIN my_table_5 s6 ON s4.id = s6.my_table_2_id AND s6.my_table_1_id = 0 JOIN my_table_9 ao7 JOIN my_table_5 s7 ON s7.id = ao7.associatedobjectid ON s4.id = ao7.my_table_2_id AND s7.my_table_1_id = 0 AND ( NOT EXISTS ( SELECT * FROM my_table_8 ovd WHERE s7.id = ovd.my_table_2_id ) OR EXISTS ( SELECT * FROM my_table_8 ovd WHERE s7.id = ovd.my_table_2_id AND '' BETWEEN ovd.startdate AND ovd.enddate ) ) ON s1.id = s3.sourceid AND s3.relationdefinitionid IN ( ... ) AND ( NOT EXISTS ( SELECT * FROM my_table_8 ovd WHERE s3.id = ovd.my_table_2_id ) OR EXISTS ( SELECT * FROM my_table_8 ovd WHERE s3.id = ovd.my_table_2_id AND '' BETWEEN ovd.startdate AND ovd.enddate ) ) LEFT OUTER JOIN my_table_10 s1a3m0 ON s1.id = s1a3m0.my_table_4_id LEFT OUTER JOIN my_table_3 s1a3m1 ON s1a3m0.termid = s1a3m1.id WHERE s0.last_name = '' AND ( s0.id IN ( ... ) OR s0.id IN ( ... ) OR s0.id IN ( ... ) OR s0.id IN ( ... ) OR s0.id IN ( ... ) OR s0.id IN ( ... ) OR s0.id IN ( ... ) OR s0.id IN ( ... ) ) AND ( s1a3m0.spid IN ( 0, 0, s1.spid ) OR s1a3m0.spid IS NULL ) AND NOT EXISTS ( SELECT * FROM my_table_10 fl_exclude WHERE fl_exclude.my_table_4_id = s1a3m0.my_table_4_id AND fl_exclude.spid IN ( 0, 0, s1.spid ) AND CASE fl_exclude.spid WHEN 0 THEN 0 WHEN 0 THEN 0 ELSE 0 END > CASE s1a3m0.spid WHEN 0 THEN 0 WHEN 0 THEN 0 ELSE 0 END ) ORDER BY c1 NULLS FIRST, c2 NULLS FIRST, c5 NULLS FIRST; and this is its execution plan QUERY PLAN &! nbsp; ; Sort (cost=726512.79..726512.82 rows=11 width=196) Sort Key: s0.id, o.name, ((SubPlan 2)) -> Nested Loop Anti Join (cost=595.80..726512.60 rows=11 width=196) Join Filter: (fl_exclude.spid = ANY (ARRAY[2407, 4, c.spid])) -> Nested Loop Left Join (cost=595.80..726079.95 rows=17 width=208) -> Nested Loop Left Join (cost=595.80..726057.91 rows=17 width=167) Filter: ((s1a3m0.spid = ANY ('{2407,4}'::integer[])) OR (s1a3m0.spid = c.spid) OR (s1a3m0.spid IS NULL)) -> Nested Loop (cost=595.80..726040.10 rows=17 width=159) -> Nested Loop Left Join (cost=595.80..725977.82 rows=17 width=142) Join Filter: (c.id = s3.sourceid) -> Nested Loop (cost=595.80..2357.82 rows=17 width=77) Join Filter: (s0.my_table_4_id = c.id) -> Nested Loop (cost=595.80..2336.88 rows=19 width=69) -> Bitmap Heap Scan on my_table_3 s0 (cost=595.80..1165.50 rows=160 width=49) Recheck Cond: ((id = ANY ('{...}'::integer[])) OR (id = ANY ('{...}'::integer[])) OR (id = ANY ('{...}'::integer[])) OR (id = ANY ('{...}'::integer[])) OR (id = ANY ('{...}'::integer[])) OR (id = ANY ('{...}'::integer[])) OR (id = ANY ('{..}'::integer[])) OR (id = ANY ('{...}'::integer[]))) Filter: (retired = 0::numeric) -> BitmapOr (cost=595.80..595.80 rows=182 width=0) -> Bitmap Index Scan on my_table_3_pk (cost=0.00..81.77 rows=25 width=0) Index Cond: (id = ANY ('{...}'::integer[])) -> Bitmap Index Scan on my_table_3_pk (cost=0.00..81.77 rows=25 width=0) Index Cond: (id = ANY ('{...}'::integer[])) -> Bitmap Index Scan on my_table_3_pk (cost=0.00..81.77 rows=25 width=0) Index Cond: (id = ANY ('{...}'::integer[])) -> Bitmap Index Scan on my_table_3_pk (cost=0.00..81.77 rows=25 width=0) Index Cond: (id = ANY ('{...}'::integer[])) -> Bitmap Index Scan on my_table_3_pk (cost=0.00..81.77 rows=25 width=0) Index Cond: (id = ANY ('{...}'::integer[])) -> Bitmap Index Scan on my_table_3_pk (cost=0.00..81.77 rows=25 width=0) Index Cond: (id = ANY ('{...}'::integer[])) -> Bitmap Index Scan on my_table_3_pk (cost=0.00..81.77 rows=25 width=0) Index Cond: (id = ANY ('{...}'::integer[])) -> Bitmap Index Scan on my_table_3_pk (cost=0.00..23.11 rows=7 width=0) Index Cond: (id = ANY ('{...}'::integer[])) -> Index Scan using my_table51_idx on my_table_5 s2 (cost=0.00..7.31 rows=1 width=20) Index Cond: ((my_table_2_id = s0.my_table_4_id) AND (my_table_1_id = 5435171)) -> Index Scan using my_table_4_pk on my_table_4 c (cost=0.00..1.09 rows=1 width=8) Index Cond: (id = s2.my_table_2_id) Filter: (retired = 0::numeric) -> Materialize (cost=0.00..723619.75 rows=1 width=69) -> Nested Loop (cost=0.00..723619.75 rows=1 width=69) Join Filter: (c.id = ao7.my_table_2_id) -> Nested Loop (cost=0.00..460418.49 rows=18988 width=4) -> Index Scan using my_table52_idx on my_table5 s7 (cost=0.00..324460.90 rows=16361 width=4) Index Cond: (my_table_1_id = 10832605) Filter: ((NOT (SubPlan 8)) OR (SubPlan 9)) SubPlan 8 -> Index Only Scan using my_table_81_idx on my_table8 ovd (cost=0.00..6.96 rows=1 width=0) Index Cond: (my_table_2_id = s7.id) SubPlan 9 -> Index Only Scan using my_table_81_idx on my_table8 ovd (cost=0.00..6.96 rows=1 width=0) Index Cond: ((my_table_2_id = s7.id) AND (startdate <= 1294617600000::bigint) AND (enddate >= 1294617600000::bigint)) -> Index Scan using my_table_9_idx on my_table_9 ao7 (cost=0.00..8.27 rows=4 width=8) Index Cond: (my_table_9_id = s7.id) -> Materialize (cost=0.00..262631.62 rows=2 width=89) -> Nested Loop (cost=0.00..262631.61 rows=2 width=89) -> Nested Loop (cost=0.00..262616.66 rows=1 width=85) -> Nested Loop (cost=0.00..262602.64 rows=4 width=85) -> Nested Loop (cost=0.00..260617.71 rows=46 width=36) -> Nested Loop (cost=0.00..199380.20 rows=3996 width=32) -> Nested Loop (cost=0.00..150606.82 rows=16820 width=24) -> Index Scan using my_table52_idx on my_table5 s6 (cost=0.00..16383.86 rows=16820 width=20) Index Cond: (my_table_1_id = 10868152) -> Index Only Scan using my_table_2_pk on my_table_2 o (cost=0.00..7.97 rows=1 width=4) Index Cond: (id = s6.my_table_2_id) -> Index Scan using my_table_7_idx on my_table_7 s3_a (cost=0.00..2.81 rows=9 width=8) Index Cond: (targetid = o.id) -> Index Scan using my_table_6_pk on my_table_6 s3 (cost=0.00..15.31 rows=1 width=8) Index Cond: (id = s3_a.my_table_6_id) Filter: ((relationdefinitionid = ANY ('{16018218,16018217,16018219,16018216}'::integer[])) AND ((NOT (SubPlan 10)) OR (SubPlan 11))) SubPlan 10 -> Index Only Scan using my_table_81_idx on my_table8 ovd (cost=0.00..6.96 rows=1 width=0) Index Cond: (my_table_2_id = s3.id) SubPlan 11 -> Index Only Scan using my_table_81_idx on my_table8 ovd (cost=0.00..6.96 rows=1 width=0) Index Cond: ((my_table_2_id = s3.id) AND (startdate <= 1294617600000::bigint) AND (enddate >= 1294617600000::bigint)) -> Index Scan using my_table_3_idx on my_table_3 s5 (cost=0.00..43.13 rows=2 width=49) Index Cond: (my_table_4_id = o.id) Filter: ((retired = 0::numeric) AND ((NOT (SubPlan 6)) OR (SubPlan 7))) SubPlan 6 -> Index Only Scan using my_table_81_idx on my_table8 ovd (cost=0.00..6.96 rows=1 width=0) Index Cond: (my_table_2_id = s5.id) SubPlan 7 -> Index Only Scan using my_table_81_idx on my_table8 ovd (cost=0.00..6.96 rows=1 width=0) Index Cond: ((my_table_2_id = s5.id) AND (startdate <= 1294617600000::bigint) AND (enddate >= 1294617600000::bigint)) -> Index Scan using my_table_2_pk on my_table_2 f5objects (cost=0.00..3.50 rows=1 width=4) Index Cond: (id = s5.id) Filter: (contentsetid = 10821468) -> Index Scan using my_table_4_pk on my_table_4 c (cost=0.00..14.94 rows=1 width=4) Index Cond: (id = o.id) Filter: ((retired = 0::numeric) AND ((NOT (SubPlan 4)) OR (SubPlan 5))) SubPlan 4 -> Index Only Scan using my_table_81_idx on my_table8 ovd (cost=0.00..6.96 rows=1 width=0) Index Cond: (my_table_2_id = c.id) SubPlan 5 -> Index Only Scan using my_table_81_idx on my_table8 ovd (cost=0.00..6.96 rows=1 width=0) Index Cond: ((my_table_2_id = c.id) AND (startdate <= 1294617600000::bigint) AND (enddate >= 1294617600000::bigint)) -> Index Scan using my_table_2_pk on my_table_2 o (cost=0.00..3.65 rows=1 width=29) Index Cond: (id = c.id) -> Index Scan using my_table_10_pk on my_table_10 s1a3m0 (cost=0.00..1.03 rows=1 width=12) Index Cond: (c.id = my_table_4_id) -> Index Scan using my_table_3_pk on my_table_3 s1a3m1 (cost=0.00..1.29 rows=1 width=45) Index Cond: (s1a3m0.my_table_3_id = id) -> Index Only Scan using my_table_10_pk on my_table_10 fl_exclude (cost=0.00..1.04 rows=1 width=8) Index Cond: (my_table_4_id = s1a3m0.my_table_4_id) Filter: (CASE spid WHEN 2407 THEN 2 WHEN 4 THEN 1 ELSE 0 END > CASE s1a3m0.spid WHEN 2407 THEN 2 WHEN 4 THEN 1 ELSE 0 END) SubPlan 1 -> Nested Loop (cost=0.00..14.66 rows=1 width=23) -> Index Scan using my_table_1_pk on my_table_1 fd (cost=0.00..6.27 rows=1 width=4) Index Cond: (id = s2.my_table_1_id) -> Index Scan using my_table_2_pk on my_table_2 o (cost=0.00..8.38 rows=1 width=27) Index Cond: (id = fd.typeid) SubPlan 2 -> Index Scan using my_table_2_pk on my_table_2 o (cost=0.00..8.38 rows=1 width=23) Index Cond: (id = s3.id) SubPlan 3 -> Nested Loop (cost=0.00..14.66 rows=1 width=23) -> Index Scan using my_table_1_pk on my_table_1 fd (cost=0.00..6.27 rows=1 width=4) Index Cond: (id = s6.my_table_1_id) -> Index Scan using my_table_2_pk on my_table_2 o (cost=0.00..8.38 rows=1 width=27) Index Cond: (id = fd.typeid) (120 rows) Is there another way to do this query faster? Thank you for the tips, David Carpio |
