decrease my query duration

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 ) THEN 0 ELSE 0 END) ORDER BY
c1 NULLS FIRST,c2 NULLS FIRST,c5 NULLS FIRST;

and this is its execution plan

*QUERY PLAN *
;
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