explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JVnI : 10_plan

Settings
# exclusive inclusive rows x rows loops node
1. 134.040 634,965.097 ↓ 610.0 60,999 1

Sort (cost=132.07..132.32 rows=100 width=1,768) (actual time=634,940.596..634,965.097 rows=60,999 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'::text, 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: 14904kB
  • Buffers: shared hit=9, temp read=12644 written=12640
  • Buffers: shared hit=6, temp read=2417 written=2418
2.          

CTE v_osversion_data

3. 320.397 3,526.519 ↓ 248,346.0 248,346 1

GroupAggregate (cost=25.17..25.30 rows=1 width=476) (actual time=3,073.552..3,526.519 rows=248,346 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,812.657 3,206.122 ↓ 24,839.9 248,399 1

Sort (cost=25.17..25.19 rows=10 width=530) (actual time=3,073.254..3,206.122 rows=248,399 loops=1)

  • Output: _dblink_phonebinary_common_1.model, _dblink_phonebinary_common_1.product_code, _dblink_phonebinary_common_1.sw_version, _dblink_phonebinary_common_1.osversion
  • Sort Key: _dblink_phonebinary_common_1.model, _dblink_phonebinary_common_1.product_code, _dblink_phonebinary_common_1.sw_version
  • Sort Method: external merge Disk: 19336kB
  • Buffers: shared hit=6, temp read=2417 written=2418
5. 1,393.465 1,393.465 ↓ 24,839.9 248,399 1

Foreign Scan on _dblink_phonebinary_common_1 (cost=15.00..25.00 rows=10 width=530) (actual time=0.438..1,393.465 rows=248,399 loops=1)

  • Output: _dblink_phonebinary_common_1.model, _dblink_phonebinary_common_1.product_code, _dblink_phonebinary_common_1.sw_version, _dblink_phonebinary_common_1.osversion
  • 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
6. 268.950 634,831.057 ↓ 610.0 60,999 1

Nested Loop Left Join (cost=80.32..103.44 rows=100 width=1,768) (actual time=633,211.086..634,831.057 rows=60,999 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'::text, 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: shared hit=6, temp read=10781 written=10777
7. 103.240 634,562.107 ↓ 6,099.9 60,999 1

Hash Left Join (cost=65.32..75.66 rows=10 width=1,578) (actual time=633,193.212..634,562.107 rows=60,999 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: shared hit=6, temp read=10781 written=10777
8. 110.114 634,290.669 ↓ 6,099.9 60,999 1

Hash Left Join (cost=65.29..75.50 rows=10 width=1,546) (actual time=633,024.990..634,290.669 rows=60,999 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: shared hit=6, temp read=4580 written=8616
9. 31.432 630,314.121 ↓ 6,099.9 60,999 1

Hash Left Join (cost=65.25..75.34 rows=10 width=1,514) (actual time=629,158.541..630,314.121 rows=60,999 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)
10. 46.284 630,282.598 ↓ 6,099.9 60,999 1

Hash Left Join (cost=40.12..50.17 rows=10 width=1,464) (actual time=629,158.439..630,282.598 rows=60,999 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)
11. 630,236.223 630,236.223 ↓ 6,099.9 60,999 1

Foreign Scan on _dblink_fusclientlog_2 a (cost=15.00..25.00 rows=10 width=1,394) (actual time=629,158.331..630,236.223 rows=60,999 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.logdateserver >= to_timestamp(concat('2019-10-26', '-', '090000000000'), 'YYYY-MM-DD-HH24MISSFF6'::text)) AND (a.resultcode <> ALL ('{K,L,M,N,O,P,Q}'::bpchar[])) AND (a.logdateserver < ((to_timestamp(concat('2019-10-26', '-', '090000000000'), 'YYYY-MM-DD-HH24MISSFF6'::text))::timestamp without time zone + 1)))
  • Rows Removed by Filter: 63403617
  • 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')
12. 0.013 0.091 ↑ 1.7 6 1

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

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

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

  • Output: c.description, c.upgrademode
  • Remote Query: SELECT upgrademode, description FROM fusclientupgrademode
14. 0.008 0.091 ↓ 1.4 14 1

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

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

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

  • Output: d.appname, d.appcode
  • Remote Query: SELECT appname, NULL, appcode FROM fusclientapp
16. 123.205 3,866.434 ↓ 248,346.0 248,346 1

Hash (cost=0.02..0.02 rows=1 width=158) (actual time=3,866.434..3,866.434 rows=248,346 loops=1)

  • Output: e.osversion, e.model, e.product_code, e.sw_version
  • Buckets: 262144 (originally 1024) Batches: 2 (originally 1) Memory Usage: 14363kB
  • Buffers: shared hit=6, temp read=2417 written=7812
17. 3,743.229 3,743.229 ↓ 248,346.0 248,346 1

CTE Scan on v_osversion_data e (cost=0.00..0.02 rows=1 width=158) (actual time=3,073.559..3,743.229 rows=248,346 loops=1)

  • Output: e.osversion, e.model, e.product_code, e.sw_version
  • Buffers: shared hit=6, temp read=2417 written=6455
18. 107.109 168.198 ↓ 248,346.0 248,346 1

Hash (cost=0.02..0.02 rows=1 width=158) (actual time=168.198..168.198 rows=248,346 loops=1)

  • Output: f.osversion, f.model, f.product_code, f.sw_version
  • Buckets: 262144 (originally 1024) Batches: 2 (originally 1) Memory Usage: 14363kB
  • Buffers: temp read=4039 written=1358
19. 61.089 61.089 ↓ 248,346.0 248,346 1

CTE Scan on v_osversion_data f (cost=0.00..0.02 rows=1 width=158) (actual time=0.014..61.089 rows=248,346 loops=1)

  • Output: f.osversion, f.model, f.product_code, f.sw_version
  • Buffers: temp read=4039 written=1
20. 0.000 0.000 ↑ 10.0 1 60,999

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

  • Output: b.description, b.accessmode
21. 0.182 0.182 ↑ 10.0 1 1

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

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