explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BLk2

Settings
# exclusive inclusive rows x rows loops node
1. 32.387 1,336.654 ↑ 2,160.1 158 1

Nested Loop (cost=83,816.58..51,581,047.01 rows=341,294 width=347) (actual time=190.089..1,336.654 rows=158 loops=1)

2.          

Initplan (for Nested Loop)

3. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on dblanguage (cost=0.00..2.75 rows=1 width=6) (actual time=0.017..0.030 rows=1 loops=1)

  • Filter: ((languageisocode)::text = 'en'::text)
  • Rows Removed by Filter: 139
4. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on dblanguage dblanguage_1 (cost=0.00..2.75 rows=1 width=6) (actual time=0.012..0.026 rows=1 loops=1)

  • Filter: ((languageisocode)::text = 'en'::text)
  • Rows Removed by Filter: 139
5. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on dblanguage dblanguage_2 (cost=0.00..2.75 rows=1 width=6) (actual time=0.010..0.023 rows=1 loops=1)

  • Filter: ((languageisocode)::text = 'en'::text)
  • Rows Removed by Filter: 139
6. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on dblanguage dblanguage_3 (cost=0.00..2.75 rows=1 width=6) (actual time=0.013..0.027 rows=1 loops=1)

  • Filter: ((languageisocode)::text = 'en'::text)
  • Rows Removed by Filter: 139
7. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on dblanguage dblanguage_4 (cost=0.00..2.75 rows=1 width=6) (actual time=0.010..0.023 rows=1 loops=1)

  • Filter: ((languageisocode)::text = 'en'::text)
  • Rows Removed by Filter: 139
8. 0.307 1,294.658 ↑ 159.1 158 1

Nested Loop (cost=83,802.27..236,727.18 rows=25,134 width=332) (actual time=189.870..1,294.658 rows=158 loops=1)

9. 0.232 1,292.771 ↑ 11.7 158 1

Nested Loop (cost=83,801.70..168,667.03 rows=1,852 width=317) (actual time=189.783..1,292.771 rows=158 loops=1)

10. 0.310 1,291.275 ↓ 1.2 158 1

Nested Loop (cost=83,801.13..163,632.34 rows=137 width=302) (actual time=189.740..1,291.275 rows=158 loops=1)

11. 0.292 1,288.898 ↓ 15.9 159 1

Nested Loop (cost=83,800.56..163,264.84 rows=10 width=287) (actual time=189.679..1,288.898 rows=159 loops=1)

12. 0.330 1,287.493 ↓ 15.9 159 1

Hash Join (cost=83,800.28..163,202.88 rows=10 width=289) (actual time=189.651..1,287.493 rows=159 loops=1)

  • Hash Cond: (st.seasonclientid = sc.seasonclientid)
13. 4.165 1,286.739 ↓ 10.6 159 1

Hash Join (cost=83,783.07..163,185.50 rows=15 width=293) (actual time=189.184..1,286.739 rows=159 loops=1)

  • Hash Cond: (ss.smstyleid = st.smstyleid)
14. 128.520 1,095.626 ↓ 48.7 3,943 1

Nested Loop Left Join (cost=0.85..79,402.23 rows=81 width=150) (actual time=0.409..1,095.626 rows=3,943 loops=1)

  • Join Filter: (o.officeid = cs.officeid)
  • Rows Removed by Join Filter: 513207
15. 3.310 931.619 ↓ 48.7 3,943 1

Nested Loop Left Join (cost=0.85..79,206.96 rows=81 width=133) (actual time=0.354..931.619 rows=3,943 loops=1)

16. 4.419 908.594 ↓ 48.7 3,943 1

Nested Loop Left Join (cost=0.57..78,745.34 rows=81 width=120) (actual time=0.330..908.594 rows=3,943 loops=1)

17. 8.720 845.030 ↓ 48.7 3,943 1

Nested Loop Left Join (cost=0.28..78,139.62 rows=81 width=99) (actual time=0.299..845.030 rows=3,943 loops=1)

18. 832.367 832.367 ↓ 48.7 3,943 1

Seq Scan on dbsmstylesupplier ss (cost=0.00..77,702.92 rows=81 width=105) (actual time=0.273..832.367 rows=3,943 loops=1)

  • Filter: (lasteditedby_date > (('now'::cstring)::date - 1))
  • Rows Removed by Filter: 761574
19. 3.943 3.943 ↓ 0.0 0 3,943

Index Scan using pk_productid on dbsmproductid pi (cost=0.28..5.38 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=3,943)

  • Index Cond: (smproductidid = ss.productid)
20. 59.145 59.145 ↑ 1.0 1 3,943

Index Scan using pk_clients on dbclientsupplier cs (cost=0.29..7.47 rows=1 width=29) (actual time=0.015..0.015 rows=1 loops=3,943)

  • Index Cond: (clientsupplierid = ss.clientsupplierid)
21. 19.715 19.715 ↑ 1.0 1 3,943

Index Scan using pk_supplier on dbsupplier su (cost=0.29..5.69 rows=1 width=36) (actual time=0.004..0.005 rows=1 loops=3,943)

  • Index Cond: (cs.supplierid = supplierid)
22. 35.427 35.487 ↑ 1.2 131 3,943

Materialize (cost=0.00..7.33 rows=155 width=32) (actual time=0.000..0.009 rows=131 loops=3,943)

23. 0.060 0.060 ↑ 1.2 131 1

Seq Scan on dboffice o (cost=0.00..6.55 rows=155 width=32) (actual time=0.006..0.060 rows=131 loops=1)

24. 17.672 186.948 ↑ 5.0 23,237 1

Hash (cost=82,339.09..82,339.09 rows=115,450 width=151) (actual time=186.948..186.948 rows=23,237 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2987kB
25. 10.026 169.276 ↑ 5.0 23,237 1

Hash Left Join (cost=44,623.38..82,339.09 rows=115,450 width=151) (actual time=37.553..169.276 rows=23,237 loops=1)

  • Hash Cond: (st.smfitid = fi.smfitid)
26. 6.601 134.960 ↓ 3.4 23,237 1

Hash Left Join (cost=2,146.26..39,517.59 rows=6,829 width=129) (actual time=13.243..134.960 rows=23,237 loops=1)

  • Hash Cond: (st.genreclassificationid = gr.genreclassificationid)
27. 15.749 128.139 ↓ 3.4 23,237 1

Hash Left Join (cost=2,132.12..39,467.38 rows=6,829 width=134) (actual time=13.006..128.139 rows=23,237 loops=1)

  • Hash Cond: (st.smproductgroupid = pg.smproductgroupid)
28. 4.330 108.104 ↓ 3.4 23,237 1

Nested Loop (cost=1,832.29..39,027.03 rows=6,829 width=130) (actual time=8.693..108.104 rows=23,237 loops=1)

29. 0.058 0.058 ↑ 1.0 1 1

Seq Scan on dbclient c (cost=0.00..5.14 rows=1 width=8) (actual time=0.053..0.058 rows=1 loops=1)

  • Filter: ((companyshort)::text = 'VEN'::text)
  • Rows Removed by Filter: 90
30. 97.268 103.716 ↑ 1.2 23,237 1

Bitmap Heap Scan on dbsmstyle st (cost=1,832.29..38,751.68 rows=27,021 width=137) (actual time=8.634..103.716 rows=23,237 loops=1)

  • Recheck Cond: (clientid = c.clientid)
  • Filter: (istemplate = 0)
  • Rows Removed by Filter: 76
  • Heap Blocks: exact=11837
31. 6.448 6.448 ↑ 1.2 23,476 1

Bitmap Index Scan on ixfk_smstyle09 (cost=0.00..1,825.53 rows=27,348 width=0) (actual time=6.448..6.448 rows=23,476 loops=1)

  • Index Cond: (clientid = c.clientid)
32. 2.047 4.286 ↑ 1.0 7,979 1

Hash (cost=196.59..196.59 rows=8,259 width=13) (actual time=4.286..4.286 rows=7,979 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 358kB
33. 2.239 2.239 ↑ 1.0 7,979 1

Seq Scan on dbsmproductgroup pg (cost=0.00..196.59 rows=8,259 width=13) (actual time=0.017..2.239 rows=7,979 loops=1)

34. 0.142 0.220 ↑ 1.0 495 1

Hash (cost=7.95..7.95 rows=495 width=13) (actual time=0.220..0.220 rows=495 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
35. 0.078 0.078 ↑ 1.0 495 1

Seq Scan on dbgenreclassification gr (cost=0.00..7.95 rows=495 width=13) (actual time=0.009..0.078 rows=495 loops=1)

36. 0.529 24.290 ↑ 19.5 727 1

Hash (cost=42,299.61..42,299.61 rows=14,201 width=40) (actual time=24.290..24.290 rows=727 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 59kB
37. 0.852 23.761 ↑ 19.5 727 1

Nested Loop Left Join (cost=0.57..42,299.61 rows=14,201 width=40) (actual time=0.115..23.761 rows=727 loops=1)

38. 0.372 0.372 ↑ 1.2 727 1

Seq Scan on dbsmfit fi (cost=0.00..15.40 rows=840 width=16) (actual time=0.006..0.372 rows=727 loops=1)

39. 22.537 22.537 ↑ 17.0 1 727

Index Scan using pk_18ndscrptn on dbi18ndescription i_fit (cost=0.57..50.17 rows=17 width=32) (actual time=0.030..0.031 rows=1 loops=727)

  • Index Cond: ((rid = fi.smfitid) AND (languageid = $8) AND ((descriptionkey)::text = 'Description'::text))
40. 0.161 0.424 ↓ 1.2 447 1

Hash (cost=12.55..12.55 rows=373 width=12) (actual time=0.424..0.424 rows=447 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
41. 0.263 0.263 ↓ 1.2 447 1

Seq Scan on dbseasonclient sc (cost=0.00..12.55 rows=373 width=12) (actual time=0.016..0.263 rows=447 loops=1)

  • Filter: ((code)::text > '124'::text)
  • Rows Removed by Filter: 149
42. 1.113 1.113 ↑ 1.0 1 159

Index Scan using pk_department on dbdepartment de (cost=0.29..6.19 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=159)

  • Index Cond: (departmentid = ss.departmentid)
  • Filter: (((description)::text !~~ '99%'::text) AND ((description)::text !~~ '79%'::text))
43. 2.067 2.067 ↑ 17.0 1 159

Index Scan using pk_18ndscrptn on dbi18ndescription i_class (cost=0.57..36.58 rows=17 width=32) (actual time=0.012..0.013 rows=1 loops=159)

  • Index Cond: ((rid = st.smmkzsummarizationid) AND (languageid = $9) AND ((descriptionkey)::text = 'Description'::text))
44. 1.264 1.264 ↑ 17.0 1 158

Index Scan using pk_18ndscrptn on dbi18ndescription i_assort (cost=0.57..36.58 rows=17 width=32) (actual time=0.007..0.008 rows=1 loops=158)

  • Index Cond: ((rid = st.smassortmentid) AND (languageid = $10) AND ((descriptionkey)::text = 'Description'::text))
45. 1.580 1.580 ↑ 17.0 1 158

Index Scan using pk_18ndscrptn on dbi18ndescription i_subgrp (cost=0.57..36.58 rows=17 width=32) (actual time=0.010..0.010 rows=1 loops=158)

  • Index Cond: ((rid = st.smassortmentsubgroupid) AND (languageid = $12) AND ((descriptionkey)::text = 'Description'::text))
46. 2.212 2.212 ↑ 17.0 1 158

Index Scan using pk_18ndscrptn on dbi18ndescription i_prod (cost=0.57..36.58 rows=17 width=32) (actual time=0.010..0.014 rows=1 loops=158)

  • Index Cond: ((rid = st.smproductgroupid) AND (languageid = $11) AND ((descriptionkey)::text = 'Description'::text))
47.          

SubPlan (for Nested Loop)

48. 1.106 1.106 ↑ 1.0 1 158

Index Scan using pk_ldpsrmppng on dbldapusermapping (cost=0.29..8.31 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=158)

  • Index Cond: (ldapusermappingid = ss.createdby_user)
49. 0.948 0.948 ↑ 1.0 1 158

Index Scan using pk_ldpsrmppng on dbldapusermapping dbldapusermapping_1 (cost=0.29..8.31 rows=1 width=14) (actual time=0.006..0.006 rows=1 loops=158)

  • Index Cond: (ldapusermappingid = ss.lasteditedby_user)
50. 0.632 0.632 ↑ 1.0 1 158

Seq Scan on dbsmsupplierstatus (cost=0.00..1.05 rows=1 width=58) (actual time=0.002..0.004 rows=1 loops=158)

  • Filter: (smsupplierstatusid = ss.smsupplierstatusid)
  • Rows Removed by Filter: 8
51. 0.158 1.896 ↓ 0.0 0 158

Nested Loop (cost=0.57..92.07 rows=32 width=26) (actual time=0.012..0.012 rows=0 loops=158)

52. 1.738 1.738 ↓ 0.0 0 158

Seq Scan on dbsmffpdecision ff (cost=0.00..1.96 rows=1 width=11) (actual time=0.011..0.011 rows=0 loops=158)

  • Filter: (smffpdecisionid = ss.ffpdecision)
  • Rows Removed by Filter: 88
53. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_18ndscrptn on dbi18ndescription iff (cost=0.57..89.63 rows=32 width=32) (never executed)

  • Index Cond: ((rid = ss.ffpdecision) AND (languageid = 3064860::numeric) AND ((descriptionkey)::text = 'Description'::text))
54. 2.212 2.212 ↓ 0.0 0 158

Seq Scan on dbsmstandard std (cost=0.00..2.46 rows=1 width=3) (actual time=0.014..0.014 rows=0 loops=158)

  • Filter: (smstandardid = st.standard)
  • Rows Removed by Filter: 120
55. 0.000 0.316 ↓ 0.0 0 158

Index Scan using pk_basedon on dbsmbasedon (cost=19.21..27.22 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=158)

  • Index Cond: (smbasedonid = $6)
56.          

Initplan (for Index Scan)

57. 0.316 29.862 ↑ 1.0 1 158

Aggregate (cost=18.77..18.78 rows=1 width=8) (actual time=0.189..0.189 rows=1 loops=158)

58. 29.546 29.546 ↓ 0.0 0 158

Index Scan using fk_basedon_stls on dbsmbasedon bo (cost=0.43..18.74 rows=12 width=8) (actual time=0.187..0.187 rows=0 loops=158)

  • Index Cond: (smstylesupplierid = ss.smstylesupplierid)
59. 0.158 0.158 ↓ 0.0 0 158

Index Scan using pk_ldpsrmppng on dbldapusermapping dbldapusermapping_2 (cost=0.29..8.31 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=158)

  • Index Cond: (ldapusermappingid = ss.responsibleid)
Planning time : 32.359 ms
Execution time : 1,338.141 ms