explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9nq3

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 376,304.888 ↓ 0.0 0 1

Unique (cost=22,750,034.51..22,750,034.64 rows=26 width=8) (actual time=376,304.888..376,304.888 rows=0 loops=1)

2. 0.052 376,304.887 ↓ 0.0 0 1

Sort (cost=22,750,034.51..22,750,034.57 rows=26 width=8) (actual time=376,304.887..376,304.887 rows=0 loops=1)

  • Sort Key: pcy.id
  • Sort Method: quicksort Memory: 25kB
3. 76,428.125 376,304.835 ↓ 0.0 0 1

Hash Right Join (cost=53,378.22..22,750,033.90 rows=26 width=8) (actual time=376,304.835..376,304.835 rows=0 loops=1)

  • Hash Cond: ((pav.attribute_id = i.id) AND (pav.policy_id = pcy.id))
  • Filter: (pav.id IS NULL)
  • Rows Removed by Filter: 8920
4. 42,690.979 291,076.146 ↑ 1.0 751,098,550 1

Append (cost=0.00..17,063,402.74 rows=751,100,374 width=24) (actual time=9.855..291,076.146 rows=751,098,550 loops=1)

5. 6,793.178 6,793.178 ↓ 1.0 24,256,565 1

Seq Scan on pcy_attribute_value_1 pav (cost=0.00..497,695.64 rows=24,256,564 width=24) (actual time=9.854..6,793.178 rows=24,256,565 loops=1)

6. 6,198.887 6,198.887 ↑ 1.0 24,015,466 1

Seq Scan on pcy_attribute_value_10 pav_1 (cost=0.00..525,249.66 rows=24,015,466 width=24) (actual time=12.113..6,198.887 rows=24,015,466 loops=1)

7. 5,642.405 5,642.405 ↓ 1.0 22,128,149 1

Seq Scan on pcy_attribute_value_11 pav_2 (cost=0.00..506,902.48 rows=22,128,148 width=24) (actual time=5.965..5,642.405 rows=22,128,149 loops=1)

8. 6,663.006 6,663.006 ↑ 1.0 21,615,075 1

Seq Scan on pcy_attribute_value_12 pav_3 (cost=0.00..481,140.76 rows=21,615,076 width=24) (actual time=9.020..6,663.006 rows=21,615,075 loops=1)

9. 5,768.178 5,768.178 ↓ 1.0 21,610,105 1

Seq Scan on pcy_attribute_value_13 pav_4 (cost=0.00..490,601.04 rows=21,610,104 width=24) (actual time=6.128..5,768.178 rows=21,610,105 loops=1)

10. 5,046.253 5,046.253 ↑ 1.0 19,821,848 1

Seq Scan on pcy_attribute_value_14 pav_5 (cost=0.00..442,178.48 rows=19,821,848 width=24) (actual time=8.517..5,046.253 rows=19,821,848 loops=1)

11. 6,337.566 6,337.566 ↑ 1.0 17,786,444 1

Seq Scan on pcy_attribute_value_15 pav_6 (cost=0.00..409,265.44 rows=17,786,444 width=24) (actual time=22.422..6,337.566 rows=17,786,444 loops=1)

12. 6,341.134 6,341.134 ↑ 1.0 18,329,563 1

Seq Scan on pcy_attribute_value_16 pav_7 (cost=0.00..420,478.64 rows=18,329,564 width=24) (actual time=11.582..6,341.134 rows=18,329,563 loops=1)

13. 5,062.587 5,062.587 ↓ 1.0 17,137,981 1

Seq Scan on pcy_attribute_value_17 pav_8 (cost=0.00..385,222.80 rows=17,137,980 width=24) (actual time=6.786..5,062.587 rows=17,137,981 loops=1)

14. 6,439.907 6,439.907 ↑ 1.0 21,664,648 1

Seq Scan on pcy_attribute_value_18 pav_9 (cost=0.00..487,354.48 rows=21,664,648 width=24) (actual time=4.944..6,439.907 rows=21,664,648 loops=1)

15. 6,084.654 6,084.654 ↑ 1.0 18,823,724 1

Seq Scan on pcy_attribute_value_19 pav_10 (cost=0.00..429,895.24 rows=18,823,724 width=24) (actual time=3.006..6,084.654 rows=18,823,724 loops=1)

16. 6,029.149 6,029.149 ↑ 1.0 24,569,640 1

Seq Scan on pcy_attribute_value_2 pav_11 (cost=0.00..503,672.40 rows=24,569,640 width=24) (actual time=14.923..6,029.149 rows=24,569,640 loops=1)

17. 5,101.010 5,101.010 ↑ 1.0 18,593,604 1

Seq Scan on pcy_attribute_value_20 pav_12 (cost=0.00..422,136.04 rows=18,593,604 width=24) (actual time=6.901..5,101.010 rows=18,593,604 loops=1)

18. 6,759.831 6,759.831 ↑ 1.0 17,717,190 1

Seq Scan on pcy_attribute_value_21 pav_13 (cost=0.00..411,255.90 rows=17,717,190 width=24) (actual time=9.308..6,759.831 rows=17,717,190 loops=1)

19. 5,473.104 5,473.104 ↑ 1.0 17,679,307 1

Seq Scan on pcy_attribute_value_22 pav_14 (cost=0.00..405,818.08 rows=17,679,308 width=24) (actual time=10.460..5,473.104 rows=17,679,307 loops=1)

20. 6,803.469 6,803.469 ↑ 1.0 19,211,336 1

Seq Scan on pcy_attribute_value_23 pav_15 (cost=0.00..444,361.36 rows=19,211,336 width=24) (actual time=3.213..6,803.469 rows=19,211,336 loops=1)

21. 5,789.807 5,789.807 ↑ 1.0 19,697,878 1

Seq Scan on pcy_attribute_value_24 pav_16 (cost=0.00..468,295.78 rows=19,697,878 width=24) (actual time=3.679..5,789.807 rows=19,697,878 loops=1)

22. 7,673.239 7,673.239 ↓ 1.0 16,977,709 1

Seq Scan on pcy_attribute_value_25 pav_17 (cost=0.00..425,120.08 rows=16,977,708 width=24) (actual time=8.858..7,673.239 rows=16,977,709 loops=1)

23. 5,714.176 5,714.176 ↑ 1.0 18,315,582 1

Seq Scan on pcy_attribute_value_26 pav_18 (cost=0.00..441,854.82 rows=18,315,582 width=24) (actual time=7.920..5,714.176 rows=18,315,582 loops=1)

24. 4,970.175 4,970.175 ↑ 1.0 17,423,956 1

Seq Scan on pcy_attribute_value_27 pav_19 (cost=0.00..418,257.56 rows=17,423,956 width=24) (actual time=5.603..4,970.175 rows=17,423,956 loops=1)

25. 6,068.796 6,068.796 ↓ 1.0 17,489,101 1

Seq Scan on pcy_attribute_value_28 pav_20 (cost=0.00..434,493.00 rows=17,489,100 width=24) (actual time=10.225..6,068.796 rows=17,489,101 loops=1)

26. 4,547.650 4,547.650 ↑ 1.0 15,350,059 1

Seq Scan on pcy_attribute_value_29 pav_21 (cost=0.00..376,254.59 rows=15,350,059 width=24) (actual time=11.824..4,547.650 rows=15,350,059 loops=1)

27. 6,450.679 6,450.679 ↑ 1.0 24,344,688 1

Seq Scan on pcy_attribute_value_3 pav_22 (cost=0.00..498,651.88 rows=24,344,688 width=24) (actual time=10.422..6,450.679 rows=24,344,688 loops=1)

28. 7,797.732 7,797.732 ↑ 1.0 21,911,364 1

Seq Scan on pcy_attribute_value_30 pav_23 (cost=0.00..526,566.64 rows=21,911,364 width=24) (actual time=10.939..7,797.732 rows=21,911,364 loops=1)

29. 18,761.530 18,761.530 ↑ 1.0 23,397,760 1

Seq Scan on pcy_attribute_value_31 pav_24 (cost=0.00..555,023.60 rows=23,397,760 width=24) (actual time=15.781..18,761.530 rows=23,397,760 loops=1)

30. 15,468.333 15,468.333 ↑ 1.0 19,520,010 1

Seq Scan on pcy_attribute_value_32 pav_25 (cost=0.00..482,027.10 rows=19,520,010 width=24) (actual time=3.481..15,468.333 rows=19,520,010 loops=1)

31. 5,492.818 5,492.818 ↑ 1.0 16,248,011 1

Seq Scan on pcy_attribute_value_33 pav_26 (cost=0.00..413,405.11 rows=16,248,011 width=24) (actual time=22.349..5,492.818 rows=16,248,011 loops=1)

32. 6,267.022 6,267.022 ↑ 1.0 15,955,452 1

Seq Scan on pcy_attribute_value_34 pav_27 (cost=0.00..388,909.52 rows=15,955,452 width=24) (actual time=5.752..6,267.022 rows=15,955,452 loops=1)

33. 7,069.903 7,069.903 ↑ 1.0 19,811,290 1

Seq Scan on pcy_attribute_value_35 pav_28 (cost=0.00..472,007.90 rows=19,811,290 width=24) (actual time=6.045..7,069.903 rows=19,811,290 loops=1)

34. 6,918.439 6,918.439 ↑ 1.0 21,631,995 1

Seq Scan on pcy_attribute_value_36 pav_29 (cost=0.00..510,339.96 rows=21,631,996 width=24) (actual time=24.012..6,918.439 rows=21,631,995 loops=1)

35. 8,394.585 8,394.585 ↑ 1.0 17,698,390 1

Seq Scan on pcy_attribute_value_37 pav_30 (cost=0.00..435,164.90 rows=17,698,390 width=24) (actual time=8.328..8,394.585 rows=17,698,390 loops=1)

36. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on pcy_attribute_value_38 pav_31 (cost=0.00..16.10 rows=610 width=24) (actual time=0.005..0.005 rows=0 loops=1)

37. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on pcy_attribute_value_39 pav_32 (cost=0.00..16.10 rows=610 width=24) (actual time=0.002..0.002 rows=0 loops=1)

38. 5,798.047 5,798.047 ↓ 1.0 24,889,637 1

Seq Scan on pcy_attribute_value_4 pav_33 (cost=0.00..510,028.36 rows=24,889,636 width=24) (actual time=5.334..5,798.047 rows=24,889,637 loops=1)

39. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on pcy_attribute_value_40 pav_34 (cost=0.00..16.10 rows=610 width=24) (actual time=0.004..0.004 rows=0 loops=1)

40. 5,078.356 5,078.356 ↓ 1.0 23,437,325 1

Seq Scan on pcy_attribute_value_5 pav_35 (cost=0.00..483,944.24 rows=23,437,324 width=24) (actual time=8.789..5,078.356 rows=23,437,325 loops=1)

41. 6,529.829 6,529.829 ↓ 1.0 23,356,997 1

Seq Scan on pcy_attribute_value_6 pav_36 (cost=0.00..484,783.96 rows=23,356,996 width=24) (actual time=17.805..6,529.829 rows=23,356,997 loops=1)

42. 5,622.988 5,622.988 ↑ 1.0 22,122,242 1

Seq Scan on pcy_attribute_value_7 pav_37 (cost=0.00..462,940.42 rows=22,122,242 width=24) (actual time=7.514..5,622.988 rows=22,122,242 loops=1)

43. 5,576.852 5,576.852 ↑ 1.0 23,454,474 1

Seq Scan on pcy_attribute_value_8 pav_38 (cost=0.00..499,598.74 rows=23,454,474 width=24) (actual time=7.305..5,576.852 rows=23,454,474 loops=1)

44. 5,849.882 5,849.882 ↓ 1.0 23,103,985 1

Seq Scan on pcy_attribute_value_9 pav_39 (cost=0.00..512,457.84 rows=23,103,984 width=24) (actual time=14.305..5,849.882 rows=23,103,985 loops=1)

45. 6.519 8,800.564 ↓ 1.7 8,920 1

Hash (cost=53,300.96..53,300.96 rows=5,151 width=16) (actual time=8,800.564..8,800.564 rows=8,920 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 547kB
46. 4.086 8,794.045 ↓ 1.7 8,920 1

Nested Loop (cost=13.33..53,300.96 rows=5,151 width=16) (actual time=45.776..8,794.045 rows=8,920 loops=1)

  • Join Filter: (prd.id = pcy.product_id)
47. 0.081 38.895 ↓ 7.0 7 1

Hash Join (cost=12.90..47.44 rows=1 width=24) (actual time=30.206..38.895 rows=7 loops=1)

  • Hash Cond: (i.product_id = prd.id)
48. 23.568 37.747 ↑ 1.3 7 1

Bitmap Heap Scan on prd_item i (cost=4.49..39.01 rows=9 width=16) (actual time=29.084..37.747 rows=7 loops=1)

  • Recheck Cond: ((code)::text = 'DSZ_POLISA'::text)
  • Heap Blocks: exact=7
49. 14.179 14.179 ↑ 1.3 7 1

Bitmap Index Scan on uq_prd_item_code_parentitemid (cost=0.00..4.49 rows=9 width=0) (actual time=14.179..14.179 rows=7 loops=1)

  • Index Cond: ((code)::text = 'DSZ_POLISA'::text)
50. 0.011 1.067 ↑ 1.0 7 1

Hash (cost=8.32..8.32 rows=7 width=8) (actual time=1.066..1.067 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 1.056 1.056 ↑ 1.0 7 1

Seq Scan on prd_product prd (cost=0.00..8.32 rows=7 width=8) (actual time=0.015..1.056 rows=7 loops=1)

  • Filter: ((flow_code)::text = 'D1'::text)
  • Rows Removed by Filter: 179
52. 8,751.064 8,751.064 ↑ 11.1 1,274 7

Index Scan using idx_policy_product_id on pcy_policy pcy (cost=0.43..53,076.99 rows=14,122 width=16) (actual time=17.523..1,250.152 rows=1,274 loops=7)

  • Index Cond: (product_id = i.product_id)
  • Filter: ((creation_date < '2019-08-17 23:59:59'::timestamp without time zone) AND ((status)::text <> ALL ('{CANCELED,CANCELED_BY_HQ}'::text[])) AND ((type)::text = 'POLICY'::text))
  • Rows Removed by Filter: 849
Planning time : 1,965.906 ms
Execution time : 376,305.476 ms