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. 213.354 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+++++++
  • -> Hash Left Join (cost=65.29..75.50 rows=10 width=1546) (actual time=633024.990..634290.669 rows=60999 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
8. 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)
9. 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)
10. 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')
11. 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
12. 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
13. 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
14. 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
15. 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
16. 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
17. 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
18. 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
19. 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
20. 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