explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VmHh : v3

Settings
# exclusive inclusive rows x rows loops node
1. 4.975 191.839 ↑ 8.0 14,984 1

Hash Left Join (cost=12,301.81..481,433.30 rows=119,131 width=298) (actual time=55.481..191.839 rows=14,984 loops=1)

  • Hash Cond: ((pro.color_id_)::text = (color.color_id_)::text)
2.          

CTE sp

3. 11.677 11.677 ↑ 8.1 14,984 1

Index Scan using cmm_serialized_product_info_idx01 on cmm_serialized_product_info (cost=0.51..6,641.25 rows=121,929 width=62) (actual time=0.035..11.677 rows=14,984 loops=1)

  • Index Cond: ((site_id_)::text = ANY ('{V100001,V100002,V123002,V123004,VA00001,VA00002,VA00003,VA00004,VA00005,VA00006,VA00007,VA00009,VA00010,VA00011,VA00012,VA23002,VA23005,VAVA001,VAVA003,VC23002,VE00001,VF00002,VH23001,VI23001,VI23002,VJ23001,VK23001,VL23001,VM23001,VM23002,VM23003,VM23004,VP23002,VA23001,VA23004,VAVA002,9V0001,9VM001,V123003,V500001,VA00008,VA23003,VA23006,VA23007,VA23008,VA23009,VB23002,VB23003,VB23005,VB23101,VB23103,VC23001,VD23001,VF00001,VG00001,VG23001,VL23002,VM00001,VP00001,VP23001,VQ00001}'::text[]))
4. 22.013 186.841 ↑ 8.0 14,984 1

Hash Join (cost=5,648.09..474,306.19 rows=119,131 width=237) (actual time=55.442..186.841 rows=14,984 loops=1)

  • Hash Cond: ((sp.product_id_)::text = (pro.product_id_)::text)
5. 10.188 115.327 ↑ 8.0 14,984 1

Nested Loop (cost=528.80..457,601.10 rows=119,131 width=262) (actual time=5.564..115.327 rows=14,984 loops=1)

6. 5.734 30.219 ↑ 8.0 14,984 1

Hash Join (cost=528.24..5,072.59 rows=119,131 width=310) (actual time=5.546..30.219 rows=14,984 loops=1)

  • Hash Cond: ((sp.site_id_)::text = (party.party_code_)::text)
7. 18.988 18.988 ↑ 8.1 14,984 1

CTE Scan on sp (cost=0.00..2,438.58 rows=121,929 width=294) (actual time=0.038..18.988 rows=14,984 loops=1)

8. 0.973 5.497 ↑ 1.0 3,875 1

Hash (cost=479.80..479.80 rows=3,875 width=23) (actual time=5.497..5.497 rows=3,875 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 213kB
9. 2.313 4.524 ↑ 1.0 3,875 1

Hash Join (cost=163.24..479.80 rows=3,875 width=23) (actual time=1.618..4.524 rows=3,875 loops=1)

  • Hash Cond: ((org.party_id_)::text = (party.party_id_)::text)
10. 0.608 0.608 ↑ 1.0 3,875 1

Seq Scan on cmm_organization_info org (cost=0.00..248.75 rows=3,875 width=52) (actual time=0.004..0.608 rows=3,875 loops=1)

11. 0.791 1.603 ↑ 1.0 3,958 1

Hash (cost=113.66..113.66 rows=3,966 width=43) (actual time=1.603..1.603 rows=3,958 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 294kB
12. 0.812 0.812 ↑ 1.0 3,958 1

Seq Scan on cmm_party_info party (cost=0.00..113.66 rows=3,966 width=43) (actual time=0.004..0.812 rows=3,958 loops=1)

13. 74.920 74.920 ↑ 1.0 1 14,984

Index Scan using serialized_product_identifier_idx01 on cmm_serialized_product_identifier identifier (cost=0.56..3.79 rows=1 width=74) (actual time=0.005..0.005 rows=1 loops=14,984)

  • Index Cond: ((serialized_product_id_)::text = (sp.serialized_product_id_)::text)
14. 22.738 49.501 ↑ 1.0 65,229 1

Hash (cost=3,151.13..3,151.13 rows=67,613 width=110) (actual time=49.501..49.501 rows=65,229 loops=1)

  • Buckets: 4096 Batches: 4 Memory Usage: 1721kB
15. 26.763 26.763 ↑ 1.0 65,229 1

Seq Scan on cmm_product pro (cost=0.00..3,151.13 rows=67,613 width=110) (actual time=0.004..26.763 rows=65,229 loops=1)

16. 0.011 0.023 ↑ 2.4 45 1

Hash (cost=11.10..11.10 rows=110 width=196) (actual time=0.023..0.023 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
17. 0.012 0.012 ↑ 2.4 45 1

Seq Scan on cmm_color_info color (cost=0.00..11.10 rows=110 width=196) (actual time=0.004..0.012 rows=45 loops=1)

Planning time : 0.882 ms
Execution time : 193.265 ms