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