explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xLhL

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 98,448.094 ↓ 0.0 0 1

Limit (cost=3,679,717.88..3,679,717.88 rows=1 width=64) (actual time=98,448.094..98,448.094 rows=0 loops=1)

  • Output: itemsupers0_.tmid, region1_.riid, gpmitem2_.tiid, lifecyclep3_.lfid, lifecyclep4_.lrid, item5_.tiid, lifecyclep6_.lfid, lifecyclep7_.lrid
  • Buffers: shared hit=6,589,485, temp read=37,255 written=37,255
2. 0.006 98,448.094 ↓ 0.0 0 1

Sort (cost=3,679,717.88..3,679,717.88 rows=1 width=64) (actual time=98,448.094..98,448.094 rows=0 loops=1)

  • Output: itemsupers0_.tmid, region1_.riid, gpmitem2_.tiid, lifecyclep3_.lfid, lifecyclep4_.lrid, item5_.tiid, lifecyclep6_.lfid, lifecyclep7_.lrid
  • Sort Key: itemsupers0_.tmid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6,589,485, temp read=37,255 written=37,255
3. 1,426.256 98,448.088 ↓ 0.0 0 1

Hash Join (cost=1,799,514.45..3,679,717.87 rows=1 width=64) (actual time=98,448.088..98,448.088 rows=0 loops=1)

  • Output: itemsupers0_.tmid, region1_.riid, gpmitem2_.tiid, lifecyclep3_.lfid, lifecyclep4_.lrid, item5_.tiid, lifecyclep6_.lfid, lifecyclep7_.lrid
  • Hash Cond: ((itemsupers0_.tpaliasid = item8_.tpid) AND ((itemsupers0_.tmalitprefix)::text = (item8_.tiprefix)::text) AND ((itemsupers0_.tmalitid)::text = (item8_.tipaitid)::text))
  • Buffers: shared hit=6,589,485, temp read=37,255 written=37,255
4. 1,386.423 97,021.818 ↑ 1.0 7,924,502 1

Hash Left Join (cost=1,799,505.98..3,590,535.71 rows=7,926,549 width=86) (actual time=69,344.057..97,021.818 rows=7,924,502 loops=1)

  • Output: itemsupers0_.tmid, itemsupers0_.tpaliasid, itemsupers0_.tmalitprefix, itemsupers0_.tmalitid, region1_.riid, gpmitem2_.tiid, lifecyclep3_.lfid, lifecyclep4_.lrid, item5_.tiid, lifecyclep6_.lfid, lifecyclep7_.lrid
  • Hash Cond: (item5_.lrid = lifecyclep7_.lrid)
  • Buffers: shared hit=6,589,481, temp read=37,255 written=37,255
5. 1,349.285 95,635.394 ↑ 1.0 7,924,502 1

Hash Left Join (cost=1,799,495.08..3,560,800.24 rows=7,926,549 width=86) (actual time=69,344.045..95,635.394 rows=7,924,502 loops=1)

  • Output: itemsupers0_.tmid, itemsupers0_.tpaliasid, itemsupers0_.tmalitprefix, itemsupers0_.tmalitid, region1_.riid, gpmitem2_.tiid, lifecyclep3_.lfid, lifecyclep4_.lrid, item5_.tiid, item5_.lrid, lifecyclep6_.lfid
  • Hash Cond: (item5_.lfid = lifecyclep6_.lfid)
  • Buffers: shared hit=6,589,481, temp read=37,255 written=37,255
6. 1,360.332 94,286.108 ↑ 1.0 7,924,502 1

Hash Left Join (cost=1,799,483.51..3,531,064.10 rows=7,926,549 width=86) (actual time=69,344.036..94,286.108 rows=7,924,502 loops=1)

  • Output: itemsupers0_.tmid, itemsupers0_.tpaliasid, itemsupers0_.tmalitprefix, itemsupers0_.tmalitid, region1_.riid, gpmitem2_.tiid, lifecyclep3_.lfid, lifecyclep4_.lrid, item5_.tiid, item5_.lfid, item5_.lrid
  • Hash Cond: (gpmitem2_.lrid = lifecyclep4_.lrid)
  • Buffers: shared hit=6,589,481, temp read=37,255 written=37,255
7. 1,313.260 92,925.775 ↑ 1.0 7,924,502 1

Hash Left Join (cost=1,799,472.61..3,501,328.63 rows=7,926,549 width=86) (actual time=69,344.030..92,925.775 rows=7,924,502 loops=1)

  • Output: itemsupers0_.tmid, itemsupers0_.tpaliasid, itemsupers0_.tmalitprefix, itemsupers0_.tmalitid, region1_.riid, gpmitem2_.tiid, gpmitem2_.lrid, lifecyclep3_.lfid, item5_.tiid, item5_.lfid, item5_.lrid
  • Hash Cond: (gpmitem2_.lfid = lifecyclep3_.lfid)
  • Buffers: shared hit=6,589,481, temp read=37,255 written=37,255
8. 2,675.952 91,612.513 ↑ 1.0 7,924,502 1

Hash Join (cost=1,799,461.03..3,471,592.49 rows=7,926,549 width=86) (actual time=69,344.022..91,612.513 rows=7,924,502 loops=1)

  • Output: itemsupers0_.tmid, itemsupers0_.tpaliasid, itemsupers0_.tmalitprefix, itemsupers0_.tmalitid, region1_.riid, gpmitem2_.tiid, gpmitem2_.lfid, gpmitem2_.lrid, item5_.tiid, item5_.lfid, item5_.lrid
  • Hash Cond: (itemsupers0_.tiid = gpmitem2_.tiid)
  • Buffers: shared hit=6,589,481, temp read=37,255 written=37,255
9. 1,700.579 83,260.483 ↑ 1.0 7,924,502 1

Hash Left Join (cost=1,399,038.26..2,892,822.36 rows=7,926,549 width=70) (actual time=63,638.524..83,260.483 rows=7,924,502 loops=1)

  • Output: itemsupers0_.tmid, itemsupers0_.tiid, itemsupers0_.tpaliasid, itemsupers0_.tmalitprefix, itemsupers0_.tmalitid, region1_.riid, item5_.tiid, item5_.lfid, item5_.lrid
  • Hash Cond: (itemsupers0_.riid = region1_.riid)
  • Buffers: shared hit=6,127,989, temp read=37,255 written=37,255
10. 8,907.875 81,559.785 ↑ 1.0 7,924,502 1

Merge Left Join (cost=1,399,023.92..2,787,732.89 rows=7,926,549 width=70) (actual time=63,638.382..81,559.785 rows=7,924,502 loops=1)

  • Output: itemsupers0_.tmid, itemsupers0_.riid, itemsupers0_.tiid, itemsupers0_.tpaliasid, itemsupers0_.tmalitprefix, itemsupers0_.tmalitid, item5_.tiid, item5_.lfid, item5_.lrid
  • Merge Cond: ((itemsupers0_.tpaliasid = item5_.tpid) AND ((itemsupers0_.tmalitprefix)::text = (item5_.tiprefix)::text) AND ((itemsupers0_.tmalitid)::text = (item5_.tipaitid)::text))
  • Buffers: shared hit=6,127,984, temp read=37,255 written=37,255
11. 3,747.600 3,747.600 ↑ 1.0 7,924,502 1

Index Scan using xak02traditemal_tm on public.traditemal_tm itemsupers0_ (cost=0.56..1,207,403.06 rows=7,926,549 width=46) (actual time=0.011..3,747.600 rows=7,924,502 loops=1)

  • Output: itemsupers0_.tmid, itemsupers0_.tmregtim, itemsupers0_.tmregid, itemsupers0_.tmchgtim, itemsupers0_.tmchgid, itemsupers0_.tmreadonly, itemsupers0_.tpaliasid, itemsupers0_.tmalitid, itemsupers0_.tmalityp, itemsupers0_.tiid, itemsupers0_.tmprio, itemsupers0_.tmvlftim, itemsupers0_.tmvlttim, itemsupers0_.tmrepqty, itemsupers0_.tmitname, itemsupers0_.tmstatus, itemsupers0_.tmstruct, itemsupers0_.tmrepltxt, itemsupers0_.tmconscod, itemsupers0_.tmleadti, itemsupers0_.tmleadtyp, itemsupers0_.tmad1var, itemsupers0_.tmad2var, itemsupers0_.tmad3var, itemsupers0_.tmad4var, itemsupers0_.tmad5var, itemsupers0_.tmad6var, itemsupers0_.tmad7var, itemsupers0_.tmad1num, itemsupers0_.tmad2num, itemsupers0_.tmad3num, itemsupers0_.tmad4num, itemsupers0_.tmad5num, itemsupers0_.tmad6num, itemsupers0_.tmad7num, itemsupers0_.tmad1tim, itemsupers0_.tmad2tim, itemsupers0_.tmad3tim, itemsupers0_.tmalitprefix, itemsupers0_.ppid, itemsupers0_.riid
  • Buffers: shared hit=5,666,492
12. 1,131.239 68,904.310 ↓ 1.9 13,963,270 1

Materialize (cost=1,399,023.36..1,436,709.31 rows=7,537,190 width=45) (actual time=63,638.363..68,904.310 rows=13,963,270 loops=1)

  • Output: item5_.tiid, item5_.tpid, item5_.tiprefix, item5_.tipaitid, item5_.lfid, item5_.lrid
  • Buffers: shared hit=461,492, temp read=37,255 written=37,255
13. 63,992.574 67,773.071 ↑ 1.1 7,119,698 1

Sort (cost=1,399,023.36..1,417,866.34 rows=7,537,190 width=45) (actual time=63,638.357..67,773.071 rows=7,119,698 loops=1)

  • Output: item5_.tiid, item5_.tpid, item5_.tiprefix, item5_.tipaitid, item5_.lfid, item5_.lrid
  • Sort Key: item5_.tpid, item5_.tiprefix, item5_.tipaitid
  • Sort Method: external merge Disk: 298,024kB
  • Buffers: shared hit=461,492, temp read=37,255 written=37,255
14. 3,780.497 3,780.497 ↑ 1.0 7,533,234 1

Seq Scan on public.tradpaitem_ti item5_ (cost=0.00..306,207.90 rows=7,537,190 width=45) (actual time=0.138..3,780.497 rows=7,533,234 loops=1)

  • Output: item5_.tiid, item5_.tpid, item5_.tiprefix, item5_.tipaitid, item5_.lfid, item5_.lrid
  • Buffers: shared hit=461,492
15. 0.049 0.119 ↑ 1.0 414 1

Hash (cost=9.15..9.15 rows=415 width=8) (actual time=0.119..0.119 rows=414 loops=1)

  • Output: region1_.riid
  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
  • Buffers: shared hit=5
16. 0.070 0.070 ↑ 1.0 414 1

Seq Scan on public.region_ri region1_ (cost=0.00..9.15 rows=415 width=8) (actual time=0.010..0.070 rows=414 loops=1)

  • Output: region1_.riid
  • Buffers: shared hit=5
17. 1,954.633 5,676.078 ↑ 1.0 7,533,234 1

Hash (cost=306,207.90..306,207.90 rows=7,537,190 width=24) (actual time=5,676.078..5,676.078 rows=7,533,234 loops=1)

  • Output: gpmitem2_.tiid, gpmitem2_.lfid, gpmitem2_.lrid
  • Buckets: 8,388,608 Batches: 1 Memory Usage: 359,803kB
  • Buffers: shared hit=461,492
18. 3,721.445 3,721.445 ↑ 1.0 7,533,234 1

Seq Scan on public.tradpaitem_ti gpmitem2_ (cost=0.00..306,207.90 rows=7,537,190 width=24) (actual time=0.146..3,721.445 rows=7,533,234 loops=1)

  • Output: gpmitem2_.tiid, gpmitem2_.lfid, gpmitem2_.lrid
  • Buffers: shared hit=461,492
19. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=10.70..10.70 rows=70 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: lifecyclep3_.lfid
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
20. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on public.lifecycleprofile_lf lifecyclep3_ (cost=0.00..10.70 rows=70 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: lifecyclep3_.lfid
21. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=10.40..10.40 rows=40 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: lifecyclep4_.lrid
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
22. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public.lifecyclematcher_lr lifecyclep4_ (cost=0.00..10.40 rows=40 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: lifecyclep4_.lrid
23. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=10.70..10.70 rows=70 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: lifecyclep6_.lfid
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
24. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public.lifecycleprofile_lf lifecyclep6_ (cost=0.00..10.70 rows=70 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: lifecyclep6_.lfid
25. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=10.40..10.40 rows=40 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: lifecyclep7_.lrid
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
26. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public.lifecyclematcher_lr lifecyclep7_ (cost=0.00..10.40 rows=40 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: lifecyclep7_.lrid
27. 0.004 0.014 ↑ 1.0 1 1

Hash (cost=8.45..8.45 rows=1 width=21) (actual time=0.014..0.014 rows=1 loops=1)

  • Output: item8_.tpid, item8_.tiprefix, item8_.tipaitid
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
28. 0.010 0.010 ↑ 1.0 1 1

Index Scan using xpktradpaitem_ti on public.tradpaitem_ti item8_ (cost=0.43..8.45 rows=1 width=21) (actual time=0.009..0.010 rows=1 loops=1)

  • Output: item8_.tpid, item8_.tiprefix, item8_.tipaitid
  • Index Cond: (item8_.tiid = '1511342039861'::bigint)
  • Buffers: shared hit=4
Planning time : 58.142 ms
Execution time : 98,551.648 ms