Обсуждение: decrease my query duration

Поиск
Список
Период
Сортировка

decrease my query duration

От
David Carpio
Дата:
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



Re: decrease my query duration

От
bricklen
Дата:

On Thu, Jul 4, 2013 at 5:26 PM, David Carpio <davidc@consistentstate.com> wrote:
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

<snip query & EXPLAIN>

Have a look at https://wiki.postgresql.org/wiki/Slow_Query_Questions and supply a few more details, it will increase your chances of getting helpful answers.
Also, can you supply the EXPLAIN (ANALYZE, BUFFERS) plan instead of the simple EXPLAIN plan?

Re: decrease my query duration

От
Michael Paquier
Дата:
On Fri, Jul 5, 2013 at 10:04 AM, bricklen <bricklen@gmail.com> wrote:
> On Thu, Jul 4, 2013 at 5:26 PM, David Carpio <davidc@consistentstate.com>
> wrote:
> Also, can you supply the EXPLAIN (ANALYZE, BUFFERS) plan instead of the
> simple EXPLAIN plan?
Then it might be interesting that you scan what is wrong with this utility:
http://explain.depesz.com/
--
Michael

Re: decrease my query duration

От
David Carpio
Дата:
On Thu 04 Jul 2013 08:10:45 PM PET, Michael Paquier wrote:
> On Fri, Jul 5, 2013 at 10:04 AM, bricklen <bricklen@gmail.com> wrote:
>> On Thu, Jul 4, 2013 at 5:26 PM, David Carpio <davidc@consistentstate.com>
>> wrote:
>> Also, can you supply the EXPLAIN (ANALYZE, BUFFERS) plan instead of the
>> simple EXPLAIN plan?
> Then it might be interesting that you scan what is wrong with this utility:
> http://explain.depesz.com/
> --
> Michael

Hello

This is the EXPLAIN ANALYZE

 QUERY PLAN


Sort  (cost=726512.79..726512.82 rows=11 width=196) (actual
time=884295.151..884295.178 rows=221 loops=1)
   Sort Key: s0.id, o.guid, ((SubPlan 2))
   Sort Method: quicksort  Memory: 132kB
   ->  Nested Loop Anti Join  (cost=595.80..726512.60 rows=11
width=196) (actual time=212913.939..884287.920 rows=221 loops=1)
   Join Filter: (fl_exclude.spid = ANY (ARRAY[2407, 4, c.spid]))
   ->  Nested Loop Left Join  (cost=595.80..726079.95 rows=17
width=208) (actual time=212912.427..884246.266 rows=221 loops=1)
   ->  Nested Loop Left Join  (cost=595.80..726057.91 rows=17
width=167) (actual time=212912.296..884236.056 rows=221 loops=1)
   Filter: ((s1a3m0.spid = ANY ('{2407,4}'::integer[])) OR (s1a3m0.spid
= c.spid) OR (s1a3m0.spid IS NULL))
   Rows Removed by Filter: 7
   ->  Nested Loop  (cost=595.80..726040.10 rows=17 width=159) (actual
time=212911.997..884213.578 rows=221 loops=1)
   ->  Nested Loop Left Join  (cost=595.80..725977.82 rows=17
width=142) (actual time=212911.597..884187.879 rows=221 loops=1)
   Join Filter: (c.id = s3.sourceid)
   Rows Removed by Join Filter: 16690241
   ->  Nested Loop  (cost=595.80..2357.82 rows=17 width=77) (actual
time=23.459..99.852 rows=171 loops=1)
   Join Filter: (s0.my_table_4_id = c.id)
   ->  Nested Loop  (cost=595.80..2336.88 rows=19 width=69) (actual
time=22.780..77.976 rows=171 loops=1)
   ->  Bitmap Heap Scan on my_table_3 s0  (cost=595.80..1165.50
rows=160 width=49) (actual time=22.629..48.588 rows=171 loops=1)
   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)
   Rows Removed by Filter: 11
   ->  BitmapOr  (cost=595.80..595.80 rows=182 width=0) (actual
time=22.534..22.534 rows=0 loops=1)
   ->  Bitmap Index Scan on my_table_3_pk  (cost=0.00..81.77 rows=25
width=0) (actual time=5.220..5.220 rows=25 loops=1)
   Index Cond: (id = ANY ('{...}'::integer[]))
   ->  Bitmap Index Scan on my_table_3_pk  (cost=0.00..81.77 rows=25
width=0) (actual time=2.278..2.278 rows=25 loops=1)
   Index Cond: (id = ANY ('{...}'::integer[]))
   ->  Bitmap Index Scan on my_table_3_pk  (cost=0.00..81.77 rows=25
width=0) (actual time=3.520..3.520 rows=25 loops=1)
   Index Cond: (id = ANY ('{...}'::integer[]))
   ->  Bitmap Index Scan on my_table_3_pk  (cost=0.00..81.77 rows=25
width=0) (actual time=1.791..1.791 rows=25 loops=1)
   Index Cond: (id = ANY ('{...}'::integer[]))
   ->  Bitmap Index Scan on my_table_3_pk  (cost=0.00..81.77 rows=25
width=0) (actual time=3.178..3.178 rows=25 loops=1)
   Index Cond: (id = ANY ('{...}'::integer[]))
   ->  Bitmap Index Scan on my_table_3_pk  (cost=0.00..81.77 rows=25
width=0) (actual time=3.657..3.657 rows=25 loops=1)
   Index Cond: (id = ANY ('{...}'::integer[]))
   ->  Bitmap Index Scan on my_table_3_pk  (cost=0.00..81.77 rows=25
width=0) (actual time=2.245..2.245 rows=25 loops=1)
   Index Cond: (id = ANY ('{...}'::integer[]))
   ->  Bitmap Index Scan on my_table_3_pk  (cost=0.00..23.11 rows=7
width=0) (actual time=0.634..0.634 rows=7 loops=1)
   Index Cond: (id = ANY ('{...}'::integer[]))
   ->  Index Scan using my_table_51_idx on my_table_5 s2
(cost=0.00..7.31 rows=1 width=20) (actual time=0.160..0.165 rows=1
loops=171)
   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) (actual time=0.096..0.117 rows=1 loops=171)
   Index Cond: (id = s2.my_table_2_id)
   Filter: (retired = 0::numeric)
   ->  Materialize  (cost=0.00..723619.75 rows=1 width=69) (actual
time=4.612..5144.606 rows=97605 loops=171)
   ->  Nested Loop  (cost=0.00..723619.75 rows=1 width=69) (actual
time=788.562..876415.024 rows=97605 loops=1)
   Join Filter: (c.id = ao7.my_table_2_id)
   Rows Removed by Join Filter: 1785384515
   ->  Nested Loop  (cost=0.00..460418.49 rows=18988 width=4) (actual
time=1.638..2911.401 rows=18292 loops=1)
   ->  Index Scan using my_table_52_idx on my_table_5 s7
(cost=0.00..324460.90 rows=16361 width=4) (actual time=0.945..1799.632
rows=18292 loops=1)
   Index Cond: (my_table_1_id = 10832605)
   Filter: ((NOT (SubPlan 8)) OR (SubPlan 9))
   Rows Removed by Filter: 3921
   SubPlan 8
   ->  Index Only Scan using my_table_81_idx on my_table_8 ovd
(cost=0.00..6.96 rows=1 width=0) (actual time=0.044..0.044 ro
   ws=1 loops=22213)
   Index Cond: (my_table_2_id = s7.id)
   Heap Fetches: 22213
   SubPlan 9
   ->  Index Only Scan using my_table_81_idx on my_table_8 ovd
(cost=0.00..6.96 rows=1 width=0) (actual time=0.007..0.007 ro
   ws=1 loops=22213)
   Index Cond: ((my_table_2_id = s7.id) AND (startdate <=
1294617600000::bigint) AND (enddate >= 1294617600000::bigint))
   Heap Fetches: 18292
   ->  Index Scan using my_table_9_idx on my_table_9 ao7
(cost=0.00..8.27 rows=4 width=8) (actual time=0.040..0.055 rows=1
loops=18292)
   Index Cond: (my_table_9_id = s7.id)
   ->  Materialize  (cost=0.00..262631.62 rows=2 width=89) (actual
time=0.002..20.243 rows=97610 loops=18292)
   ->  Nested Loop  (cost=0.00..262631.61 rows=2 width=89) (actual
time=7.016..33482.141 rows=97610 loops=1)
   ->  Nested Loop  (cost=0.00..262616.66 rows=1 width=85) (actual
time=6.734..31695.248 rows=97610 loops=1)
   ->  Nested Loop  (cost=0.00..262602.64 rows=4 width=85) (actual
time=6.728..22212.748 rows=1272302 loops=1)
   ->  Nested Loop  (cost=0.00..260617.71 rows=46 width=36) (actual
time=6.450..5653.369 rows=98279 loops=1)
   ->  Nested Loop  (cost=0.00..199380.20 rows=3996 width=32) (actual
time=1.680..1560.622 rows=272880 loops=1)
   ->  Nested Loop  (cost=0.00..150606.82 rows=16820 width=24) (actual
time=1.530..333.465 rows=17561 loops=1)
   ->  Index Scan using my_table_52_idx on my_table_5 s6
(cost=0.00..16383.86 rows=16820 width=20) (actual time=0.084..42.426
rows=17561 loops=1)
   Index Cond: (my_table_1_id = ...)
   ->  Index Only Scan using my_table_2_pk on my_table_2 o
(cost=0.00..7.97 rows=1 width=4) (actual time=0.013..0.014 rows=1
loops=17561)
   Index Cond: (id = s6.my_table_2_id)
   Heap Fetches: 17561
   ->  Index Scan using my_table_7_idx on my_table_7 s3_a
(cost=0.00..2.81 rows=9 width=8) (actual time=0.017..0.059 rows=16
loops=17561)
   Index Cond: (my_table_7_id = o.id)
   ->  Index Scan using my_table_6_pk on my_table_6 s3
(cost=0.00..15.31 rows=1 width=8) (actual time=0.013..0.013 rows=0
loops=272880)
   Index Cond: (id = s3_a.my_table_6_id)
   Filter: ((relationdefinitionid = ANY ('{...}'::integer[])) AND ((NOT
(SubPlan 10)) OR (SubPlan 11)))
   Rows Removed by Filter: 1
   SubPlan 10
   ->  Index Only Scan using my_table_81_idx on my_table_8 ovd
(cost=0.00..6.96 rows=1 width=0) (actual time=0.005..0.005 rows=0
loops=98279)
   Index Cond: (my_table_2_id = s3.id)
   Heap Fetches: 0
   SubPlan 11
   ->  Index Only Scan using my_table_81_idx on my_table_8 ovd
(cost=0.00..6.96 rows=1 width=0) (never executed)
   Index Cond: ((my_table_2_id = s3.id) AND (startdate <=
1294617600000::bigint) AND (enddate >= 1294617600000::bigint))
   Heap Fetches: 0
   ->  Index Scan using my_table_3_idx on my_table_3 s5
(cost=0.00..43.13 rows=2 width=49) (actual time=0.019..0.158 rows=13
loops=98279)
   Index Cond: (my_table_4_id = o.id)
   Filter: ((retired = 0::numeric) AND ((NOT (SubPlan 6)) OR (SubPlan
7)))
   Rows Removed by Filter: 2
   SubPlan 6
   ->  Index Only Scan using my_table_81_idx on my_table_8 ovd
(cost=0.00..6.96 rows=1 width=0) (actual time=0.005..0.005 rows=0
loops=1476591)
   Index Cond: (my_table_2_id = s5.id)
   Heap Fetches: 502460
   SubPlan 7
   ->  Index Only Scan using my_table_81_idx on my_table_8 ovd
(cost=0.00..6.96 rows=1 width=0) (actual time=0.005..0.005 rows=1
loops=502460)
   Index Cond: ((my_table_2_id = s5.id) AND (startdate <=
1294617600000::bigint) AND (enddate >= 1294617600000::bigint))
   Heap Fetches: 298171
   ->  Index Scan using my_table_2_pk on my_table_2 f5objects
(cost=0.00..3.50 rows=1 width=4) (actual time=0.006..0.007 rows=0
loops=1272302)
   Index Cond: (id = s5.id)
   Filter: (contentsetid = ...)
   Rows Removed by Filter: 1
   ->  Index Scan using my_table_4_pk on my_table_4 c
(cost=0.00..14.94 rows=1 width=4) (actual time=0.015..0.016 rows=1
loops=97610)
   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_table_8 ovd
(cost=0.00..6.96 rows=1 width=0) (actual time=0.006..0.006 rows=0
loops=97610)
   Index Cond: (my_table_2_id = c.id)
   Heap Fetches: 9369
   SubPlan 5
   ->  Index Only Scan using my_table_81_idx on my_table_8 ovd
(cost=0.00..6.96 rows=1 width=0) (actual time=0.006..0.006 rows=1
loops=9369)
   Index Cond: ((my_table_2_id = c.id) AND (startdate <=
1294617600000::bigint) AND (enddate >= 1294617600000::bigint))
   Heap Fetches: 9369
   ->  Index Scan using my_table_2_pk on my_table_2 o  (cost=0.00..3.65
rows=1 width=29) (actual time=0.098..0.098 rows=1 loops=221)
   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) (actual time=0.089..0.092 rows=1
loops=221)
   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) (actual time=0.042..0.042 rows=1
loops=221)
   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) (actual time=0.012..0.012 rows=0
loops=221)
   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)
   Rows Removed by Filter: 1
   Heap Fetches: 228
   SubPlan 1
   ->  Nested Loop  (cost=0.00..14.66 rows=1 width=23) (actual
time=0.040..0.041 rows=1 loops=221)
   ->  Index Scan using my_table_1_pk on my_table_1 fd
(cost=0.00..6.27 rows=1 width=4) (actual time=0.015..0.015 rows=1
loops=221)
   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) (actual time=0.013..0.014 rows=1 loops=221)
   Index Cond: (id = fd.typeid)
   SubPlan 2
   ->  Index Scan using my_table_2_pk on my_table_2  (cost=0.00..8.38
rows=1 width=23) (actual time=0.062..0.063 rows=1 loops=221)
   Index Cond: (id = s3.id)
   SubPlan 3
   ->  Nested Loop  (cost=0.00..14.66 rows=1 width=23) (actual
time=0.028..0.033 rows=1 loops=221)
   ->  Index Scan using my_table_1_pk on my_table_1 fd
(cost=0.00..6.27 rows=1 width=4) (actual time=0.003..0.008 rows=1
loops=221)
   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) (actual time=0.009..0.009 rows=1 loops=214)
   Index Cond: (id = fd.typeid)
   Total runtime: 884385.637 ms

Thank you for your time

David Carpio

Re: decrease my query duration

От
David Johnston
Дата:
David Carpio wrote
> Thank you for your time

You're not likely to get too many if any takers who want to try and decipher
that mess you call a query/explain.  Especially since you've made it pretty
much impossible to read by removing/obfuscating information.  It is not
self-contained and we have no idea what the goal of the query is without
actually reading it.  At minimum you should write a paragraph or two
describing your schema, problem, and what broadly you are trying to
accomplish with the supplied query.

My first instinct is that you table/schema layout simply sucks and that you
are going to have to either re-design that or put considerable effort into
manual caching or turn it into a function so that you can help the planner
out.

Ignore the whole "it runs too slowly" for the time being and create
self-contained example that at least runs and where the query can actually
be read.  In addition maybe try performing surgery on the query so that a
lot of the detail is removed without losing the structure.  That way you and
others can much more quickly view this modified query - even if it doesn't
run - and get a feel for what it is doing and how it goes about doing it.
You'd still need table definitions and sample data even for this.

The whole "I want help but I can't (or won't take the time to) actually tell
you what I am doing" position doesn't usually work for these kinds of
problems.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/decrease-my-query-duration-tp5762622p5762758.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.