explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hPS8 : 9_plan

Settings
# exclusive inclusive rows x rows loops node
1. 83.939 3,930.604 ↓ 610.0 61,001 1

Sort (cost=131.87..132.12 rows=100 width=1,648) (actual time=3,912.991..3,930.604 rows=61,001 loops=1)

  • Output: (((a.imeinum)::text || (edb_ora_timestamp_tochar_immutable(a.logdateserver, 'YYYYMMDDHH24MISS'::character varying))::text)), (edb_ora_timestamp_tochar_immutable(a.logdateserver, 'YYYY/MM/DD HH24:MI:SS'::character varying)), a.localcode, a.buyercode, a.model, ('IMEI'), a.imeinum, a.totaldltime, b.description, c.description, (CASE WHEN (a.resultcode = ANY ('{0,1,C,T}'::bpchar[])) THEN 'S'::text ELSE ''::text END), a.currentversion, a.updateversion, d.appname, a.clientversion, a.agentversion, a.pluginversion, e.osversion, f.osversion, a.webdltime, a.phonedltime, a.logdateserver
  • Sort Key: a.logdateserver
  • Sort Method: external sort Disk: 14888kB
  • Buffers: shared hit=3, temp read=10290 written=10285
2.          

CTE v_osversion_data

3. 409.922 1,938.732 ↓ 250,628.0 250,628 1

HashAggregate (cost=25.10..25.11 rows=1 width=530) (actual time=1,739.417..1,938.732 rows=250,628 loops=1)

  • Output: _dblink_phonebinary_common_1.model, _dblink_phonebinary_common_1.product_code, replace((_dblink_phonebinary_common_1.sw_version)::text, '~'::text, '/'::text), max((_dblink_phonebinary_common_1.osversion)::text), _dblink_phonebinary_common_1.sw_version
  • Group Key: _dblink_phonebinary_common_1.model, _dblink_phonebinary_common_1.product_code, _dblink_phonebinary_common_1.sw_version
4. 1,528.810 1,528.810 ↓ 25,068.1 250,681 1

Foreign Scan on pg_temp_17._dblink_phonebinary_common_1 (cost=15.00..25.00 rows=10 width=530) (actual time=0.438..1,528.810 rows=250,681 loops=1)

  • Output: _dblink_phonebinary_common_1.binary_key, _dblink_phonebinary_common_1.sw_version, _dblink_phonebinary_common_1.version_number, _dblink_phonebinary_common_1.crc, _dblink_phonebinary_common_1.site_code, _dblink_phonebinary_common_1.nation_code, _dblink_phonebinary_common_1.product_code, _dblink_phonebinary_common_1.model, _dblink_phonebinary_common_1.encrypt_string, _dblink_phonebinary_common_1.path, _dblink_phonebinary_common_1.status, _dblink_phonebinary_common_1.user_id, _dblink_phonebinary_common_1.rel_desc, _dblink_phonebinary_common_1.last_modified, _dblink_phonebinary_common_1.sw_index, _dblink_phonebinary_common_1.reserved1, _dblink_phonebinary_common_1.reserved2, _dblink_phonebinary_common_1.reserved3, _dblink_phonebinary_common_1.do_notify, _dblink_phonebinary_common_1.display_version, _dblink_phonebinary_common_1.direct_support, _dblink_phonebinary_common_1.direct_version, _dblink_phonebinary_common_1.direct_product_code, _dblink_phonebinary_common_1.direct_buyer_code, _dblink_phonebinary_common_1.versionseq, _dblink_phonebinary_common_1.blockclientapp, _dblink_phonebinary_common_1.blockclientver, _dblink_phonebinary_common_1.blockenable, _dblink_phonebinary_common_1.delflag, _dblink_phonebinary_common_1.registerer, _dblink_phonebinary_common_1.regdate, _dblink_phonebinary_common_1.fileexist, _dblink_phonebinary_common_1.uuser, _dblink_phonebinary_common_1.udate, _dblink_phonebinary_common_1.svcstartdate, _dblink_phonebinary_common_1.svcstartdone, _dblink_phonebinary_common_1.svcstartstatus, _dblink_phonebinary_common_1.dlallowedcnt, _dblink_phonebinary_common_1.dlcnt, _dblink_phonebinary_common_1.noticepopup, _dblink_phonebinary_common_1.osversion, _dblink_phonebinary_common_1.binarytype, _dblink_phonebinary_common_1.versiongrp, _dblink_phonebinary_common_1.versiontemp, _dblink_phonebinary_common_1.macstatus, _dblink_phonebinary_common_1.macsvcstartdate, _dblink_phonebinary_common_1.macsvcstartdone, _dblink_phonebinary_common_1.macsvcstartstatus, _dblink_phonebinary_common_1.svcstartuser, _dblink_phonebinary_common_1.macsvcstartuser, _dblink_phonebinary_common_1.bnrsupport, _dblink_phonebinary_common_1.transfer, _dblink_phonebinary_common_1.fotaverification, _dblink_phonebinary_common_1.smartstatus, _dblink_phonebinary_common_1.smartsvcstartdate, _dblink_phonebinary_common_1.smartsvcstartdone, _dblink_phonebinary_common_1.smartsvcstartstatus, _dblink_phonebinary_common_1.smartsvcstartuser, _dblink_phonebinary_common_1.fileexist2, _dblink_phonebinary_common_1.crc2, _dblink_phonebinary_common_1.path2, _dblink_phonebinary_common_1.dummy, _dblink_phonebinary_common_1.foolproof, _dblink_phonebinary_common_1.binarysize, _dblink_phonebinary_common_1.fac_binarysize, _dblink_phonebinary_common_1.noticeurl1, _dblink_phonebinary_common_1.noticeurl2, _dblink_phonebinary_common_1.reqmodel, _dblink_phonebinary_common_1.reqproductcode, _dblink_phonebinary_common_1.reqswversion, _dblink_phonebinary_common_1.reqno, _dblink_phonebinary_common_1.skulocalcode, _dblink_phonebinary_common_1.binarynature, _dblink_phonebinary_common_1.uploadtype, _dblink_phonebinary_common_1.logicvaluehome, _dblink_phonebinary_common_1.logicoptionhome, _dblink_phonebinary_common_1.logicoptionfac, _dblink_phonebinary_common_1.logicvaluefac, _dblink_phonebinary_common_1.omcfactory, _dblink_phonebinary_common_1.fb_check, _dblink_phonebinary_common_1.upgrade_check, _dblink_phonebinary_common_1.smart_check
  • Remote Query: SELECT NULL, sw_version, NULL, NULL, NULL, NULL, product_code, model, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, osversion, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM phonebinary_common
5. 824.656 3,846.665 ↓ 610.0 61,001 1

Nested Loop Left Join (cost=80.32..103.44 rows=100 width=1,648) (actual time=2,276.094..3,846.665 rows=61,001 loops=1)

  • Output: ((a.imeinum)::text || (edb_ora_timestamp_tochar_immutable(a.logdateserver, 'YYYYMMDDHH24MISS'::character varying))::text), edb_ora_timestamp_tochar_immutable(a.logdateserver, 'YYYY/MM/DD HH24:MI:SS'::character varying), a.localcode, a.buyercode, a.model, 'IMEI', a.imeinum, a.totaldltime, b.description, c.description, CASE WHEN (a.resultcode = ANY ('{0,1,C,T}'::bpchar[])) THEN 'S'::text ELSE ''::text END, a.currentversion, a.updateversion, d.appname, a.clientversion, a.agentversion, a.pluginversion, e.osversion, f.osversion, a.webdltime, a.phonedltime, a.logdateserver
  • Join Filter: (a.accessmode = (b.accessmode)::bpchar)
  • Buffers: temp read=8429 written=8424
6. 73.282 3,022.009 ↓ 6,100.1 61,001 1

Hash Left Join (cost=65.32..75.66 rows=10 width=1,578) (actual time=2,275.918..3,022.009 rows=61,001 loops=1)

  • Output: a.imeinum, a.logdateserver, a.localcode, a.buyercode, a.model, a.totaldltime, a.resultcode, a.currentversion, a.updateversion, a.clientversion, a.agentversion, a.pluginversion, a.webdltime, a.phonedltime, a.accessmode, c.description, d.appname, e.osversion, f.osversion
  • Hash Cond: (((a.model)::text = (f.model)::text) AND ((a.localcode)::bpchar = f.product_code) AND ((a.updateversion)::text = f.sw_version))
  • Buffers: temp read=8429 written=8424
7. 76.147 2,822.663 ↓ 6,100.1 61,001 1

Hash Left Join (cost=65.29..75.50 rows=10 width=1,546) (actual time=2,149.717..2,822.663 rows=61,001 loops=1)

  • Output: a.imeinum, a.logdateserver, a.localcode, a.buyercode, a.model, a.totaldltime, a.resultcode, a.currentversion, a.updateversion, a.clientversion, a.agentversion, a.pluginversion, a.webdltime, a.phonedltime, a.accessmode, c.description, d.appname, e.osversion
  • Hash Cond: (((a.model)::text = (e.model)::text) AND ((a.localcode)::bpchar = e.product_code) AND ((a.currentversion)::text = e.sw_version))
  • Buffers: temp read=2176 written=6250
8. 18.210 600.285 ↓ 6,100.1 61,001 1

Hash Left Join (cost=65.25..75.34 rows=10 width=1,514) (actual time=3.367..600.285 rows=61,001 loops=1)

  • Output: a.imeinum, a.logdateserver, a.localcode, a.buyercode, a.model, a.totaldltime, a.resultcode, a.currentversion, a.updateversion, a.clientversion, a.agentversion, a.pluginversion, a.webdltime, a.phonedltime, a.accessmode, c.description, d.appname
  • Hash Cond: (a.clientapp = d.appcode)
9. 18.821 581.562 ↓ 6,100.1 61,001 1

Hash Left Join (cost=40.12..50.17 rows=10 width=1,464) (actual time=2.840..581.562 rows=61,001 loops=1)

  • Output: a.imeinum, a.logdateserver, a.localcode, a.buyercode, a.model, a.totaldltime, a.resultcode, a.currentversion, a.updateversion, a.clientversion, a.agentversion, a.pluginversion, a.webdltime, a.phonedltime, a.accessmode, a.clientapp, c.description
  • Hash Cond: (a.upgrademode = (c.upgrademode)::bpchar)
10. 561.688 561.688 ↓ 6,100.1 61,001 1

Foreign Scan on pg_temp_17._dblink_fusclientlog_2 a (cost=15.00..25.00 rows=10 width=1,394) (actual time=1.770..561.688 rows=61,001 loops=1)

  • Output: a.model, a.imeinum, a.buyercode, a.localcode, a.currentversion, a.updateversion, a.webdltime, a.phonedltime, a.totaldltime, a.logdateclient, a.logdateserver, a.localip, a.localmemsize, a.localosversion, a.localcpukind, a.upgrademode, a.clientapp, a.clientversion, a.agentname, a.agentversion, a.pluginname, a.pluginversion, a.resultcode, a.accessmode, a.localoslang, a.localusbhostctlr, a.localmotherboard, a.localusbhostctlrid, a.stress, a.predown, a.smartid, a.binarynature, a.foolproof, a.snused, a.interneterror, a.binarysizeserver, a.binarysizetransfered, a.upgradevariable, a.ccode, a.unnum, a.reason4fac, a.commenttext, a.octacellid, a.dvifmodel, a.dvifunnum
  • Filter: (a.resultcode <> ALL ('{K,L,M,N,O,P,Q}'::bpchar[]))
  • Remote Query: SELECT model, imeinum, buyercode, localcode, currentversion, updateversion, webdltime, phonedltime, totaldltime, NULL, logdateserver, NULL, NULL, NULL, NULL, upgrademode, clientapp, clientversion, NULL, agentversion, NULL, pluginversion, resultcode, accessmode, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM fusclientlog WHERE ((imeinum)::text <> 'WEBDL') AND ((imeinum)::text NOT LIKE '%?%') AND (totaldltime <= 21600) AND (totaldltime >= 0) AND (webdltime <= 21600) AND (webdltime >= 0) AND (phonedltime <= 21600) AND (phonedltime >= 0) AND (accessmode = '2') AND (logdateserver >= to_timestamp(concat('2019-10-26', '-', '090000000000'), 'YYYY-MM-DD-HH24MISSFF6')) AND (logdateserver < ((to_timestamp(concat('2019-10-26', '-', '090000000000'), 'YYYY-MM-DD-HH24MISSFF6'))::timestamp without time zone + 1))
11. 0.003 1.053 ↑ 1.7 6 1

Hash (cost=25.00..25.00 rows=10 width=86) (actual time=1.053..1.053 rows=6 loops=1)

  • Output: c.description, c.upgrademode
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 1.050 1.050 ↑ 1.7 6 1

Foreign Scan on pg_temp_17._dblink_fusclientupgrademode_4 c (cost=15.00..25.00 rows=10 width=86) (actual time=1.046..1.050 rows=6 loops=1)

  • Output: c.description, c.upgrademode
  • Remote Query: SELECT upgrademode, description FROM fusclientupgrademode
13. 0.007 0.513 ↓ 1.4 14 1

Hash (cost=25.00..25.00 rows=10 width=66) (actual time=0.513..0.513 rows=14 loops=1)

  • Output: d.appname, d.appcode
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.506 0.506 ↓ 1.4 14 1

Foreign Scan on pg_temp_17._dblink_fusclientapp_5 d (cost=15.00..25.00 rows=10 width=66) (actual time=0.502..0.506 rows=14 loops=1)

  • Output: d.appname, d.appcode
  • Remote Query: SELECT appname, NULL, appcode FROM fusclientapp
15. 83.424 2,146.231 ↓ 250,628.0 250,628 1

Hash (cost=0.02..0.02 rows=1 width=158) (actual time=2,146.231..2,146.231 rows=250,628 loops=1)

  • Output: e.osversion, e.model, e.product_code, e.sw_version
  • Buckets: 262144 (originally 1024) Batches: 2 (originally 1) Memory Usage: 14479kB
  • Buffers: temp written=5446
16. 2,062.807 2,062.807 ↓ 250,628.0 250,628 1

CTE Scan on v_osversion_data e (cost=0.00..0.02 rows=1 width=158) (actual time=1,739.419..2,062.807 rows=250,628 loops=1)

  • Output: e.osversion, e.model, e.product_code, e.sw_version
  • Buffers: temp written=4076
17. 78.731 126.064 ↓ 250,628.0 250,628 1

Hash (cost=0.02..0.02 rows=1 width=158) (actual time=126.064..126.064 rows=250,628 loops=1)

  • Output: f.osversion, f.model, f.product_code, f.sw_version
  • Buckets: 262144 (originally 1024) Batches: 2 (originally 1) Memory Usage: 14479kB
  • Buffers: temp read=4078 written=1371
18. 47.333 47.333 ↓ 250,628.0 250,628 1

CTE Scan on v_osversion_data f (cost=0.00..0.02 rows=1 width=158) (actual time=0.013..47.333 rows=250,628 loops=1)

  • Output: f.osversion, f.model, f.product_code, f.sw_version
  • Buffers: temp read=4078 written=1
19. 0.000 0.000 ↑ 10.0 1 61,001

Materialize (cost=15.00..25.05 rows=10 width=86) (actual time=0.000..0.000 rows=1 loops=61,001)

  • Output: b.description, b.accessmode
20. 0.133 0.133 ↑ 10.0 1 1

Foreign Scan on pg_temp_17._dblink_fusclientaccessmode_3 b (cost=15.00..25.00 rows=10 width=86) (actual time=0.133..0.133 rows=1 loops=1)

  • Output: b.description, b.accessmode
  • Remote Query: SELECT accessmode, description FROM fusclientaccessmode WHERE ((accessmode)::bpchar = '2')
Planning time : 1.138 ms
Execution time : 3,978.691 ms