postgresql - Deducing estimated time taken with EXPLAIN -


the following explain output of query set enable_seqscan = true.

 hash join  (cost=1028288.04..278841855100.04 rows=429471108 width=125)    hash cond: ((u.destination)::text = (n.mid)::text)    ->  nested loop  (cost=0.00..278587474234.17 rows=429471108 width=112)      join filter: (((u.destination)::text <> (u2.mid)::text) , ("position"((u2.path_name)::text, (suffix(u.path_name))::text) = 0) , (((prefix((u.path_name)::text))::text = (prefix((u2.path_name)::text))::text) or ((prefix((u.path_name)::text))::text = 'common'::text)))      ->  seq scan on unresolved u2  (cost=0.00..2780546.32 rows=117608632 width=79)      ->  index scan using unresolved__mid on unresolved u  (cost=0.00..1864.44 rows=492 width=53)            index cond: ((u.mid)::text = (u2.destination)::text)    ->  hash  (cost=488335.24..488335.24 rows=27237024 width=33)      ->  seq scan on name n  (cost=0.00..488335.24 rows=27237024 width=33)

(9 rows)

the following explain output of same query set enable_seqscan = false.

 hash join  (cost=102089128.45..279381508122.13 rows=429471108 width=125)    hash cond: ((u.destination)::text = (n.mid)::text)    ->  nested loop  (cost=0.00..279026066415.86 rows=429471108 width=112)      join filter: (((u.destination)::text <> (u2.mid)::text) , ("position"((u2.path_name)::text, (suffix(u.path_name))::text) = 0) , (((prefix((u.path_name)::text))::text = (prefix((u2.path_name)::text))::text) or ((prefix((u.path_name)::text))::text = 'common'::text)))      ->  index scan using unresolved__destination on unresolved u2  (cost=0.00..441372728.01 rows=117608632 width=79)      ->  index scan using unresolved__mid on unresolved u  (cost=0.00..1864.44 rows=492 width=53)            index cond: ((u.mid)::text = (u2.destination)::text)    ->  hash  (cost=101549175.65..101549175.65 rows=27237024 width=33)      ->  index scan using name_pkey on name n  (cost=0.00..101549175.65 rows=27237024 width=33)

(9 rows)

i know how long query take. it's been running 10 hours now. estimated time deduced 'cost' in first row, in case of latter '279381508122.13 ms' 8.8 years?! :-(

the numbers not correspond time. relative numbers only. documentation (using explain):

the costs measured in arbitrary units determined planner's cost parameters (see section 18.6.2). traditional practice measure costs in units of disk page fetches; is, seq_page_cost conventionally set 1.0 , other cost parameters set relative that. (the examples in section run default cost parameters.)

in event, nested loop due vague join condition appears killing performance. hard tell without seeing original query , table/index structures, might find benefit in creating functional index on unresolved, assuming "prefix()" immutable function:

create index idx_path_name_prefix on unresolved (prefix(path_name));

Comments

Post a Comment

Popular posts from this blog

c# - SharpSVN - How to get the previous revision? -

i trying find efficient way previous revision of file text comparison using sharpsvn. using (svnclient c = new svnclient()) { c.authentication.defaultcredentials = new networkcredential( configurationmanager.appsettings.get("svnserviceusername") , configurationmanager.appsettings.get("svnservicepassword") , configurationmanager.appsettings.get("svnservicedomain") ); c.authentication.sslservertrusthandlers += new eventhandler<svnsslservertrusteventargs>(authentication_sslservertrusthandlers); collection<svnfileversioneventargs> fileversioncollection = new collection<svnfileversioneventargs>(); svnrevisionrange range = new svnrevisionrange(0, this.hooks.revision); svnfileversionsargs args = new svnfileversionsargs(); args.retrieveproperties = true; args.range = range; foreach (svnchangeitem item in log.changedpaths) { string path = this.repositorypath + it...
Read more

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

url - Querystring manipulation of email Address in PHP -

i using third party application (email2http) send me emailid, body , message. unable retrieve email id due name appended on , enclosed < , >. here querystring getting. test.php?from=ajay+reddy &subject=cx&body=somebody when print variable in php returns name , not email id. $from = $_get['from']; echo $from; //output ajay reddy please suggest me how can emailid also. i dont want use post method due testing , debugging. it's working fine. forgot use htmlentities() in debugging.
Read more