Project

General

Profile

« Previous | Next » 

Revision 10376

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/REMIB/Specimen/postprocess.sql: map_nulls() derived cols: updated runtimes for map_nulls() inlining, which created a speed improvement of 7x for the numeric columns and 2.5x for the text columns (292563.362->41929.772 ms and 83640.424->35690.797 ms, respectively). note that the map_nulls__coord__*() calls could be optimized further by combining the successive map_nulls() calls into one, with the hstores merged.

View differences:

inputs/REMIB/Specimen/postprocess.sql
40 40 
  LANGUAGE sql IMMUTABLE
41 41 
  COST 100;
42 42 

  		




  43
  
  
    
SELECT mk_derived_col((:table_str, 'country'      ), $$map_nulls__text("country_verbatim"      )$$); -- runtime: 1.5 min ("Time: 83640.424 ms") @vegbiendev


  		




  44
  
  
    
SELECT mk_derived_col((:table_str, 'stateProvince'), $$map_nulls__text("stateProvince_verbatim")$$); -- runtime: 1.5 min ("Time: 84850.995 ms") @vegbiendev


  		




  45
  
  
    
SELECT mk_derived_col((:table_str, 'county'       ), $$map_nulls__text("county_verbatim"       )$$); -- runtime: 1.5 min ("Time: 82940.423 ms") @vegbiendev


  		




  46
  
  
    
SELECT mk_derived_col((:table_str, 'locality'     ), $$map_nulls__text("locality_verbatim"     )$$); -- runtime: 1.5 min ("Time: 85278.697 ms") @vegbiendev


  		




  47
  
  
    
SELECT mk_derived_col((:table_str, 'habitat'      ), $$map_nulls__text("habitat_verbatim"      )$$); -- runtime: 1   min ("Time: 75241.704 ms") @vegbiendev


  		




  
  43
  
    
SELECT mk_derived_col((:table_str, 'country'      ), $$map_nulls__text("country_verbatim"      )$$); -- runtime: 35 s ("Time: 35690.797 ms") @vegbiendev


  		




  
  44
  
    
SELECT mk_derived_col((:table_str, 'stateProvince'), $$map_nulls__text("stateProvince_verbatim")$$); -- runtime: 35 s ("Time: 36074.430 ms") @vegbiendev


  		




  
  45
  
    
SELECT mk_derived_col((:table_str, 'county'       ), $$map_nulls__text("county_verbatim"       )$$); -- runtime: 35 s ("Time: 36096.911 ms") @vegbiendev


  		




  
  46
  
    
SELECT mk_derived_col((:table_str, 'locality'     ), $$map_nulls__text("locality_verbatim"     )$$); -- runtime: 35 s ("Time: 36076.364 ms") @vegbiendev


  		




  
  47
  
    
SELECT mk_derived_col((:table_str, 'habitat'      ), $$map_nulls__text("habitat_verbatim"      )$$); -- runtime: 35 s ("Time: 35481.828 ms") @vegbiendev


  		




  48
  48
  
    

  		




  49
  49
  
    

  		




  50
  50
  
    
CREATE OR REPLACE FUNCTION map_nulls__coord__longitude(value text)


  		




  ......




  66
  66
  
    
  LANGUAGE sql IMMUTABLE


  		




  67
  67
  
    
  COST 100;


  		




  68
  68
  
    

  		




  69
  
  
    
SELECT mk_derived_col((:table_str, 'longitude_deg'), $$map_nulls__coord__longitude("longitude_deg_verbatim")$$); -- runtime: 3   min ("Time: 188247.487 ms") @vegbiendev


  		




  70
  
  
    
SELECT mk_derived_col((:table_str, 'longitude_min'), $$map_nulls__coord__other    ("longitude_min_verbatim")$$); -- runtime: 5.5 min ("Time: 292563.362 ms") @vegbiendev


  		




  71
  
  
    
SELECT mk_derived_col((:table_str, 'longitude_sec'), $$map_nulls__coord__other    ("longitude_sec_verbatim")$$); -- runtime: 5.5 min ("Time: 287815.892 ms") @vegbiendev


  		




  72
  
  
    
SELECT mk_derived_col((:table_str, 'latitude_deg' ), $$map_nulls__coord__other    ("latitude_deg_verbatim" )$$); -- runtime: 5.5 min ("Time: 292004.215 ms") @vegbiendev


  		




  73
  
  
    
SELECT mk_derived_col((:table_str, 'latitude_min' ), $$map_nulls__coord__other    ("latitude_min_verbatim" )$$); -- runtime: 5.5 min ("Time: 292807.322 ms") @vegbiendev


  		




  74
  
  
    
SELECT mk_derived_col((:table_str, 'latitude_sec' ), $$map_nulls__coord__other    ("latitude_sec_verbatim" )$$); -- runtime: 5.5 min ("Time: 288486.261 ms") @vegbiendev


  		




  
  69
  
    
SELECT mk_derived_col((:table_str, 'longitude_deg'), $$map_nulls__coord__longitude("longitude_deg_verbatim")$$); -- runtime: 40 s ("Time: 39417.099 ms") @vegbiendev


  		




  
  70
  
    
SELECT mk_derived_col((:table_str, 'longitude_min'), $$map_nulls__coord__other    ("longitude_min_verbatim")$$); -- runtime: 40 s ("Time: 41929.772 ms") @vegbiendev


  		




  
  71
  
    
SELECT mk_derived_col((:table_str, 'longitude_sec'), $$map_nulls__coord__other    ("longitude_sec_verbatim")$$); -- runtime: 40 s ("Time: 42106.205 ms") @vegbiendev


  		




  
  72
  
    
SELECT mk_derived_col((:table_str, 'latitude_deg' ), $$map_nulls__coord__other    ("latitude_deg_verbatim" )$$); -- runtime: 40 s ("Time: 42187.294 ms") @vegbiendev


  		




  
  73
  
    
SELECT mk_derived_col((:table_str, 'latitude_min' ), $$map_nulls__coord__other    ("latitude_min_verbatim" )$$); -- runtime: 40 s ("Time: 43220.851 ms") @vegbiendev


  		




  
  74
  
    
SELECT mk_derived_col((:table_str, 'latitude_sec' ), $$map_nulls__coord__other    ("latitude_sec_verbatim" )$$); -- runtime: 40 s ("Time: 42267.566 ms") @vegbiendev


  		












Also available in:  Unified diff