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
            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                                                            
                                                                                                                                                                       ;                          
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