| /*------------------------------------------------------------------------- |
| * |
| * HashVsHash.sq |
| * Cost model visualization |
| * |
| * Required software: Sysquake LE, which may be downloaded for free from |
| * http://www.calerga.com/products/Sysquake/index.html |
| * This script was developed using Sysquake 3.6 LE on MS Windows. |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| * |
| *------------------------------------------------------------------------- |
| */ |
| title "HashVsHash cost model" |
| help |
| {@ |
| This Sysquake LE script plots the results of the PostgreSQL or |
| Greenplum DB optimizer's cost models for comparison of two alternative |
| plans: hash join between T (outer) and U (inner), and hash join between |
| U (outer) and T (inner). |
| |
| The intended users are database kernel developers and performance |
| analysts who are familiar with the concepts and source code of these |
| optimizers. |
| |
| The "Parameters" area can be scrolled using a mouse wheel or the |
| hand cursor to bring all the controls into view. Unneeded controls |
| can be hidden using the "Show controls" checkboxes. |
| |
| Kurt Harriman, greenplum inc |
| @} |
| version |
| {@ |
| The cost model is thought to be mostly up-to-date with the current |
| versions of PostgreSQL and Greenplum DB as of |
| March 29, 2007 |
| @} |
| |
| variable relT |
| variable relU |
| variable joinsel |
| variable joinsize |
| |
| // GUCs and #defines |
| variable costgucs |
| variable work_mem |
| variable NTUP_PER_BUCKET |
| variable blcksz |
| // blcksz(1) = BLCKSZ = current blocksize for cost model; adjustable via slider |
| // blcksz(2) same as (1) while modelVersion is a pg version, else stays frozen |
| // blcksz(3) false to keep (2) frozen; true to update (2) when (1) changes |
| |
| // calculated |
| variable hashbuildT |
| variable hashbuildU |
| variable joincostTU |
| variable joincostUT |
| |
| // keyboard input |
| variable scaleflag |
| |
| // platform dependent |
| define MAXIMUM_ALIGNOF = 8 |
| define sizeof_ptr = 8 |
| |
| // initial parameter values |
| define BLCKSZ_default = 8192 // for PostgreSQL versions |
| define rows_default = 1000 |
| define rowSize_default = 100 |
| |
| // heap page capacity |
| define sizeof_PageHeaderData = 20 // storage/bufpage.h, up to pd_linp (first ItemId) |
| define MaxSpecialSpace = 32 // access/htup.h |
| define sizeof_ItemIdData = 4 // storage/itemid.h |
| |
| // constants |
| define INT_MAX = 2147483647 |
| |
| // visible control group ids |
| variable _nondumpable visibleControls |
| define vc_explain_id = _auto |
| define vc_gucs_id = _auto |
| define vc_joinsel_id = _auto |
| define vc_quals_id = _auto |
| define vc_sizeadj_id = _auto |
| define vc_subplans_id = _auto |
| define vc_vers_id = _auto |
| |
| // slider control ids |
| define blcksz_id = _auto |
| define explain_rows_id = _auto |
| define explain_rowSize_id = _auto |
| define explain_scanCost_id = _auto |
| define gucs_id = _auto |
| define joinrows_id = _auto |
| define ntup_per_bucket_id = _auto |
| define quals_id = _auto |
| define relT_id = _auto |
| define relU_id = _auto |
| define relT_rows_id = _auto |
| define relU_rows_id = _auto |
| define relT_rowSize_id = _auto |
| define relU_rowSize_id = _auto |
| define work_mem_id = _auto |
| |
| // button and checkbox control ids |
| define modelVersion_id1 = _auto |
| define modelVersion_id2 = _auto |
| define modelVersion_id3 = _auto |
| define modelVersion_id4 = _auto |
| define modelVersion_idN = modelVersion_id4 |
| define visibleControls_id1 = _auto |
| define visibleControls_id2 = _auto |
| define visibleControls_id3 = _auto |
| define visibleControls_id4 = _auto |
| define visibleControls_idN = visibleControls_id4 |
| define optionCheckboxes_id1 = _auto |
| define optionCheckboxes_id2 = _auto |
| define optionCheckboxes_id3 = _auto |
| define optionCheckboxes_idN = optionCheckboxes_id3 |
| |
| // plot & line control ids |
| define outer_rows_id = _auto |
| define inner_rows_id = _auto |
| define joincost_id = _auto |
| define joincostTU_id = _auto |
| define joincostUT_id = _auto |
| define T_rows_id = _auto |
| define U_rows_id = _auto |
| define xytrace_id = _auto |
| |
| // option checkboxes |
| variable _nondumpable optionCheckboxes |
| define oc_spilltweak = _auto |
| |
| // cost model version ids |
| variable _nondumpable modelVersion // enumerator struct for the chosen version |
| variable _nondumpable modelVersionChooser |
| define ver_pg815 = _auto |
| define ver_pg820 = _auto // pg 8.2.0 uses costsize.c version 1.169 |
| // This version introduced the MinimalTuple format |
| // for HJ workfiles etc., with shorter headers. |
| // See htup.h version 1.83 / MAIN:tgl:20060627213120 |
| // Adds seq_page_cost GUC - see costsize.c 1.156 / MAIN:tgl:20060605024958 |
| // pg 8.2.1 uses costsize.c version 1.169.2.1 / |
| // REL8_2_STABLE:tgl:20061215184235 - Skipping |
| // 8.2.1 here because HJ costing isn't affected |
| define ver_pg822 = _auto // pg 8.2.2 thru 8.2.4 all use costsize.c version 1.169.2.2 |
| // REL8_2_STABLE:tgl:20070108160931 |
| // Removes bias against HJ with inner rel larger than outer. |
| define ver_pg830 = _auto // pg 8.3.0 is still in development at this writing. |
| // At present this visualization reflects unreleased |
| // costsize.c version 1.179 dated 2007-03-27. |
| // Stored tuple header size reduced - see... |
| // htup.h ver 1.89 / MAIN:momjian:20070109220059 |
| // htup.h ver 1.91 / MAIN:tgl:20070209033533 |
| define ver_pgmin = ver_pg815 |
| define ver_pgmax = ver_pg830 |
| define ver_gp231 = _auto // gp 2.3.1 is based on pg 8.1.1 |
| // uses costsize.c version 1.15.2.5 / Release-2_3_0-branch:kharriman:20070227024315 |
| define ver_gp300 = _auto // gp 3.0 is based on pg 8.2.3 |
| // BLCKSZ increased to 32K - see pg_config_manual.h ver 1.6 / MAIN:cmcdevitt:20070206024456 |
| // uses costsize.c version 1.29 |
| // Removes bias against HJ with inner rel larger than outer. |
| // Adds seq_page_cost GUC - see costsize.c 1.156 / MAIN:tgl:20060605024958 |
| // Stored tuple header size reduced - see... |
| // pg htup.h ver 1.89 / MAIN:momjian:20070109220059 (will be in 8.3) |
| // pg htup.h ver 1.91 / MAIN:tgl:20070209033533 (will be in 8.3) |
| // gp htup.h ver 1.6 / MAIN:gsherry:20070301024612 |
| // Num of buckets for hash join - new formula |
| // nodeHash.c ver 1.11 / MAIN:tkordas:20070313184638 |
| // gp 3.0.0.1 skipped - no HJ costing changes |
| define ver_gp3002 = _auto // uses costsize.c version 1.29.6.5 |
| // Adds gp_cost_hashjoin_chainwalk GUC, off by default - see |
| // costsize.c ver 1.29.6.4 / Release-3_0_0-branch:jzhang:20070816011436 |
| // "#define NTUP_PER_BUCKET 10" replaced by gp_hashjoin_tuples_per_bucket guc with default value 5 |
| // nodeHash.c ver 1.15.2.4 / Release-3_0_0-branch:tkordas:20070816172047 |
| // nodeHash.c ver 1.15.2.5 / Release-3_0_0-branch:tkordas:20070816190818 |
| // nodeHash.c ver 1.19 / MAIN:tkordas:20070816174001 |
| // nodeHash.c ver 1.20 / MAIN:tkordas:20070816191722 |
| // No other changes relevant to HJ cost. |
| define ver_gpmin = ver_gp231 |
| define ver_gpmax = ver_gp3002 |
| //define ver_default = ver_gpmax |
| define ver_default = ver_gp300 |
| |
| // bit bucket for unused result of multiple assignment |
| variable _nondumpable unused |
| |
| |
| //--------------------------------------------------------------------// |
| // Initialization of globals // |
| //--------------------------------------------------------------------// |
| |
| init ( relT, relU, ... |
| joinsel, joinsize, ... |
| costgucs, work_mem, blcksz, NTUP_PER_BUCKET, ... |
| modelVersion, modelVersionChooser, optionCheckboxes, ... |
| visibleControls, ... |
| scaleflag, unused ) = init |
| function |
| {@ |
| function ( relT, relU, ... |
| joinsel, joinsize, ... |
| costgucs, work_mem, blcksz, NTUP_PER_BUCKET, ... |
| modelVersion, modelVersionChooser, optionCheckboxes, ... |
| visibleControls, ... |
| scaleflag, unused ) = ... |
| init |
| subplots('HashVsHash\tControl Panel\nT HJ U\tHash Build (U inner)\nU HJ T\tHash Build (T inner)'); |
| scaleflag = 0; |
| unused = []; |
| |
| ( modelVersion, modelVersionChooser, blcksz, NTUP_PER_BUCKET ) = ... |
| initVersions( ver_default, BLCKSZ_default ); |
| optionCheckboxes = optionCheckboxes_init(); |
| visibleControls = visibleControls_init(); |
| |
| costgucs = costgucs_init(); |
| work_mem = 128 * 1024; // gp default is 128 MB |
| |
| relT = rel_init( 'T', 'c', rows_default * 10, rowSize_default, costgucs.cpu_tuple_cost ); |
| relU = rel_init( 'U', 'y', rows_default, rowSize_default / 10, costgucs.cpu_tuple_cost ); |
| |
| joinsize = joinsize_init( relT.initialRows, relU.initialRows, relT.initialRows ); |
| joinsel = joinsel_init( joinsize.joinRows / joinsize.xprodRows, 1, 0 ); |
| @} |
| |
| |
| //********************************************************************// |
| // // |
| // Structs // |
| // // |
| //********************************************************************// |
| |
| //--------------------------------------------------------------------// |
| // PostgreSQL and Greenplum versions supported // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function ( modelVersion, modelVersionChooser, blcksz, NTUP_PER_BUCKET ) = ... |
| initVersions( ver_default, BLCKSZ_default ) |
| versionPrototype = version_init([], 'versionPrototype', []); |
| versionPrototype.sizeof_HeapTupleHeaderData = MAXALIGN(27); |
| versionPrototype.sizeof_MinimalTupleData = versionPrototype.sizeof_HeapTupleHeaderData; |
| versionPrototype.NTUP_PER_BUCKET = 10; |
| |
| versionEnumerators = {}; |
| versionEnumerators = join(versionEnumerators, initPgVersionEnumerators( versionPrototype )); |
| versionEnumerators = join(versionEnumerators, initGpVersionEnumerators( versionPrototype )); |
| |
| modelVersionChooser = enumChooser_init( versionEnumerators, ... |
| 'Version of cost model', 4, ... |
| modelVersion_id1 : modelVersion_idN ); |
| |
| modelVersion_initial = enumChooser_id_to_enum( modelVersionChooser, ver_default ); |
| |
| blcksz = [ BLCKSZ_default, BLCKSZ_default, true ]; |
| ( modelVersion, blcksz, NTUP_PER_BUCKET ) = modelVersion_set( modelVersion_initial, blcksz ); |
| |
| function versions = ... |
| initPgVersionEnumerators(v) |
| v = version_init( v, 'pg 8.1.5', ver_pg815 ); |
| versions{end+1} = v; |
| |
| v = version_init( v, 'pg 8.2.0', ver_pg820 ); |
| v.sizeof_MinimalTupleData = MAXALIGN(12); // MAIN:tgl:20060627213120 |
| versions{end+1} = v; |
| |
| v = version_init( v, 'pg 8.2.2', ver_pg822 ); |
| versions{end+1} = v; |
| |
| v = version_init( v, 'pg 8.3.0', ver_pg830 ); |
| v.sizeof_HeapTupleHeaderData = MAXALIGN(23); // MAIN:tgl:20070209033533 |
| versions{end+1} = v; |
| |
| function versions = ... |
| initGpVersionEnumerators(v) |
| v = version_init( v, 'gp 2.3.1', ver_gp231 ); |
| v.BLCKSZ = 8*1024; |
| versions{end+1} = v; |
| |
| v = version_init( v, 'gp 3.0', ver_gp300 ); |
| v.BLCKSZ = 32*1024; // MAIN:cmcdevitt:20070206024456 (pg_config_manual.h 1.6) |
| v.sizeof_MinimalTupleData = MAXALIGN(12); // MAIN:tgl:20060627213120 |
| v.sizeof_HeapTupleHeaderData = MAXALIGN(23); // MAIN:gsherry:20070301024612 |
| versions{end+1} = v; |
| |
| v = version_init( v, 'gp 3.0.0.2', ver_gp3002 ); |
| v.NTUP_PER_BUCKET = 5; // nodeHash.c ver 1.15.2.5 / Release-3_0_0-branch:tkordas:20070816190818 |
| versions{end+1} = v; |
| |
| function ( modelVersion, blcksz, NTUP_PER_BUCKET ) = ... |
| modelVersion_set( newVersion, blcksz ) |
| if isfield( newVersion, 'BLCKSZ' ) |
| // Reset BLCKSZ to version's default when switching to a gp version. |
| blcksz = [ newVersion.BLCKSZ, blcksz(2), false ]; |
| else |
| // Don't reset BLCKSZ when switching between pg versions. |
| // When switching from gp to pg, restore latest pg BLCKSZ setting. |
| blcksz = [ blcksz(2), blcksz(2), true ]; |
| end |
| NTUP_PER_BUCKET = newVersion.NTUP_PER_BUCKET; |
| modelVersion = newVersion; |
| |
| function versionEnumerator = ... |
| version_init( copyFromVersion, enum_name, enum_id ) |
| versionEnumerator = enumerator_init(enum_name, enum_id); |
| if ~isempty(copyFromVersion) |
| for fieldname = fieldnames(copyFromVersion) |
| if ~isfield(versionEnumerator, fieldname) |
| versionEnumerator.(fieldname) = copyFromVersion.(fieldname); |
| end |
| end |
| end |
| |
| function blcksz = ... |
| blcksz_set( blcksz, newBLCKSZ ) |
| // If pg version, save a copy of the BLCKSZ for modelVersion_set() |
| if blcksz(3) |
| blcksz(2) = newBLCKSZ; |
| end |
| blcksz(1) = newBLCKSZ; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // costgucs // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function costgucs = ... |
| costgucs_init |
| costgucs = struct(); |
| costgucs.cpu_tuple_cost = 0.01; |
| costgucs.cpu_operator_cost = 0.0025; |
| costgucs.seq_page_cost = 1; |
| costgucs.hj_chainwalk_weight = 1; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // optionCheckboxes // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function optionCheckboxes = ... |
| optionCheckboxes_init() |
| enums = {}; |
| |
| enums{end+1} = enumerator_init( 'Batch 0 spill cost ' , oc_spilltweak ); |
| // add items here |
| |
| optionChooser = enumChooser_init( enums, ... |
| 'Tweaks', 2, ... |
| optionCheckboxes_id1 : optionCheckboxes_idN ); |
| optionCheckboxes = checkboxes_init( [], optionChooser ); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // rel // |
| //--------------------------------------------------------------------// |
| // NB. User can set initialRows, initialRowSize and initialScanCost from |
| // an EXPLAIN report. From initialRows and initialScanCost we compute |
| // scanCostPerRow. Subsequently we compute the join's subplan cost as |
| // scanCost = rows * scanCostPerRow; this is added into the join cost. |
| // 'rows' is initialized from initialRows but may then be changed by |
| // the user to experiment with varying the amount of data entering the |
| // join. As 'rows' varies, we update the scanCost accordingly using the |
| // scanCostPerRow derived from initialRows and initialCost. |
| function |
| {@ |
| function rel = ... |
| rel_init( name, rowLineStyle, rows, rowSize, scanCostPerRow ) |
| rel = struct(); |
| rel.name = name; |
| rel.rows = rows; |
| rel.rowSize = MAXALIGN( rowSize ); |
| rel.rowLineStyle = rowLineStyle; |
| |
| rel.initialRows = rows; |
| rel.initialRowSize = rowSize; |
| rel.initialScanCost = rows * scanCostPerRow; |
| rel.scanCost = rel.initialScanCost; |
| rel.scanCostPerRow = scanCostPerRow; |
| |
| function ( rel, joinsel, joinsize ) = ... |
| rel_initialRows_drag( rel_old, whichrel, joinsel_old, joinsize_old, nb, x1 ) |
| if isempty( nb ) || x1 <= 0 |
| cancel; |
| end |
| rel = rel_old; |
| joinsel = joinsel_old; |
| joinsize = joinsize_old; |
| rows = ceil( x1 ); |
| if whichrel == 1 |
| joinsize = joinsize_init( rows, joinsize.rowsU, joinsize.joinRows ); |
| else |
| joinsize = joinsize_init( joinsize.rowsT, rows, joinsize.joinRows ); |
| end |
| rel.rows = rows; |
| rel.initialRows = rows; |
| rel = rel_initialScanCost_drag( rel, 0, rel.initialScanCost ); |
| joinsel.selectivity = joinsize.joinRows / joinsize.xprodRows; |
| |
| function rel = ... |
| rel_initialRowSize_drag( rel_old, nb, x1 ) |
| if isempty( nb ) || x1 < 0 |
| cancel; |
| end |
| rel = rel_old; |
| rel.initialRowSize = ceil( x1 ); |
| rel.rowSize = MAXALIGN( rel.initialRowSize ); |
| |
| function rel = ... |
| rel_initialScanCost_drag( rel_old, nb, x1 ) |
| if isempty( nb ) || x1 < 0 |
| cancel; |
| end |
| rel = rel_old; |
| rel.initialScanCost = x1; |
| rel.scanCostPerRow = rel.initialScanCost / rel.initialRows; |
| rel.scanCost = rel.rows * rel.scanCostPerRow; |
| |
| function rel = ... |
| rel_rows_drag( rel_old, nb, x1 ) |
| if isempty( nb ) |
| cancel; |
| end |
| rel = rel_old; |
| rel.rows = ceil( x1 ); |
| rel.scanCost = rel.rows * rel.scanCostPerRow; |
| |
| function ( msg, rel ) = ... |
| rel_rows_dragmsg( rel, nb, x1 ) |
| rel = rel_rows_drag( rel, nb, x1 ); |
| msg = rel_rows_mouseover( rel, rel.rows ); |
| |
| function ( msg, cursor ) = ... |
| rel_rows_mouseover( rel, x0 ) |
| msg = sprintf( '%s = %d rows', rel.name, x0 ); |
| cursor = true; |
| |
| function rel = ... |
| rel_rowSize_drag( rel_old, nb, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| rel = rel_old; |
| rel.rowSize = MAXALIGN(round(x1)); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // joinsel // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function joinsel = ... |
| joinsel_init( selectivity, nquals_hash, nquals_postjoin ) |
| joinsel = struct(); |
| joinsel.selectivity = selectivity; |
| joinsel.nquals_hash = nquals_hash; |
| joinsel.nquals_postjoin = nquals_postjoin; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // joinsize // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function joinsize = ... |
| joinsize_init( rowsT, rowsU, joinRows ) |
| joinsize = struct(); |
| joinsize.rowsT = rowsT; |
| joinsize.rowsU = rowsU; |
| joinsize.xprodRows = rowsT * rowsU; |
| joinsize.joinRows = min( joinRows, joinsize.xprodRows ); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // hashbuild // |
| //--------------------------------------------------------------------// |
| |
| make hashbuildT = hashbuild_make( relT, work_mem, NTUP_PER_BUCKET, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ) |
| make hashbuildU = hashbuild_make( relU, work_mem, NTUP_PER_BUCKET, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ) |
| function |
| {@ |
| function hashbuild = ... |
| hashbuild_make( rel, work_mem, NTUP_PER_BUCKET, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ) |
| hashbuild = struct(); |
| ( hashbuild.nbuckets, hashbuild.nbatch, hashbuild.bytes ) = ... |
| ExecChooseHashTableSize( rel.rows, rel.rowSize, ... |
| work_mem, NTUP_PER_BUCKET, ... |
| modelVersion, optionCheckboxes ); |
| ( hashbuild.totalCost, hashbuild.ioCost, hashbuild.cpuCost ) = ... |
| cost_hashjoin( 0, 0, 0, 0, ... |
| rel.rows, rel.rowSize, rel.scanCost, ... |
| hashbuild.nbuckets, hashbuild.nbatch, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // joincostTU/UT // |
| //--------------------------------------------------------------------// |
| |
| make joincostTU = ... |
| joincost_make( relT, relU, hashbuildU, ... |
| joinsel, costgucs, blcksz, modelVersion, optionCheckboxes ) |
| make joincostUT = ... |
| joincost_make( relU, relT, hashbuildT, ... |
| joinsel, costgucs, blcksz, modelVersion, optionCheckboxes ) |
| function |
| {@ |
| function hjCost = ... |
| joincost_make( relOuter, relInner, hashbuildInner, ... |
| joinsel, costgucs, blcksz, modelVersion, optionCheckboxes ) |
| ( hjCost, hjIO, hjCPU ) = ... |
| cost_hashjoin( relOuter.rows, relOuter.rowSize, relOuter.scanCost, 0, ... |
| relInner.rows, relInner.rowSize, relInner.scanCost, ... |
| hashbuildInner.nbuckets, hashbuildInner.nbatch, ... |
| joinsel, costgucs, blcksz, modelVersion, optionCheckboxes ); |
| @} |
| |
| |
| //********************************************************************// |
| // // |
| // User Interface // |
| // // |
| //********************************************************************// |
| |
| //--------------------------------------------------------------------// |
| // Keyboard event handlers // |
| //--------------------------------------------------------------------// |
| keydown "s" scaleflag = scaleflag_keydown( scaleflag ) |
| |
| function |
| {@ |
| function scaleflag = ... |
| scaleflag_keydown( scaleflag ) |
| scaleflag = ~scaleflag; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // VisibleControls // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function visibleControls = ... |
| visibleControls_init() |
| enums = {}; |
| enums{end+1} = enumerator_init( 'Version' , vc_vers_id ); |
| enums{end+1} = enumerator_init( 'Size Adj' , vc_sizeadj_id ); |
| enums{end+1} = enumerator_init( 'GUCs' , vc_gucs_id ); |
| enums{end+1} = enumerator_init( 'Quals' , vc_quals_id ); |
| enums{end+1} = enumerator_init( 'Selectivity' , vc_joinsel_id ); |
| enums{end+1} = enumerator_init( 'Subplans' , vc_subplans_id ); |
| enums{end+1} = enumerator_init( 'Explain' , vc_explain_id ); |
| visibleControlsChooser = enumChooser_init( enums, ... |
| 'Show controls', 4, ... |
| visibleControls_id1 : visibleControls_idN ); |
| initiallyVisible = [ vc_vers_id ]; |
| visibleControls = checkboxes_init( initiallyVisible, visibleControlsChooser ); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // "Control Panel" // |
| //--------------------------------------------------------------------// |
| |
| figure "Control Panel" |
| mouseover |
| _msg = params_mouseover() |
| |
| mouseup visibleControls_id1 |
| visibleControls = ... |
| checkboxes_mouseup( visibleControls, _id, _x1, _x0 ) |
| mouseup visibleControls_id2 |
| visibleControls = ... |
| checkboxes_mouseup( visibleControls, _id, _x1, _x0 ) |
| mouseup visibleControls_id3 |
| visibleControls = ... |
| checkboxes_mouseup( visibleControls, _id, _x1, _x0 ) |
| mouseup modelVersion_id1 |
| ( modelVersion, blcksz, NTUP_PER_BUCKET ) = ... |
| vers_mouseup( modelVersionChooser, blcksz, _id, _x1 ) |
| mouseup modelVersion_id2 |
| ( modelVersion, blcksz, NTUP_PER_BUCKET ) = ... |
| vers_mouseup( modelVersionChooser, blcksz, _id, _x1 ) |
| mouseup modelVersion_id3 |
| ( modelVersion, blcksz, NTUP_PER_BUCKET ) = ... |
| vers_mouseup( modelVersionChooser, blcksz, _id, _x1 ) |
| mouseup optionCheckboxes_id1 |
| optionCheckboxes = ... |
| checkboxes_mouseup( optionCheckboxes, _id, _x1, _x0 ) |
| mouseup optionCheckboxes_id2 |
| optionCheckboxes = ... |
| checkboxes_mouseup( optionCheckboxes, _id, _x1, _x0 ) |
| mouseup optionCheckboxes_id3 |
| optionCheckboxes = ... |
| checkboxes_mouseup( optionCheckboxes, _id, _x1, _x0 ) |
| mousedrag blcksz_id |
| blcksz = blcksz_drag( blcksz, _nb, _x1 ) |
| mousedrag explain_rows_id |
| ( relT, relU, joinsel, joinsize ) = ... |
| explain_rows_drag( relT, relU, joinsel, joinsize, _nb, _x1 ) |
| mousedrag explain_rowSize_id |
| ( relT, relU ) = ... |
| explain_rowSize_drag( relT, relU, _nb, _x1 ) |
| mousedrag explain_scanCost_id |
| ( relT, relU ) = ... |
| explain_scanCost_drag( relT, relU, _nb, _x1 ) |
| mousedrag gucs_id |
| costgucs = ... |
| gucs_drag( costgucs, _nb, _x1 ) |
| mousedrag joinrows_id |
| ( joinsel, joinsize ) = ... |
| joinrows_drag( joinsel, joinsize, _nb, _x1 ) |
| mousedrag quals_id |
| joinsel = ... |
| quals_drag( joinsel, _nb, _x1 ) |
| mousedrag ntup_per_bucket_id |
| NTUP_PER_BUCKET = intSlider_drag(_nb, _x1) |
| mousedrag relT_rows_id |
| relT = ... |
| rel_rows_drag( relT, _nb, _x1 ) |
| mousedrag relU_rows_id |
| relU = ... |
| rel_rows_drag( relU, _nb, _x1 ) |
| mousedrag relT_rowSize_id |
| relT = ... |
| rel_rowSize_drag( relT, _nb, _x1 ) |
| mousedrag relU_rowSize_id |
| relU = ... |
| rel_rowSize_drag( relU, _nb, _x1 ) |
| mousedrag work_mem_id |
| work_mem = work_mem_drag(_nb, _x1) |
| draw |
| params_draw( relT, relU, hashbuildT, hashbuildU, joinsel, joinsize, ... |
| costgucs, work_mem, blcksz, NTUP_PER_BUCKET, ... |
| modelVersion, modelVersionChooser, optionCheckboxes, ... |
| joincostTU, joincostUT, ... |
| visibleControls ) |
| |
| function |
| {@ |
| function ... |
| params_draw( relT, relU, hashbuildT, hashbuildU, joinsel, joinsize, ... |
| costgucs, work_mem, blcksz, NTUP_PER_BUCKET, ... |
| modelVersion, modelVersionChooser, optionCheckboxes, ... |
| joincostTU, joincostUT, ... |
| visibleControls ) |
| title( sprintf( '%s Cost: T x U = %.0f U x T = %.0f', ... |
| modelVersion.enum_name, joincostTU, joincostUT ) ); |
| firsttab = 'hj_chainwalk_weight (0.0000)____'; // make room for widest label |
| checkboxes_draw( visibleControls, firsttab ); |
| |
| for vc_enum_id = checkboxes_chosen( visibleControls ) |
| switch vc_enum_id |
| case vc_vers_id |
| vers_draw( blcksz, modelVersion, modelVersionChooser, optionCheckboxes, firsttab ); |
| case vc_explain_id |
| explain_draw( firsttab, relT, relU, joinsize, work_mem ); |
| case vc_gucs_id |
| gucs_draw( costgucs, work_mem, NTUP_PER_BUCKET ); |
| case vc_joinsel_id |
| joinsize_draw( joinsize ); |
| case vc_quals_id |
| quals_draw( joinsel ); |
| case vc_sizeadj_id |
| sizeadj_draw( relT, relU, hashbuildT, hashbuildU, joinsel ); |
| case vc_subplans_id |
| subplans_draw( relT, relU ); |
| end |
| end |
| |
| function msg = ... |
| params_mouseover() |
| msg = 'Control panel area can be dragged up/down or scrolled with mousewheel.'; |
| |
| function blcksz = ... |
| blcksz_drag( blcksz, nb, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| blcksz = blcksz_set( blcksz, 2^round(x1+12) ); |
| |
| function ... |
| explain_draw( firsttab, relT, relU, joinsize, work_mem ) |
| text( '-------------------- EXPLAIN plan' ); |
| settabs( [ firsttab, '\t000000000000000\t \t000000000000000' ] ); |
| textfield( 'Joined rows out', '%.0f', joinsize.joinRows, '', joinrows_id ); |
| textfield( 'Rows to join (T, U)\t', '%.0f%.0f', [ joinsize.rowsT, joinsize.rowsU ], '', explain_rows_id ); |
| textfield( 'Subplan cost (T, U)\t', '%.0f%.0f%', [ relT.initialScanCost, relU.initialScanCost ], '', explain_scanCost_id ); |
| textfield( 'Row size (T, U)\t', '%.0f%.0f', [ relT.initialRowSize, relU.initialRowSize ], '', explain_rowSize_id ); |
| textfield( 'work_mem (KB)', '%.0f', work_mem, '', work_mem_id ); |
| text( ' Please ignore extra trailing digits or junk in the fields above (Sysquake bug).' ); |
| settabs( firsttab ); |
| |
| function ( relT, relU, joinsel, joinsize ) = ... |
| explain_rows_drag( relT, relU, joinsel, joinsize, nb, x1 ) |
| if isempty( nb ) || x1 <= 0 |
| cancel; |
| end |
| switch nb |
| case 1 |
| ( relT, joinsel, joinsize ) = rel_initialRows_drag( relT, 1, joinsel, joinsize, nb, x1 ); |
| case 2 |
| ( relU, joinsel, joinsize ) = rel_initialRows_drag( relU, 2, joinsel, joinsize, nb, x1 ); |
| end |
| |
| function ( relT, relU ) = ... |
| explain_rowSize_drag( relT, relU, nb, x1 ) |
| if isempty( nb ) || x1 <= 0 |
| cancel; |
| end |
| switch nb |
| case 1 |
| relT = rel_initialRowSize_drag( relT, nb, x1 ); |
| case 2 |
| relU = rel_initialRowSize_drag( relU, nb, x1 ); |
| end |
| |
| function ( relT, relU ) = ... |
| explain_scanCost_drag( relT, relU, nb, x1 ) |
| if isempty( nb ) || x1 <= 0 |
| cancel; |
| end |
| switch nb |
| case 1 |
| relT = rel_initialScanCost_drag( relT, nb, x1 ); |
| case 2 |
| relU = rel_initialScanCost_drag( relU, nb, x1 ); |
| end |
| |
| function ... |
| gucs_draw( costgucs, work_mem, NTUP_PER_BUCKET ) |
| text('-------------------- GUCs'); |
| labels = sprintf( 'work_mem (%d KB)', work_mem ); |
| slider( labels, work_mem, [32,4*1024*1024], 'L', '', work_mem_id ); |
| labels = sprintf( 'cpu_tuple_cost (%f)\ncpu_operator_cost (%f)\nseq_page_cost (%f)\nhj_chainwalk_weight (%f)', ... |
| costgucs.cpu_tuple_cost, costgucs.cpu_operator_cost, ... |
| costgucs.seq_page_cost, costgucs.hj_chainwalk_weight ); |
| slider( labels, ... |
| [costgucs.cpu_tuple_cost; costgucs.cpu_operator_cost; costgucs.seq_page_cost; costgucs.hj_chainwalk_weight], ... |
| [0,0.1; 0,0.1; 0,10; 1e-4,100], '---L', '', gucs_id ); |
| labels = sprintf('ntup_per_bucket (%d)', NTUP_PER_BUCKET); |
| slider(labels, NTUP_PER_BUCKET, [1,20], '', '', ntup_per_bucket_id); |
| |
| function costgucs = ... |
| gucs_drag( costgucs_old, nb, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| costgucs = costgucs_old; |
| switch nb |
| case 1 |
| costgucs.cpu_tuple_cost = x1; |
| case 2 |
| costgucs.cpu_operator_cost = x1; |
| case 3 |
| costgucs.seq_page_cost = x1; |
| case 4 |
| costgucs.hj_chainwalk_weight = x1; |
| end |
| |
| function ... |
| joinsize_draw( joinsize ) |
| invsel = joinsize.xprodRows / joinsize.joinRows; |
| text( sprintf( '-------------------- Join selectivity = 1 / %.0f', invsel ) ); |
| labels = sprintf( 'T rows to join (%d)\nU rows to join (%d)', joinsize.rowsT, joinsize.rowsU ); |
| slider( labels, [ joinsize.rowsT; joinsize.rowsU ], [ 1,1e9 ], 'L', '', explain_rows_id ); |
| labels = sprintf( 'Joined rows out (%.0f)', joinsize.joinRows ); |
| slider( labels, joinsize.joinRows, [ 1,joinsize.xprodRows ], 'L', '', joinrows_id ); |
| |
| function ( joinsel, joinsize ) = ... |
| joinrows_drag( joinsel_old, joinsize_old, nb, x1 ) |
| if isempty(nb) || x1 < 0 |
| cancel; |
| end |
| joinsel = joinsel_old; |
| joinsize = joinsize_old; |
| joinsize.joinRows = min( ceil( x1 ), joinsize.xprodRows ); |
| joinsel.selectivity = joinsize.joinRows / joinsize.xprodRows; |
| |
| function ... |
| quals_draw( joinsel ) |
| text( '-------------------- Join quals' ); |
| labels = sprintf( 'Hash Cond (%d)\nJoin Filter (%d)', ... |
| joinsel.nquals_hash, joinsel.nquals_postjoin ); |
| slider( labels, [ joinsel.nquals_hash; joinsel.nquals_postjoin ], [ 0,10 ], '', '', quals_id ); |
| |
| function joinsel = ... |
| quals_drag( joinsel_old, nb, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| joinsel = joinsel_old; |
| switch nb |
| case 1 |
| joinsel.nquals_hash = round( x1 ); |
| case 2 |
| joinsel.nquals_postjoin = round( x1 ); |
| end |
| |
| function ... |
| sizeadj_draw( relT, relU, hashbuildT, hashbuildU, joinsel ) |
| bytesT = relT.rows * relT.rowSize; |
| bytesU = relU.rows * relU.rowSize; |
| joinResultRows = relT.rows * relU.rows * joinsel.selectivity; |
| text( sprintf( '-------------------- Size Adj\t%s; Out = %s', ... |
| fmtsizepair( 'T = %.0f %sB ', hashbuildT.bytes, ... |
| ' U = %.0f %sB', hashbuildU.bytes ), ... |
| fmtsize( '%.0f %s rows', joinResultRows ) ) ); |
| labels = sprintf( 'Rows from T (%d)', relT.rows ); |
| slider( labels, relT.rows, [ 1,1e9 ], 'L', relT.rowLineStyle, relT_rows_id ); |
| labels = sprintf( 'Rows from U (%d)', relU.rows ); |
| slider( labels, relU.rows, [ 1,1e9 ], 'L', relU.rowLineStyle, relU_rows_id ); |
| labels = sprintf( 'Width T (%d bytes/row)', relT.rowSize ); |
| slider( labels, relT.rowSize, [ 1,1000 ], '', '', relT_rowSize_id ); |
| labels = sprintf( 'Width U (%d bytes/row)', relU.rowSize ); |
| slider( labels, relU.rowSize, [ 1,1000 ], '', '', relU_rowSize_id ); |
| |
| function ... |
| subplans_draw( relT, relU ) |
| text( sprintf( '-------------------- Subplan cost per row ... %s = 1/%g %s = 1/%g', ... |
| relT.name, 1 / relT.scanCostPerRow, relU.name, 1 / relU.scanCostPerRow ) ); |
| labels = sprintf( '%s total cost (%d)\n%s total cost (%d)', ... |
| relT.name, relT.initialScanCost, relU.name, relU.initialScanCost ); |
| slider( labels, [ relT.initialScanCost; relU.initialScanCost ], [ 1,1e9 ], 'L', '', explain_scanCost_id ); |
| labels = sprintf( '%s rows to join (%d)\n%s rows to join (%d)', ... |
| relT.name, relT.initialRows, relU.name, relU.initialRows ); |
| slider( labels, [ relT.initialRows; relU.initialRows ], [ 1,1e9 ], 'L', '', explain_rows_id ); |
| |
| function ... |
| vers_draw( blcksz, modelVersion, modelVersionChooser, optionCheckboxes, firsttab ) |
| text( '-------------------- Version' ); |
| labels = sprintf( 'Block size (%d KB)', blcksz(1)/1024 ); |
| slider( labels, log2( blcksz(1)/4096 ), [0,6], '', '', blcksz_id ); |
| |
| settabs([firsttab, '\t\b', modelVersionChooser.longestName, '_']); |
| radiobuttons_draw( modelVersionChooser, modelVersion.enum_id ); |
| checkboxes_draw( optionCheckboxes, firsttab ); |
| settabs( firsttab ); |
| |
| function ( modelVersion, blcksz, NTUP_PER_BUCKET ) = ... |
| vers_mouseup( modelVersionChooser, blcksz, id, x1 ) |
| ( modelVersion, blcksz, NTUP_PER_BUCKET ) = ... |
| modelVersion_set( radiobuttons_mouseup( modelVersionChooser, id, x1 ), blcksz ); |
| |
| function work_mem = ... |
| work_mem_drag( nb, x1 ) |
| if isempty(nb) || x1 < 32 |
| cancel; |
| end |
| work_mem = x1; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // "(T hj U) vs (U hj T)" plots // |
| //--------------------------------------------------------------------// |
| |
| figure "HashVsHash" |
| mouseover |
| ( _msg, _cursor ) = xy_mouseover( _id, _x0, _y0 ) |
| mouseover joincostTU_id |
| _msg = joincost_mouseover( relT, relU, hashbuildT, hashbuildU, 1, _x0, _y0 ) |
| mouseover joincostUT_id |
| _msg = joincost_mouseover( relT, relU, hashbuildT, hashbuildU, 0, _x0, _y0 ) |
| |
| draw hashVsHash_draw( relT, relU, hashbuildU, ... |
| joinsel, costgucs, scaleflag, ... |
| work_mem, NTUP_PER_BUCKET, blcksz, ... |
| modelVersion, optionCheckboxes, ... |
| joincostTU, joincostUT, ... |
| 1 ) |
| |
| figure "HashVsHash (Ratio)" |
| mouseover |
| ( _msg, _cursor ) = xy_mouseover( _id, _x0, _y0 ) |
| |
| draw hashVsHash_draw( relT, relU, hashbuildU, ... |
| joinsel, costgucs, scaleflag, ... |
| work_mem, NTUP_PER_BUCKET, blcksz, ... |
| modelVersion, optionCheckboxes, ... |
| joincostTU, joincostUT, ... |
| 2 ) |
| |
| figure "HashVsHash (Difference)" |
| mouseover |
| ( _msg, _cursor ) = xy_mouseover( _id, _x0, _y0 ) |
| |
| draw hashVsHash_draw( relT, relU, hashbuildU, ... |
| joinsel, costgucs, scaleflag, ... |
| work_mem, NTUP_PER_BUCKET, blcksz, ... |
| modelVersion, optionCheckboxes, ... |
| joincostTU, joincostUT, ... |
| 3 ) |
| |
| function |
| {@ |
| function ... |
| hashVsHash_draw( relT, relU, hashbuildU, ... |
| joinsel, costgucs, scaleflag, ... |
| work_mem, NTUP_PER_BUCKET, blcksz, ... |
| modelVersion, optionCheckboxes, ... |
| joincostTU, joincostUT, ... |
| whichPlot ) |
| sc = scale; |
| if isempty(sc) |
| sc = [1,1e9]; |
| end |
| relT_rowSamples = 10.^[log10(max(1,sc(1))):.1:log10(max(1,sc(2)))]; |
| for relT_rows = relT_rowSamples |
| tuCost(end+1) = ... |
| cost_hashjoin( relT_rows, relT.rowSize, relT_rows * relT.scanCostPerRow, 0, ... |
| relU.rows, relU.rowSize, relU.scanCost, ... |
| hashbuildU.nbuckets, hashbuildU.nbatch, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ); |
| ( hashT_nbuckets, hashT_nbatch, hashT_bytes(end+1) ) = ... |
| ExecChooseHashTableSize( relT_rows, relT.rowSize, ... |
| work_mem, NTUP_PER_BUCKET, ... |
| modelVersion, optionCheckboxes ); |
| utCost(end+1) = ... |
| cost_hashjoin( relU.rows, relU.rowSize, relU.scanCost, 0, ... |
| relT_rows, relT.rowSize, relT_rows * relT.scanCostPerRow, ... |
| hashT_nbuckets, hashT_nbatch, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ); |
| end |
| switch whichPlot |
| case 1 |
| whoami = ''; |
| hashVsHash_scale( sc, scaleflag, 'loglog', [1,1e12], [ 1, tuCost, utCost ] ); |
| plot( relT_rowSamples, tuCost, 'r', joincostTU_id ); |
| plot( relT_rowSamples, utCost, 'b', joincostUT_id ); |
| legnd = 'Total cost (T x U)\nTotal cost (U x T)\nT bigger & inner\nU bigger & inner'; |
| style = 'rbh(a0a0ff)xh(ffa0a0)+'; |
| |
| // plot the cost at subplan T's specified number of rows |
| plot( relT.rows, joincostTU, 'ro', joincostTU_id ); |
| plot( relT.rows, joincostUT, 'b[', joincostUT_id ); |
| |
| // was larger rel chosen as inner? (counting hash table tuple overhead) |
| sizediff = hashT_bytes - hashbuildU.bytes; |
| costdiff = tuCost - utCost; |
| biginnerT = (sizediff >= 1000) & (costdiff >= 10); |
| biginnerU = (sizediff <= -1000) & (costdiff <= -10); |
| if nnz(biginnerT) |
| plot( relT_rowSamples(biginnerT), utCost(biginnerT), 'h(a0a0ff)x' ); |
| end |
| if nnz(biginnerU) |
| plot( relT_rowSamples(biginnerU), tuCost(biginnerU), 'h(ffa0a0)+' ); |
| end |
| |
| case 2 |
| whoami = '(Ratio)'; |
| costratio = tuCost ./ utCost; |
| sizeratio = hashT_bytes / hashbuildU.bytes; |
| hashVsHash_scale( sc, scaleflag, 'loglog', [0.5,2.0], [ costratio ] ); |
| line( [0,1], 1.0, 'h(c0c0c0)' ); |
| plot( relT_rowSamples, costratio, 'g', xytrace_id ); |
| plot( relT_rowSamples, sizeratio, 'm', xytrace_id ); |
| |
| // plot the cost ratio at subplan T's specified number of rows |
| plot( relT.rows, joincostTU / joincostUT, 'go', xytrace_id ); |
| |
| legnd = 'Cost(T x U) / Cost(U x T)\nT bytes / U bytes'; |
| style = 'gm'; |
| |
| case 3 |
| whoami = '(Difference)'; |
| diff = tuCost - utCost; |
| hashVsHash_scale( sc, scaleflag, 'loglin', [-1000,1000], diff ); |
| line( [0,1], 0.0, 'h(c0c0c0)' ); |
| plot( relT_rowSamples, diff, 'm', xytrace_id ); |
| |
| // plot the difference at subplan T's specified number of rows |
| plot( relT.rows, joincostTU - joincostUT, 'm[', xytrace_id ); |
| |
| legnd = 'Cost(T x U) - Cost(U x T)'; |
| style = 'm'; |
| end |
| label( [' ', relT.name, ' Rows'] ); |
| legend( legnd, style ); |
| plotoption('tllegend'); |
| title( sprintf( 'Hash Join Cost %s ... U = %.0f rows', ... |
| whoami, relU.rows ) ); |
| |
| function ... |
| hashVsHash_scale( sc, scaleflag, option, default, vec ) |
| scale( option ); |
| if scaleflag |
| sc(3) = min( vec ); |
| sc(4) = max( vec ); |
| else |
| sc(3:4) = default; |
| end |
| scale('lock', sc); |
| |
| function msg = ... |
| joincost_mouseover( relT, relU, hashbuildT, hashbuildU, isTxU, x0, y0 ) |
| xxx = isTxU ? 'T x U' : 'U x T'; |
| hashRowSizeT = hashbuildT.bytes / relT.rows; |
| msg = sprintf( 'Cost(%s) = %.0f %s T = %.0f rows U = %.0f rows', ... |
| xxx, y0, ... |
| fmtsizepair( 'T = %.0f %sB ', x0 * hashRowSizeT, ... |
| ' U = %.0f %sB', hashbuildU.bytes ), ... |
| x0, relU.rows ); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // "Hash Join (x outer, y inner)" plots // |
| //--------------------------------------------------------------------// |
| |
| figure "T HJ U" |
| mouseover |
| ( _msg, _cursor ) = xy_mouseover( _id, _x0, _y0 ) |
| mouseover joincost_id |
| _msg = joincost_mouseover( relT, relU, hashbuildT, hashbuildU, 1, _x0, _y0 ) |
| |
| draw joinCost_draw( relT, relU, hashbuildU, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes, ... |
| joincostTU ) |
| |
| figure "U HJ T" |
| mouseover |
| ( _msg, _cursor ) = xy_mouseover( _id, _x0, _y0 ) |
| mouseover joincost_id |
| _msg = joincost_mouseover( relT, relU, hashbuildT, hashbuildU, 0, _x0, _y0 ) |
| |
| draw joinCost_draw( relU, relT, hashbuildT, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes, ... |
| joincostUT ) |
| |
| function |
| {@ |
| function ... |
| joinCost_draw( relOuter, relInner, hashbuildInner, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes, ... |
| joincostOuterInner ) |
| scale loglog; |
| scale('lock', [1,1e9,1,1e12]); |
| sc = scale; |
| outerRowSamples = 10.^[log10(max(1,sc(1))):.1:log10(max(1,sc(2)))]; |
| for outerRows = outerRowSamples |
| (hjCost(end+1), hjIO(end+1), hjCPU(end+1)) = ... |
| cost_hashjoin( outerRows, relOuter.rowSize, outerRows * relOuter.scanCostPerRow, 0, ... |
| relInner.rows, relInner.rowSize, relInner.scanCost, ... |
| hashbuildInner.nbuckets, hashbuildInner.nbatch, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ); |
| end |
| title( sprintf( 'Hash Join (%s outer, %s inner) ... %s = %.0f rows', ... |
| relOuter.name, relInner.name, relInner.name, relInner.rows ) ); |
| line( [0,1], hashbuildInner.totalCost, 'm', xytrace_id ); |
| plot( outerRowSamples, hjIO, 'g', xytrace_id ); |
| plot( outerRowSamples, hjCPU, 'r', xytrace_id ); |
| plot( outerRowSamples, hjCost, 'b', joincost_id ); |
| |
| // join cost at exact number of rows specified |
| plot( relOuter.rows, joincostOuterInner, [ relOuter.rowLineStyle, '[' ], joincost_id ); |
| |
| label( [relOuter.name, ' Rows (Outer)'] ); |
| legend( sprintf( 'Total cost (%s x %s)\nI/O cost\nCPU cost\n%s build cost\n%s rows', ... |
| relOuter.name, relInner.name, relInner.name, relOuter.name ), ... |
| ['bgrm', relOuter.rowLineStyle] ); |
| plotoption('tllegend'); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // "Hash Build" plots // |
| //--------------------------------------------------------------------// |
| |
| figure "Hash Build (T inner)" |
| mouseover |
| ( _msg, _cursor ) = xy_mouseover( _id, _x0, _y0 ) |
| mousedrag inner_rows_id |
| ( _msg, relT ) = rel_rows_dragmsg( relT, _nb, _x1 ) |
| mouseover inner_rows_id |
| ( _msg, _cursor ) = rel_rows_mouseover( relT, _x0 ) |
| |
| draw hashTable_draw( relT, joinsel, costgucs, ... |
| work_mem, blcksz, NTUP_PER_BUCKET, ... |
| modelVersion, optionCheckboxes ) |
| |
| figure "Hash Build (U inner)" |
| mouseover |
| ( _msg, _cursor ) = xy_mouseover( _id, _x0, _y0 ) |
| mousedrag inner_rows_id |
| ( _msg, relU ) = rel_rows_dragmsg( relU, _nb, _x1 ) |
| mouseover inner_rows_id |
| ( _msg, _cursor ) = rel_rows_mouseover( relU, _x0 ) |
| |
| draw hashTable_draw( relU, joinsel, costgucs, ... |
| work_mem, blcksz, NTUP_PER_BUCKET, ... |
| modelVersion, optionCheckboxes ) |
| |
| function |
| {@ |
| function ... |
| hashTable_draw( relInner, joinsel, costgucs, ... |
| work_mem, blcksz, NTUP_PER_BUCKET, ... |
| modelVersion, optionCheckboxes ) |
| scale loglog; |
| scale('lock', [1,1e8,0.9,1e8]); |
| sc = scale; |
| rowSamples = 10.^[log10(max(1,sc(1))):.1:log10(max(1,sc(2)))]; |
| |
| // make sure the plot includes a sample at the exact number of rows specified |
| rowSamples = sort( [ rowSamples, relInner.rows ] ); |
| |
| for rows = rowSamples |
| (nbuckets(end+1), nbatch(end+1)) = ExecChooseHashTableSize( rows, relInner.rowSize, ... |
| work_mem, NTUP_PER_BUCKET, ... |
| modelVersion, optionCheckboxes ); |
| hashpages(end+1) = (nbatch(end) <= 1) ? 0 : page_size(rows, relInner.rowSize, blcksz(1), modelVersion); |
| (hjStartupCost(end+1), hjStartupIO(end+1), hjStartupCPU(end+1)) = ... |
| cost_hashjoin( 0, 0, 0, 0, ... |
| rows, relInner.rowSize, rows * relInner.scanCostPerRow, ... |
| nbuckets(end), nbatch(end), ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ); |
| end |
| plotoption('tllegend'); |
| plot(rowSamples, nbatch, 'h(ff8000)', xytrace_id); |
| plot(rowSamples, nbuckets, 'h(a0e060)', xytrace_id); |
| plot(rowSamples, hashpages, 'g', xytrace_id); |
| plot(rowSamples, hjStartupCost, 'm', xytrace_id); |
| line( [1,0], relInner.rows, relInner.rowLineStyle, inner_rows_id ); |
| |
| // mark the build cost at exact number of rows specified |
| i = find( rowSamples == relInner.rows, 1 ); |
| plot( rowSamples(i), hjStartupCost(i), 'm[', xytrace_id ); |
| |
| label( [relInner.name, ' Rows (Inner)'] ); |
| legnd = { 'Build cost\nSpill pages\nBatches\nBuckets', 'mgh(ff8000)h(a0e060)' }; |
| legend( [legnd{1}, '\n', relInner.name, ' rows'], [legnd{2}, relInner.rowLineStyle] ); |
| @} |
| |
| |
| //********************************************************************// |
| // // |
| // User Interface Utilities // |
| // // |
| //********************************************************************// |
| |
| function |
| {@ |
| function arr = ... |
| list2numFix( list ) |
| // Sysquake 3.6 bug: Can't call list2num on an empty list; it returns garbage |
| arr = isempty( list ) ? [] : list2num( list ); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // common UI functions // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function v = ... |
| floatSlider_drag(nb, x1) |
| if isempty(nb) |
| cancel; |
| end |
| v = x1; |
| |
| function v = ... |
| intSlider_drag(nb, x1) |
| if isempty(nb) |
| cancel; |
| end |
| v = round(x1); |
| |
| function (msg,cursor) = ... |
| xy_mouseover(id, x0, y0) |
| if isempty(id) |
| cancel; |
| end |
| msg = sprintf('x=%g y=%g', x0, y0); |
| cursor = false; |
| |
| function s = ... |
| fmtsize( fmt, i ) |
| j = abs( i ); |
| if j >= 1e13 |
| scl = 1e-12; |
| u = 'T'; |
| elseif j >= 1e10 |
| scl = 1e-9; |
| u = 'G'; |
| elseif j >= 1e7 |
| scl = 1e-6; |
| u = 'M'; |
| elseif j >= 1e4 |
| scl = 1e-3; |
| u = 'K'; |
| else |
| scl = 1; |
| u = ''; |
| end |
| // fmt should be something like '%.0f %s' or '%.3g%s' |
| s = sprintf( fmt, i * scl, u ); |
| |
| function s = ... |
| fmtsizepair( fmta, a, fmtb, b ) |
| diff = a - b; |
| arrow = (diff > 1000) ? '>' : (diff < -1000) ? '<' : ' '; |
| s = [ fmtsize( fmta, a ), arrow, fmtsize( fmtb, b ) ]; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // enumerator // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function enumerator = ... |
| enumerator_init( enum_name, enum_id ) |
| enumerator = struct(); |
| enumerator.enum_name = enum_name; |
| enumerator.enum_id = enum_id; |
| |
| function enum_name = ... |
| enumerator_name( enumerator ) |
| enum_name = enumerator.enum_name; |
| |
| function enum_id = ... |
| enumerator_id( enumerator ) |
| enum_id = enumerator.enum_id; |
| |
| function enum_ids = ... |
| enumerator_ids( enumerators ) |
| enum_ids = list2numFix( map( @enumerator_id, enumerators ) ); |
| |
| function enum_ids = ... |
| enumerator_ids_upd( enum_ids, remove_enum_ids, insert_enum_ids ) |
| whichToDrop = ismember( enum_ids, [ remove_enum_ids, insert_enum_ids ] ); |
| enum_ids = [ insert_enum_ids, enum_ids( ~whichToDrop ) ]; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // enumChooser // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function ix = ... |
| enumChooser_name_to_ix( chooser, enum_name ) |
| ix = find( list2numFix( map( @eq, chooser.enum_names, {enum_name} ) ), 1 ) || 0; |
| |
| function ix = ... |
| enumChooser_id_to_ix( chooser, enum_id ) |
| ix = find(chooser.enum_ids == enum_id, 1) || 0; |
| |
| function enumerator = ... |
| enumChooser_name_to_enum( chooser, enum_name ) |
| ix = enumChooser_name_to_ix( chooser, enum_name ); |
| enumerator = ix ? chooser.enumerators{ix} : []; |
| |
| function enumerator = ... |
| enumChooser_id_to_enum( chooser, enum_id ) |
| ix = enumChooser_id_to_ix( chooser, enum_id ); |
| enumerator = ix ? chooser.enumerators{ix} : struct(); |
| |
| function enumerators = ... |
| enumChooser_ids_to_enums( chooser, enum_ids ) |
| enumerators = isempty( enum_ids ) ? {} : map( @enumChooser_id_to_enum, {chooser}, num2list( enum_ids ) ); |
| |
| function chooser = ... |
| enumChooser_init( enumerators, label, buttons_per_row, control_ids ) |
| enum_names = map( @enumerator_name, enumerators ); |
| enum_ids = enumerator_ids( enumerators ); |
| |
| n = length(enumerators); |
| assert(n <= buttons_per_row * length(control_ids)); |
| |
| bargs = {}; |
| longestName = ''; |
| for i = 1 : buttons_per_row : n |
| m = min(i + buttons_per_row - 1, n); |
| iba = length(bargs) + 1; |
| barg = struct(); |
| barg.label = (i == 1) ? label : ''; |
| for ix = i : m |
| enum_name = enum_names{ix}; |
| barg.label = [barg.label, '\t', enum_name]; |
| if length(longestName) < length(enum_name) |
| longestName = enum_name; |
| end |
| end |
| barg.control_id = control_ids(iba); |
| barg.ix0 = i - 1; |
| barg.ixM = m; |
| bargs{iba} = barg; |
| end |
| |
| chooser = struct(); |
| chooser.enumerators = enumerators; |
| chooser.enum_names = enum_names; |
| chooser.enum_ids = enum_ids; |
| chooser.buttonargs = bargs; |
| chooser.control_ids = control_ids(1:length(bargs)); |
| chooser.longestName = longestName; |
| |
| function buttonarg = ... |
| enumChooser_cid_to_barg( chooser, control_id ) |
| iba = find( chooser.control_ids == control_id, 1 ); |
| buttonarg = chooser.buttonargs{iba}; |
| |
| function ... |
| radiobuttons_draw( enumChooser, chosen_enum_id ) |
| ix = enumChooser_id_to_ix( enumChooser, chosen_enum_id ); |
| for buttonarg = enumChooser.buttonargs |
| button(buttonarg.label, ix - buttonarg.ix0, 'radiobutton', '', buttonarg.control_id); |
| end |
| |
| function ( chosen_enumerator, chosen_enum_id ) = ... |
| radiobuttons_mouseup( enumChooser, id, x1 ) |
| buttonarg = enumChooser_cid_to_barg(enumChooser, id); |
| chosen_enumerator = enumChooser.enumerators{x1 + buttonarg.ix0}; |
| chosen_enum_id = chosen_enumerator.enum_id; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // checkboxes // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function checkboxes = ... |
| checkboxes_init( chosen_enum_ids, enumChooser ) |
| checkboxes = struct(); |
| checkboxes.chosen_enum_ids = chosen_enum_ids; |
| checkboxes.chooser = enumChooser; |
| checkboxes.checkmarks = checkboxes_checkmarks( chosen_enum_ids, enumChooser ); |
| |
| function chosen_enum_ids = ... |
| checkboxes_chosen( checkboxes ) |
| chosen_enum_ids = checkboxes.chosen_enum_ids; |
| |
| function ismem = ... |
| checkboxes_test( checkboxes, enum_ids ) |
| ismem = ismember( enum_ids, checkboxes.chosen_enum_ids ); |
| |
| function checkboxes = ... |
| checkboxes_upd( checkboxes, remove_enum_ids, insert_enum_ids ) |
| checkboxes.chosen_enum_ids = enumerator_ids_upd( checkboxes.chosen_enum_ids, remove_enum_ids, insert_enum_ids ); |
| checkboxes.checkmarks = checkboxes_checkmarks( checkboxes.chosen_enum_ids, checkboxes.chooser ); |
| |
| function ... |
| checkboxes_draw( checkboxes, firsttab ) |
| settabs( [ firsttab, '\t\b', checkboxes.chooser.longestName, '__' ] ); |
| checkmarks = checkboxes.checkmarks; |
| bargs = checkboxes.chooser.buttonargs; |
| for iba = 1 : length( checkmarks ) |
| barg = bargs{iba}; |
| button( barg.label, checkmarks( iba ), 'checkmark', '', barg.control_id ); |
| end |
| settabs( firsttab ); |
| |
| function checkboxes = ... |
| checkboxes_mouseup( checkboxes, id, x1, x0 ) |
| // NB: For checkboxes we use mouseup instead of mousedrag, avoiding |
| // a Sysquake 3.6 bug in which the mousedrag handler's input and |
| // output variables of list or struct type become corrupted when |
| // the user drags the mouse (not just clicks) within a checkbox. |
| chooser = checkboxes.chooser; |
| insert_enum_ids = []; |
| remove_enum_ids = []; |
| buttonarg = enumChooser_cid_to_barg( chooser, id ); |
| for iChanged = find(bitget(bitxor(x1, x0), [1:buttonarg.ixM])); |
| ix = iChanged + buttonarg.ix0; |
| id = chooser.enum_ids( ix ); |
| if bitget(x1, iChanged) |
| insert_enum_ids(end+1) = id; |
| else |
| remove_enum_ids(end+1) = id; |
| end |
| end |
| checkboxes = checkboxes_upd( checkboxes, remove_enum_ids, insert_enum_ids ); |
| |
| function checkmarks = ... |
| checkboxes_checkmarks( chosen_enum_ids, enumChooser ) |
| checkmarks = []; |
| for buttonarg = enumChooser.buttonargs |
| cm = 0; |
| for i = find(ismember(enumChooser.enum_ids(buttonarg.ix0+1 : buttonarg.ixM), chosen_enum_ids)) |
| cm = bitset(cm, i); |
| end |
| checkmarks(end+1) = cm; |
| end |
| @} |
| |
| |
| //********************************************************************// |
| // // |
| // Cost model // |
| // // |
| //********************************************************************// |
| |
| //--------------------------------------------------------------------// |
| // common cost model functions // |
| //--------------------------------------------------------------------// |
| // Last updated 24 March 2007. |
| |
| function |
| {@ |
| function per_tuple = ... |
| cost_qual_eval( nquals, cpu_operator_cost ) |
| // costsize.c |
| per_tuple = nquals * cpu_operator_cost; |
| |
| function nbytes_padded = ... |
| MAXALIGN( nbytes ) |
| // c.h |
| nbytes_padded = bitand( nbytes+MAXIMUM_ALIGNOF-1, -MAXIMUM_ALIGNOF ); |
| |
| function nbytes = ... |
| relation_byte_size( tuples, width, modelVersion ) |
| // costsize.c |
| // NB: width and sizeof_HeapTupleHeaderData have already been MAXALIGNed |
| nbytes = tuples * (width + modelVersion.sizeof_HeapTupleHeaderData); |
| |
| function npages = ... |
| page_size( tuples, width, blcksz, modelVersion ) |
| // costsize.c |
| npages = ceil( relation_byte_size( tuples, width, modelVersion ) / blcksz(1) ); |
| @} |
| |
| |
| //--------------------------------------------------------------------// |
| // Hash Join cost // |
| //--------------------------------------------------------------------// |
| // Last updated 2007-08-28. |
| |
| function |
| {@ |
| function ( nbuckets, nbatch, inner_rel_bytes ) = ... |
| ExecChooseHashTableSize( ntuples, tupwidth, work_mem, NTUP_PER_BUCKET, ... |
| modelVersion, optionCheckboxes ) |
| sizeof_HashJoinTupleData = MAXALIGN(sizeof_ptr + 4); |
| tupsize = sizeof_HashJoinTupleData + ... |
| modelVersion.sizeof_MinimalTupleData + ... |
| tupwidth; |
| inner_rel_bytes = ntuples * tupsize; |
| hash_table_bytes = work_mem * 1024; |
| if inner_rel_bytes > hash_table_bytes |
| nbuckets = min( floor(hash_table_bytes / tupsize / NTUP_PER_BUCKET), INT_MAX ); |
| dbatch = min( ceil(inner_rel_bytes / hash_table_bytes), INT_MAX * 0.5 ); |
| nbatch = 2; |
| while nbatch < dbatch |
| nbatch = nbatch * 2; |
| end |
| else |
| switch modelVersion.enum_id |
| case ver_gp300 : ver_gpmax |
| // nodeHash.c ver 1.11 / MAIN:tkordas:20070313184638 |
| dbuckets_lower = ntuples / NTUP_PER_BUCKET; |
| dbuckets_upper = hash_table_bytes / (tupsize * NTUP_PER_BUCKET); |
| if dbuckets_upper > dbuckets_lower |
| dbuckets = (dbuckets_lower + dbuckets_upper)/2.0; |
| else |
| dbuckets = dbuckets_lower; |
| end |
| nbuckets = ceil(dbuckets); |
| otherwise |
| nbuckets = ceil(ntuples / NTUP_PER_BUCKET); |
| end |
| nbatch = 1; |
| end |
| hprimes = [ 1033, 2063, 4111, 8219, 16417, 32779, 65539, 131111, ... |
| 262151, 524341, 1048589, 2097211, 4194329, 8388619, 16777289, 33554473, ... |
| 67108913, 134217773, 268435463, 536870951, 1073741831 ]; |
| nbuckets = hprimes( find( hprimes >= nbuckets, 1 ) ) || nbuckets; |
| |
| function (total_cost, io_cost, cpu_cost) = ... |
| cost_hashjoin( outer_path_rows, outerRowSize, outer_path_total_cost, outer_path_startup_cost, ... |
| inner_path_rows, innerRowSize, inner_path_total_cost, ... |
| numbuckets, numbatches, ... |
| joinsel, costgucs, blcksz, ... |
| modelVersion, optionCheckboxes ) |
| num_hashclauses = joinsel.nquals_hash; |
| cpu_operator_cost = costgucs.cpu_operator_cost; |
| cpu_tuple_cost = costgucs.cpu_tuple_cost; |
| seq_page_cost = costgucs.seq_page_cost; |
| |
| // cost and selectivity of the hashquals and other restriction clauses |
| hash_qual_cost = cost_qual_eval( joinsel.nquals_hash, cpu_operator_cost ); |
| qp_qual_cost = cost_qual_eval( joinsel.nquals_postjoin, cpu_operator_cost ); |
| hash_selec = joinsel.selectivity; |
| |
| // approx # tuples passing the hash quals |
| hashjointuples = hash_selec * outer_path_rows * inner_path_rows; |
| |
| // cost of source data |
| startup_cost(end+1) = outer_path_startup_cost; |
| run_cost(end+1) = outer_path_total_cost - outer_path_startup_cost; |
| startup_cost(end+1) = inner_path_total_cost; |
| |
| // cost of computing hash function |
| switch modelVersion.enum_id |
| case [ver_pg822 : ver_pgmax, ver_gp300 : ver_gpmax] |
| // pg costsize.c ver 1.169.2.1 / REL8_2_STABLE:tgl:20070108160931 |
| // pg costsize.c ver 1.173 / MAIN:tgl:20070108160922 |
| // gp costsize.c ver 1.29 / MAIN:cmcdevitt:20070316224417 |
| startup_cost(end+1) = (cpu_operator_cost * num_hashclauses + cpu_tuple_cost) * inner_path_rows; |
| otherwise |
| startup_cost(end+1) = cpu_operator_cost * num_hashclauses * inner_path_rows; |
| end |
| run_cost(end+1) = cpu_operator_cost * num_hashclauses * outer_path_rows; |
| |
| // hash table size that executor would use for inner relation |
| virtualbuckets = numbuckets * numbatches; |
| |
| // bucketsize fraction for inner relation |
| // NB: In the code, this is usually determined from column statistics. |
| // Since we don't have that info, assume the best. Consequently our |
| // join cost estimate will usually be less than the real one from |
| // EXPLAIN. |
| innerbucketsize = 1 / virtualbuckets; |
| |
| // I/O cost when hash table spills to disk |
| io_cost = 0; |
| if numbatches > 1 |
| outerpages = page_size(outer_path_rows, outerRowSize, blcksz, modelVersion); |
| innerpages = page_size(inner_path_rows, innerRowSize, blcksz, modelVersion); |
| |
| // experimental: exclude first batch from I/O cost |
| if checkboxes_test( optionCheckboxes, oc_spilltweak ) |
| spillfrac = ( numbatches - 1 ) / numbatches; |
| outer_spill_rows = ceil( outer_path_rows * spillfrac ); |
| inner_spill_rows = ceil( inner_path_rows * spillfrac ); |
| outerpages = page_size(outer_spill_rows, outerRowSize, blcksz, modelVersion); |
| innerpages = page_size(inner_spill_rows, innerRowSize, blcksz, modelVersion); |
| end |
| |
| startup_cost(end+1) = seq_page_cost * innerpages; |
| run_cost(end+1) = seq_page_cost * (innerpages + 2 * outerpages); |
| io_cost = startup_cost(end) + run_cost(end); |
| end |
| |
| // cpu cost for hash lookups |
| joininfactor = 1; |
| chainwalk_cost = 0; |
| switch modelVersion.enum_id |
| case [ver_pgmin : ver_pg822 - 1] |
| // searching within hash chains |
| chainwalk_cost = hash_qual_cost * ... |
| outer_path_rows * ceil(inner_path_rows * innerbucketsize) * ... |
| joininfactor; |
| case ver_pg822 : ver_pgmax |
| // searching within hash chains |
| // pg costsize.c ver 1.169.2.1 / REL8_2_STABLE:tgl:20070108160931 |
| // pg costsize.c ver 1.173 / MAIN:tgl:20070108160922 |
| chainwalk_cost = hash_qual_cost * ... |
| outer_path_rows * ceil(inner_path_rows * innerbucketsize) * ... |
| joininfactor * 0.5; |
| case [ver_gp300 : ver_gp3002 - 1] |
| // evaluating hash join quals |
| // gp costsize.c ver 1.28 / MAIN:kharriman:20070227025348 |
| // gp costsize.c ver 1.15.2.4 / Release-2_3_0-branch:kharriman:20070227023643 |
| run_cost(end+1) = hash_qual_cost * hashjointuples; |
| // searching within hash chains |
| // gp costsize.c ver 1.28 / MAIN:kharriman:20070227025348 |
| // gp costsize.c ver 1.15.2.5 / Release-2_3_0-branch:kharriman:20070227024315 |
| chainwalk_cost = 0.05 * cpu_operator_cost * ... |
| outer_path_rows * inner_path_rows * innerbucketsize * joininfactor; |
| case ver_gp3002 : ver_gpmax |
| // evaluating hash join quals |
| // gp costsize.c ver 1.28 / MAIN:kharriman:20070227025348 |
| // gp costsize.c ver 1.15.2.4 / Release-2_3_0-branch:kharriman:20070227023643 |
| run_cost(end+1) = hash_qual_cost * hashjointuples; |
| // searching within hash chains |
| // gp costsize.c ver 1.29.6.4 / Release-3_0_0-branch:jzhang:20070816011436 |
| gp_cost_hashjoin_chainwalk = false; |
| if gp_cost_hashjoin_chainwalk |
| chainwalk_cost = 0.05 * cpu_operator_cost * ... |
| outer_path_rows * inner_path_rows * innerbucketsize * joininfactor; |
| end |
| end |
| run_cost(end+1) = chainwalk_cost * costgucs.hj_chainwalk_weight; |
| |
| // cpu cost for post-join quals |
| joininfactor = 1; |
| switch modelVersion.enum_id |
| case ver_pgmin : ver_pgmax |
| cpu_per_tuple = cpu_tuple_cost + qp_qual_cost; |
| run_cost(end+1) = cpu_per_tuple * hashjointuples * joininfactor; |
| case ver_gpmin : ver_gpmax |
| // gp costsize.c ver 1.15.2.1 / Release-2_3_0-branch:kharriman:20061105010029 |
| // gp costsize.c ver 1.16 / MAIN:kharriman:20061105005913 |
| resultRows_punt = hashjointuples; // assume all rows pass |
| run_cost(end+1) = qp_qual_cost * hashjointuples; |
| run_cost(end+1) = cpu_tuple_cost * resultRows_punt; |
| end |
| |
| // bias against putting larger relation on inside |
| bias = 1; |
| innerbytes = inner_path_rows * innerRowSize; |
| outerbytes = outer_path_rows * outerRowSize; |
| if innerbytes > outerbytes && outerbytes > 0 |
| switch modelVersion.enum_id |
| case ver_pgmin : ver_pg822 - 1 |
| bias = sqrt(innerbytes/outerbytes); |
| case ver_gpmin : ver_gp300 - 1 |
| // gp costsize.c ver 1.15.2.1 / Release-2_3_0-branch:kharriman:20061105010029 |
| // gp costsize.c ver 1.16 / MAIN:kharriman:20061105005913 |
| bias = 1 + 0.1*log(innerbytes/outerbytes); |
| case [ver_pg822 : ver_pgmax, ver_gp300 : ver_gpmax] |
| // pg costsize.c ver 1.169.2.1 / REL8_2_STABLE:tgl:20070108160931 |
| // pg costsize.c ver 1.173 / MAIN:tgl:20070108160922 |
| // gp costsize.c ver 1.29 / MAIN:cmcdevitt:20070316224417 |
| // deleted the bias |
| end |
| end |
| |
| startup_sum = sum(startup_cost); |
| run_sum = sum(run_cost); |
| |
| /* |
| if outer_path_startup_cost == 1/256 |
| dumpvar( { '<<<', [ outer_path_rows, inner_path_rows ], hashjointuples, ... |
| virtualbuckets, chainwalk_cost, ... |
| 'startup_cost=', startup_sum, dumpvar(startup_cost), ... |
| 'run_cost=', run_sum, dumpvar(run_cost), '>>>' } ); |
| end |
| */ |
| |
| total_cost = startup_sum + bias*run_sum; |
| cpu_cost = startup_sum + run_sum - io_cost; |
| @} |
| |
