| /*------------------------------------------------------------------------- |
| * |
| * HashVsIndex.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.5 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 "HashVsIndex 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: a nested join into an index on the inner base table, vs. a |
| hash join with a sequential scan of the inner base table. |
| |
| Both plans are assumed to use the same outer subplan, which therefore |
| does not enter into this comparison. |
| |
| The intended users are database kernel developers and performance |
| analysts who are familiar with the concepts and source code of these |
| optimizers. |
| |
| Note that the row count and row size entering the hash join may differ |
| from the base table row count and row size. Someday these row counts |
| should be linked by the baserel qual selectivity, but for now they are |
| adjusted independently by separate sliders. |
| |
| The "Parameters" area can be scrolled with a mouse wheel or the |
| checkboxes can be used to bring all the controls into view. |
| |
| 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 hj_innerTotalCost |
| variable innerRows |
| variable innerRowSize |
| variable outerRowSize |
| |
| variable indexPages |
| variable indexCorrelation |
| |
| variable innerBaserel |
| variable innerBaserelRows |
| |
| variable qualsets |
| variable nj_quals |
| variable hj_quals |
| variable hj_innerBaserel_qualset |
| |
| // GUCs |
| variable cpu_tuple_cost |
| variable cpu_operator_cost |
| variable cpu_index_tuple_cost |
| variable effective_cache_size |
| variable random_page_cost |
| variable seq_page_cost |
| variable work_mem |
| |
| // #defines |
| variable BLCKSZ |
| variable NTUP_PER_BUCKET |
| |
| // platform dependent |
| define MAXIMUM_ALIGNOF = 8 |
| define sizeof_ptr = 8 |
| |
| // calculated |
| variable nj_innerTotalCost |
| variable hj_nbuckets |
| variable hj_nbatch |
| |
| // initial values |
| define innerRows_default = 10000 |
| |
| // 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 |
| |
| // visible control group ids |
| variable visibleControls |
| variable visibleControlsChooser |
| variable visibleControlsCheckmarks |
| define vc_vers_id = _auto |
| define vc_data_id = _auto |
| define vc_gucs_id = _auto |
| define vc_const_id = _auto |
| define vc_quals_id = _auto |
| define vc_index_id = _auto |
| define vc_baserel_id = _auto |
| |
| // slider control ids |
| define baserel_id = _auto |
| define baserel_rows_id = _auto |
| define baserel_pages_id = _auto |
| define baserel_fillfactor_id = _auto |
| define blcksz_id = _auto |
| define effective_cache_size_id = _auto |
| define gucs_id = _auto |
| define hj_innerRows_id = _auto |
| define index_id = _auto |
| define innerRows_id = _auto |
| define innerBaserelRows_id = _auto |
| define nj_innerRows_id = _auto |
| define ntup_per_bucket_id = _auto |
| define quals_ix_eq_id = _auto |
| define quals_nx_eq_id = _auto |
| define quals_ix_ne_id = _auto |
| define quals_nx_ne_id = _auto |
| define quals_pj_id = _auto |
| define random_page_cost_id = _auto |
| define seq_page_cost_id = _auto |
| define rows_id = _auto |
| define work_mem_id = _auto |
| |
| // button 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 slipBaserel_id1 = _auto |
| define slipBaserel_id2 = _auto |
| define slipBaserel_idN = slipBaserel_id2 |
| |
| // plot & line control ids |
| define xytrace_id = _auto |
| |
| // cost model version ids |
| variable modelVersion // enumerator struct for the chosen version |
| variable modelVersionChooser // enumChooser struct containing version info |
| define ver_pg815 = _auto |
| define ver_pg820 = _auto |
| define ver_pg822 = _auto |
| define ver_pg830 = _auto |
| define ver_pgmin = ver_pg815 |
| define ver_pgmax = ver_pg830 |
| define ver_gp230 = _auto |
| define ver_gp2302 = _auto |
| define ver_gp240 = _auto |
| define ver_gpmin = ver_gp230 |
| define ver_gpmax = ver_gp240 |
| define ver_default = ver_gpmax |
| |
| // radio buttons controlling recalculation of inner baserel size |
| variable slipBaserel // one of: baserel_rows_id, baserel_pages_id, baserel_fillfactor_id |
| variable slipBaserelChooser |
| |
| // for unused result of multiple assignment |
| variable unused |
| |
| |
| //--------------------------------------------------------------------// |
| // Initialization of globals // |
| //--------------------------------------------------------------------// |
| |
| init ( innerRows, innerRowSize, outerRowSize, ... |
| qualsets, ... |
| indexPages, indexCorrelation, ... |
| innerBaserel, ... |
| cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost, effective_cache_size, ... |
| random_page_cost, seq_page_cost, work_mem, ... |
| BLCKSZ, NTUP_PER_BUCKET, ... |
| modelVersion, modelVersionChooser, ... |
| visibleControls, visibleControlsChooser, ... |
| slipBaserel, slipBaserelChooser, unused ) = init |
| function |
| {@ |
| function ( innerRows, innerRowSize, outerRowSize, ... |
| qualsets, ... |
| indexPages, indexCorrelation, ... |
| innerBaserel, ... |
| cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost, effective_cache_size, ... |
| random_page_cost, seq_page_cost, work_mem, ... |
| BLCKSZ, NTUP_PER_BUCKET, ... |
| modelVersion, modelVersionChooser, ... |
| visibleControls, visibleControlsChooser, ... |
| slipBaserel, slipBaserelChooser, unused ) = ... |
| init |
| subplots('Join Cost\tParameters\nHash Table\tInner Index Scan'); |
| unused = []; |
| |
| ( modelVersion, modelVersionChooser ) = initVersions(); |
| ( visibleControls, visibleControlsChooser ) = initVisibleControls(); |
| ( slipBaserel, slipBaserelChooser ) = initSlipBaserel(); |
| qualsets = qualsets_init(); |
| |
| innerRows = innerRows_default; |
| innerRowSize = MAXALIGN( 100 ); |
| outerRowSize = MAXALIGN( 100 ); |
| |
| indexPages = 100; |
| indexCorrelation = 0; |
| |
| cpu_tuple_cost = 0.01; |
| cpu_operator_cost = 0.0025; |
| cpu_index_tuple_cost = 0.001; |
| effective_cache_size = 500; |
| random_page_cost = 100; |
| seq_page_cost = 1; |
| work_mem = 64; |
| |
| BLCKSZ = 8192; |
| NTUP_PER_BUCKET = 10; |
| |
| innerBaserel = baserel_init( innerRows_default, 100, BLCKSZ, modelVersion ); |
| |
| function ( modelVersion, modelVersionChooser ) = ... |
| initVersions |
| versionPrototype = version_init([], 'versionPrototype', []); |
| versionPrototype.sizeof_HeapTupleHeaderData = MAXALIGN(27); |
| versionPrototype.sizeof_MinimalTupleData = versionPrototype.sizeof_HeapTupleHeaderData; |
| |
| 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 = enumChooser_id_to_enum( modelVersionChooser, ver_default ); |
| |
| 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.0', ver_gp230 ); |
| versions{end+1} = v; |
| |
| v = version_init( v, 'gp 2.3.0.2', ver_gp2302 ); |
| versions{end+1} = v; |
| |
| v = version_init( v, 'gp 2.4.0', ver_gp240 ); |
| v.sizeof_MinimalTupleData = MAXALIGN(12); // MAIN:tgl:20060627213120 |
| v.sizeof_HeapTupleHeaderData = MAXALIGN(23); // MAIN:gsherry:20070301024612 |
| versions{end+1} = v; |
| |
| function ( visibleControls, visibleControlsChooser ) = ... |
| initVisibleControls |
| enums = {}; |
| enums{end+1} = enumerator_init( 'Version' , vc_vers_id ); |
| enums{end+1} = enumerator_init( 'Data' , vc_data_id ); |
| enums{end+1} = enumerator_init( 'GUCs' , vc_gucs_id ); |
| enums{end+1} = enumerator_init( '#define' , vc_const_id ); |
| enums{end+1} = enumerator_init( 'Quals' , vc_quals_id ); |
| enums{end+1} = enumerator_init( 'Index' , vc_index_id ); |
| enums{end+1} = enumerator_init( 'Baserel' , vc_baserel_id ); |
| visibleControls = enums; |
| visibleControlsChooser = enumChooser_init( enums, ... |
| 'Show controls:', 4, ... |
| visibleControls_id1 : visibleControls_idN ); |
| |
| function ( slipBaserel, slipBaserelChooser ) = ... |
| initSlipBaserel |
| enums = {}; |
| enums{end+1} = enumerator_init( 'Rows' , baserel_rows_id ); |
| enums{end+1} = enumerator_init( 'Pages' , baserel_pages_id ); |
| enums{end+1} = enumerator_init( 'Fill factor' , baserel_fillfactor_id ); |
| slipBaserelChooser = enumChooser_init( enums, ... |
| 'Update as inputs change:', 4, ... |
| slipBaserel_id1 : slipBaserel_idN ); |
| slipBaserel = baserel_pages_id; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // baserel // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function baserel = ... |
| baserel_init( rows, width, BLCKSZ, modelVersion ) |
| baserel = struct(); |
| baserel.rows = rows; |
| baserel.rowsize = width; |
| baserel = baserel_set_BLCKSZ_modelVersion( baserel, 0, BLCKSZ, modelVersion ); |
| baserel.pages = ceil( rows / baserel.maxRowsPerPage ); |
| baserel.fillfactor = rows / (baserel.pages * baserel.maxRowsPerPage); |
| |
| function baserel = ... |
| baserel_recalc( baserel, slip_enum_id ) |
| switch slip_enum_id |
| case baserel_rows_id |
| baserel.rows = ceil( baserel.fillfactor * baserel.pages * baserel.maxRowsPerPage ); |
| case baserel_pages_id |
| baserel.pages = ceil( heap_page_size( baserel.rows, baserel.rowsize, ... |
| baserel.BLCKSZ, baserel.modelVersion ) ... |
| / baserel.fillfactor ); |
| case baserel_fillfactor_id |
| baserel.fillfactor = baserel.rows / (baserel.pages * baserel.maxRowsPerPage); |
| end |
| |
| function baserel = ... |
| baserel_set_BLCKSZ_modelVersion( baserel, slip_enum_id, BLCKSZ, modelVersion ) |
| baserel.maxRowsPerPage = MaxHeapTuplesPerPage( baserel.rowsize, BLCKSZ, modelVersion ); |
| baserel.rowsizeBounds = [ MAXALIGN(1), MaxHeapTupleWidth( 1, BLCKSZ, modelVersion ) ]; |
| baserel.rppLimit = MaxHeapTuplesPerPage( MAXALIGN(1), BLCKSZ, modelVersion ); |
| baserel.BLCKSZ = BLCKSZ; |
| baserel.modelVersion = modelVersion; |
| baserel = baserel_recalc( baserel, slip_enum_id ); |
| |
| function baserel = ... |
| baserel_set_rows( baserel, slip_enum_id, nb, rows ) |
| if isempty(nb) |
| cancel; |
| end |
| baserel.rows = ceil( rows ); |
| if slip_enum_id ~= baserel_rows_id |
| baserel = baserel_recalc( baserel, slip_enum_id ); |
| end |
| |
| function baserel = ... |
| baserel_set_pages( baserel, slip_enum_id, nb, pages ) |
| if isempty(nb) |
| cancel; |
| end |
| baserel.pages = ceil( pages ); |
| if slip_enum_id ~= baserel_pages_id |
| baserel = baserel_recalc( baserel, slip_enum_id ); |
| end |
| |
| function baserel = ... |
| baserel_set_fillfactor( baserel, slip_enum_id, nb, fillfactor ) |
| if isempty(nb) |
| cancel; |
| end |
| baserel.fillfactor = fillfactor; |
| if slip_enum_id ~= baserel_fillfactor_id |
| baserel = baserel_recalc( baserel, slip_enum_id ); |
| end |
| |
| function innerBaserelRows = ... |
| innerBaserelRows_make( innerBaserel ) |
| innerBaserelRows = innerBaserel.rows; |
| @} |
| |
| make innerBaserelRows = innerBaserelRows_make( innerBaserel ); |
| |
| //--------------------------------------------------------------------// |
| // enumerator // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function enumerator = ... |
| enumerator_init( enum_name, enum_id ) |
| enumerator = struct(); |
| enumerator.enum_name = enum_name; |
| enumerator.enum_id = enum_id; |
| |
| 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 enum_name = ... |
| enumerator_name( enumerator ) |
| enum_name = enumerator.enum_name; |
| |
| function enum_id = ... |
| enumerator_id( enumerator ) |
| enum_id = enumerator.enum_id; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // enumChooser // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function ix = ... |
| enumChooser_name_to_ix( chooser, enum_name ) |
| ix = find( list2num( 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} : struct(); |
| |
| function enumerator = ... |
| enumChooser_id_to_enum( chooser, enum_id ) |
| ix = enumChooser_id_to_ix( chooser, enum_id ); |
| enumerator = ix ? chooser.enumerators{ix} : struct(); |
| |
| function chooser = ... |
| enumChooser_init( enumerators, label, buttons_per_row, control_ids ) |
| enum_names = map( @enumerator_name, enumerators ); |
| enum_ids = list2num( map( @enumerator_id, 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_drag( enumChooser, id, x1 ) |
| buttonarg = enumChooser_cid_to_barg(enumChooser, id); |
| chosen_enumerator = enumChooser.enumerators{x1 + buttonarg.ix0}; |
| chosen_enum_id = chosen_enumerator.enum_id; |
| |
| function checkmarks = ... |
| checkmarks_make( enumChooser, chosen_enumerators ) |
| chosen_enum_ids = list2num( map( @enumerator_id, chosen_enumerators ) ); |
| 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 |
| |
| function ... |
| checkboxes_draw( enumChooser, checkmarks ) |
| for iba = 1 : length(checkmarks) |
| barg = enumChooser.buttonargs{iba}; |
| button(barg.label, checkmarks(iba), 'checkmark', '', barg.control_id); |
| end |
| |
| function chosen_enumerators = ... |
| checkboxes_drag( enumChooser, chosen_enumerators, id, x1, x0 ) |
| buttonarg = enumChooser_cid_to_barg(enumChooser, id); |
| for iChanged = find(bitget(bitxor(x1, x0), [1:buttonarg.ixM])); |
| ix = iChanged + buttonarg.ix0; |
| if bitget(x1, iChanged) |
| chosen_enumerators = join({enumChooser.enumerators{ix}}, chosen_enumerators); |
| else |
| chosen_enum_ids = map( @enumerator_id, chosen_enumerators ); |
| which_to_drop = ismember( list2num(chosen_enum_ids), enumChooser.enum_ids(ix) ); |
| chosen_enumerators(which_to_drop) = {}; |
| end |
| end |
| @} |
| |
| //--------------------------------------------------------------------// |
| // qualset // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function qualset = ... |
| qualset_init(nquals, selectivity) |
| qualset = struct(); |
| qualset.nquals = nquals; |
| qualset.nquals_save = nquals; |
| qualset.selectivity = selectivity; |
| qualset.selectivity_save = selectivity; |
| |
| function qualset = ... |
| qualset_and(a, b) |
| qualset = struct(); |
| qualset.nquals = a.nquals + b.nquals; |
| qualset.nquals_save = a.nquals_save + b.nquals_save; |
| qualset.selectivity = a.selectivity * b.selectivity; |
| qualset.selectivity_save = a.selectivity_save * b.selectivity_save; |
| |
| function qualsets = ... |
| qualsets_init |
| qualset_empty = qualset_init(0, 1.0); |
| |
| qualsets = struct(); |
| qualsets.index_equijoin = qualset_empty; |
| qualsets.index_nonequijoin = qualset_empty; |
| qualsets.nonindex_equijoin = qualset_empty; |
| qualsets.nonindex_nonequijoin = qualset_empty; |
| qualsets.postjoin = qualset_empty; |
| @} |
| |
| make ( hj_quals, hj_innerBaserel_qualset ) = hj_quals_make( qualsets ) |
| make nj_quals = nj_quals_make(qualsets) |
| |
| function |
| {@ |
| function ( hj_quals, hj_innerBaserel_qualset ) = ... |
| hj_quals_make(qualsets) |
| hj_quals = struct(); |
| hj_quals.hash = qualset_and(qualsets.index_equijoin, qualsets.nonindex_equijoin); |
| hj_quals.postjoin = qualsets.postjoin; |
| hj_innerBaserel_qualset = qualset_and(qualsets.index_nonequijoin, qualsets.nonindex_nonequijoin); |
| |
| function nj_quals = ... |
| nj_quals_make(qualsets) |
| nj_quals = struct(); |
| nj_quals.key = qualsets.index_equijoin; |
| nj_quals.index = qualsets.index_nonequijoin; |
| nj_quals.baserel = qualset_and(qualsets.nonindex_equijoin, qualsets.nonindex_nonequijoin); |
| nj_quals.postjoin = qualsets.postjoin; |
| @} |
| |
| |
| //********************************************************************// |
| // // |
| // User Interface // |
| // // |
| //********************************************************************// |
| |
| |
| //--------------------------------------------------------------------// |
| // "Parameters" dialog // |
| //--------------------------------------------------------------------// |
| |
| figure "Parameters" |
| mousedrag visibleControls_id1 |
| visibleControls = checkboxes_drag( visibleControlsChooser, visibleControls, _id, _x1, _x0 ) |
| mousedrag visibleControls_id2 |
| visibleControls = checkboxes_drag( visibleControlsChooser, visibleControls, _id, _x1, _x0 ) |
| mousedrag visibleControls_id3 |
| visibleControls = checkboxes_drag( visibleControlsChooser, visibleControls, _id, _x1, _x0 ) |
| mousedrag modelVersion_id1 |
| ( modelVersion, innerBaserel ) = ... |
| vers_drag( modelVersionChooser, innerBaserel, slipBaserel, BLCKSZ, _id, _x1 ) |
| mousedrag modelVersion_id2 |
| ( modelVersion, innerBaserel ) = ... |
| vers_drag( modelVersionChooser, innerBaserel, slipBaserel, BLCKSZ, _id, _x1 ) |
| mousedrag modelVersion_id3 |
| ( modelVersion, innerBaserel ) = ... |
| vers_drag( modelVersionChooser, innerBaserel, slipBaserel, BLCKSZ, _id, _x1 ) |
| mousedrag rows_id |
| ( innerRows, innerRowSize, outerRowSize ) = ... |
| data_drag( innerRows, innerRowSize, outerRowSize, _nb, _x1 ) |
| mousedrag gucs_id |
| (cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost) = ... |
| gucs_drag(cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost, _nb, _x1) |
| mousedrag quals_ix_eq_id |
| qualsets = quals_drag(qualsets, _id, _nb, _ix, _x1) |
| mousedrag quals_nx_eq_id |
| qualsets = quals_drag(qualsets, _id, _nb, _ix, _x1) |
| mousedrag quals_ix_ne_id |
| qualsets = quals_drag(qualsets, _id, _nb, _ix, _x1) |
| mousedrag quals_nx_ne_id |
| qualsets = quals_drag(qualsets, _id, _nb, _ix, _x1) |
| mousedrag quals_pj_id |
| qualsets = quals_drag(qualsets, _id, _nb, _ix, _x1) |
| mousedrag blcksz_id |
| ( BLCKSZ, innerBaserel ) = blcksz_drag( innerBaserel, slipBaserel, modelVersion, _nb, _x1 ) |
| mousedrag effective_cache_size_id |
| effective_cache_size = intSlider_drag(_nb, _x1) |
| mousedrag ntup_per_bucket_id |
| NTUP_PER_BUCKET = intSlider_drag(_nb, _x1) |
| mousedrag random_page_cost_id |
| random_page_cost = floatSlider_drag(_nb, _x1) |
| mousedrag seq_page_cost_id |
| seq_page_cost = floatSlider_drag(_nb, _x1) |
| mousedrag work_mem_id |
| work_mem = intSlider_drag(_nb, _x1) |
| mousedrag index_id |
| ( indexPages, indexCorrelation ) = ... |
| index_drag( indexPages, indexCorrelation, _nb, _x1 ) |
| mousedrag baserel_id |
| innerBaserel = baserel_drag( innerBaserel, slipBaserel, _nb, _x1 ) |
| mousedrag baserel_rows_id |
| innerBaserel = baserel_set_rows( innerBaserel, slipBaserel, _nb, _x1 ) |
| mousedrag baserel_pages_id |
| innerBaserel = baserel_set_pages( innerBaserel, slipBaserel, _nb, _x1 ) |
| mousedrag baserel_fillfactor_id |
| innerBaserel = baserel_set_fillfactor( innerBaserel, slipBaserel, _nb, _x1 ) |
| mousedrag slipBaserel_id1 |
| ( unused, slipBaserel ) = radiobuttons_drag( slipBaserelChooser, _id, _x1 ) |
| mousedrag slipBaserel_id2 |
| ( unused, slipBaserel ) = radiobuttons_drag( slipBaserelChooser, _id, _x1 ) |
| draw |
| drawParams( innerRows, innerRowSize, outerRowSize, ... |
| cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost, effective_cache_size, ... |
| random_page_cost, seq_page_cost, work_mem, ... |
| BLCKSZ, NTUP_PER_BUCKET, ... |
| qualsets, ... |
| indexPages, indexCorrelation, ... |
| innerBaserel, ... |
| modelVersion, modelVersionChooser, ... |
| visibleControls, visibleControlsChooser, visibleControlsCheckmarks, ... |
| slipBaserel, slipBaserelChooser ) |
| |
| make visibleControlsCheckmarks = ... |
| checkmarks_make( visibleControlsChooser, visibleControls ) |
| |
| function |
| {@ |
| function ... |
| drawParams( innerRows, innerRowSize, outerRowSize, ... |
| cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost, effective_cache_size, ... |
| random_page_cost, seq_page_cost, work_mem, ... |
| BLCKSZ, NTUP_PER_BUCKET, ... |
| qualsets, ... |
| indexPages, indexCorrelation, ... |
| innerBaserel, ... |
| modelVersion, modelVersionChooser, ... |
| visibleControls, visibleControlsChooser, visibleControlsCheckmarks, ... |
| slipBaserel, slipBaserelChooser ) |
| firsttab = 'Baserel non-equijoin (1.0000, 10)_'; |
| visibleControls_draw( firsttab, visibleControlsChooser, visibleControlsCheckmarks ); |
| for vc_enumerator = visibleControls |
| switch vc_enumerator.enum_id |
| case vc_vers_id |
| vers_draw( modelVersionChooser, modelVersion, BLCKSZ, firsttab ); |
| case vc_data_id |
| data_draw( innerRows, innerRowSize, outerRowSize ); |
| case vc_gucs_id |
| gucs_draw( cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost, ... |
| random_page_cost, seq_page_cost, work_mem ); |
| case vc_const_id |
| const_draw( NTUP_PER_BUCKET ); |
| case vc_quals_id |
| quals_draw( qualsets ); |
| case vc_index_id |
| index_draw( indexPages, indexCorrelation, effective_cache_size ); |
| case vc_baserel_id |
| baserel_draw( innerBaserel, slipBaserel, slipBaserelChooser, firsttab ); |
| end |
| end |
| |
| function ... |
| visibleControls_draw( firsttab, visibleControlsChooser, visibleControlsCheckmarks ) |
| settabs([firsttab, '\t\b', visibleControlsChooser.longestName, '__']); |
| checkboxes_draw( visibleControlsChooser, visibleControlsCheckmarks ); |
| settabs(firsttab); |
| |
| function ... |
| vers_draw( modelVersionChooser, modelVersion, BLCKSZ, firsttab ) |
| text('-------------------- Version'); |
| settabs([firsttab, '\t\b', modelVersionChooser.longestName, '_']); |
| radiobuttons_draw( modelVersionChooser, modelVersion.enum_id ); |
| settabs(firsttab); |
| |
| labels = sprintf( 'Block size (%d KB)', BLCKSZ/1024 ); |
| slider( labels, log2( BLCKSZ/4096 ), [0,6], '', '', blcksz_id ); |
| |
| function ( modelVersion, innerBaserel ) = ... |
| vers_drag( modelVersionChooser, innerBaserel, slipBaserel, BLCKSZ, id, x1 ) |
| modelVersion = radiobuttons_drag( modelVersionChooser, id, x1 ); |
| innerBaserel = baserel_set_BLCKSZ_modelVersion( innerBaserel, slipBaserel, BLCKSZ, modelVersion ); |
| |
| function ( BLCKSZ, innerBaserel ) = ... |
| blcksz_drag( innerBaserel, slipBaserel, modelVersion, nb, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| BLCKSZ = 2^round(x1+12); |
| innerBaserel = baserel_set_BLCKSZ_modelVersion( innerBaserel, slipBaserel, BLCKSZ, modelVersion ); |
| |
| function ... |
| data_draw( innerRows, innerRowSize, outerRowSize ) |
| text('-------------------- Data'); |
| labels = sprintf('innerRows (%d)\ninnerRowSize (%d)\nouterRowSize (%d)', innerRows, innerRowSize, outerRowSize); |
| slider( labels, [ innerRows; innerRowSize; outerRowSize ], [ 0,1000000; 4,1000; 4,1000 ], '', '', rows_id ); |
| |
| function ( innerRows, innerRowSize, outerRowSize ) = ... |
| data_drag( innerRows, innerRowSize, outerRowSize, nb, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| switch nb |
| case 1 |
| innerRows = round(x1); |
| case 2 |
| innerRowSize = MAXALIGN(round(x1)); |
| case 3 |
| outerRowSize = MAXALIGN(round(x1)); |
| end |
| |
| function ... |
| gucs_draw( cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost, ... |
| random_page_cost, seq_page_cost, work_mem ) |
| text('-------------------- GUCs'); |
| labels = sprintf( 'work_mem (%d KB)', work_mem ); |
| slider( labels, work_mem, [32,4*1024*1024], 'L', '', work_mem_id ); |
| labels = sprintf( 'random_page_cost (%d)', random_page_cost ); |
| slider( labels, random_page_cost, [0,200], '', '', random_page_cost_id ); |
| labels = sprintf( 'seq_page_cost (%d)', seq_page_cost ); |
| slider( labels, seq_page_cost, [0,200], '', '', seq_page_cost_id ); |
| labels = sprintf( 'cpu_tuple_cost (%f)\ncpu_operator_cost (%f)\ncpu_index_tuple_cost (%f)', cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost ); |
| slider( labels, [cpu_tuple_cost; cpu_operator_cost; cpu_index_tuple_cost], [0,0.1; 0,0.1; 0,0.1], '', '', gucs_id ); |
| |
| function (cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost) = ... |
| gucs_drag(cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost, nb, x1) |
| if isempty(nb) |
| cancel; |
| end |
| switch nb |
| case 1 |
| cpu_tuple_cost = x1; |
| case 2 |
| cpu_operator_cost = x1; |
| case 3 |
| cpu_index_tuple_cost = x1; |
| end |
| |
| function ... |
| const_draw( NTUP_PER_BUCKET ) |
| text('-------------------- #defines'); |
| labels = sprintf('ntup_per_bucket (%d)', NTUP_PER_BUCKET); |
| slider(labels, NTUP_PER_BUCKET, [1,20], '', '', ntup_per_bucket_id); |
| |
| function ... |
| quals_draw( qualsets ) |
| text('-------------------- Quals\t Selectivity (green), Number of quals (blue)'); |
| labels = sprintf('Equijoin index key (%f, %d)', qualsets.index_equijoin.selectivity, qualsets.index_equijoin.nquals); |
| slider(labels, [qualsets.index_equijoin.selectivity, 1.1+0.1*qualsets.index_equijoin.nquals], [0,2.1], '', 'gb', quals_ix_eq_id); |
| labels = sprintf('Equijoin non-index (%f, %d)', qualsets.nonindex_equijoin.selectivity, qualsets.nonindex_equijoin.nquals); |
| slider(labels, [qualsets.nonindex_equijoin.selectivity, 1.1+0.1*qualsets.nonindex_equijoin.nquals], [0,2.1], '', 'gb', quals_nx_eq_id); |
| labels = sprintf('Other index key (%f, %d)', qualsets.index_nonequijoin.selectivity, qualsets.index_nonequijoin.nquals); |
| slider(labels, [qualsets.index_nonequijoin.selectivity, 1.1+0.1*qualsets.index_nonequijoin.nquals], [0,2.1], '', 'gb', quals_ix_ne_id); |
| labels = sprintf('Baserel non-join (%f, %d)', qualsets.nonindex_nonequijoin.selectivity, qualsets.nonindex_nonequijoin.nquals); |
| slider(labels, [qualsets.nonindex_nonequijoin.selectivity, 1.1+0.1*qualsets.nonindex_nonequijoin.nquals], [0,2.1], '', 'gb', quals_nx_ne_id); |
| labels = sprintf('Join but not equijoin (%f, %d)', qualsets.postjoin.selectivity, qualsets.postjoin.nquals); |
| slider(labels, [qualsets.postjoin.selectivity, 1.1+0.1*qualsets.postjoin.nquals], [0,2.1], '', 'gb', quals_pj_id); |
| |
| function qualsets = ... |
| quals_drag( qualsets, id, nb, ix, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| switch id |
| case quals_ix_eq_id |
| qualsets.index_equijoin = qualset_drag( qualsets.index_equijoin, ix, x1 ); |
| case quals_nx_eq_id |
| qualsets.nonindex_equijoin = qualset_drag( qualsets.nonindex_equijoin, ix, x1 ); |
| case quals_ix_ne_id |
| qualsets.index_nonequijoin = qualset_drag( qualsets.index_nonequijoin, ix, x1 ); |
| case quals_nx_ne_id |
| qualsets.nonindex_nonequijoin = qualset_drag( qualsets.nonindex_nonequijoin, ix, x1 ); |
| case quals_pj_id |
| qualsets.postjoin = qualset_drag( qualsets.postjoin, ix, x1 ); |
| end |
| |
| function qualset = ... |
| qualset_drag( qualset, ix, x1 ) |
| switch ix |
| case 1 |
| qualset.selectivity = min(max(x1, 0), 1); |
| qualset.selectivity_save = qualset.selectivity; |
| // If selectivity is less than 100%, assume there is at least one qual. |
| qualset.nquals = max((qualset.selectivity < 1.0 ? 1 : 0), qualset.nquals_save); |
| case 2 |
| qualset.nquals = min(max(round((x1-1.1)*10), 0), 10); |
| qualset.nquals_save = qualset.nquals; |
| // If no quals, force selectivity to 100%. |
| qualset.selectivity = max((qualset.nquals == 0 ? 1.0 : 0), qualset.selectivity_save); |
| end |
| |
| function ... |
| index_draw( indexPages, indexCorrelation, effective_cache_size ) |
| text('-------------------- Index on inner baserel'); |
| labels = sprintf('Index pages (%d)\nIndex correlation (%f)', indexPages, indexCorrelation); |
| slider(labels, [indexPages; indexCorrelation], [1,1000000; -1,1], 'L-', '', index_id); |
| labels = sprintf( 'effective_cache_size (%d)', effective_cache_size ); |
| slider( labels, effective_cache_size, [100,1000000], 'L', '', effective_cache_size_id ); |
| |
| function ( indexPages, indexCorrelation ) = ... |
| index_drag( indexPages, indexCorrelation, nb, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| switch nb |
| case 1 |
| indexPages = round(x1); |
| case 2 |
| indexCorrelation = x1; |
| end |
| |
| function ... |
| baserel_draw( baserel, slipBaserel, slipBaserelChooser, firsttab ) |
| text('-------------------- Inner Baserel'); |
| settabs([firsttab, '\t\b', slipBaserelChooser.longestName, '_']); |
| radiobuttons_draw( slipBaserelChooser, slipBaserel ); |
| settabs(firsttab); |
| |
| labels = sprintf( 'Rows (%d)', baserel.rows ); |
| slider( labels, baserel.rows, [1,100000000], 'L', '', baserel_rows_id ); |
| labels = sprintf( 'Pages (%d)', baserel.pages ); |
| slider( labels, baserel.pages, [1,100000000], 'L', '', baserel_pages_id ); |
| labels = sprintf( 'Fill factor (%f)', baserel.fillfactor ); |
| slider( labels, baserel.fillfactor, [0.01,1.0], '', '', baserel_fillfactor_id ); |
| labels = sprintf( 'Row size (%d)\nMax rows/page (%d)', ... |
| baserel.rowsize, baserel.maxRowsPerPage ); |
| slider( labels, ... |
| [ baserel.rowsize; baserel.maxRowsPerPage ], ... |
| [ baserel.rowsizeBounds; 1,baserel.rppLimit ], ... |
| 'L-', '', baserel_id ); |
| |
| function baserel = ... |
| baserel_drag( baserel, slipBaserel, nb, x1 ) |
| if isempty(nb) |
| cancel; |
| end |
| switch nb |
| case 1 |
| baserel.rowsize = round(x1); |
| baserel.maxRowsPerPage = MaxHeapTuplesPerPage( baserel.rowsize, baserel.BLCKSZ, baserel.modelVersion ); |
| case 2 |
| baserel.maxRowsPerPage = round(x1); |
| baserel.rowsize = MaxHeapTupleWidth( baserel.maxRowsPerPage, baserel.BLCKSZ, baserel.modelVersion ); |
| end |
| baserel = baserel_recalc( baserel, slipBaserel ); |
| |
| 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); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // "Join Cost" plot // |
| //--------------------------------------------------------------------// |
| |
| figure "Join Cost" |
| mouseover (_msg,_cursor) = xy_mouseover(_id,_x0,_y0) |
| |
| draw joinCost_draw( hj_innerTotalCost, nj_innerTotalCost, ... |
| innerRows, innerRowSize, outerRowSize, ... |
| nj_quals, hj_quals, ... |
| cpu_tuple_cost, cpu_operator_cost, seq_page_cost, ... |
| hj_nbuckets, hj_nbatch, ... |
| BLCKSZ, modelVersion ) |
| |
| make hj_innerTotalCost = cost_seqscan( innerBaserel, hj_innerBaserel_qualset, ... |
| cpu_tuple_cost, cpu_operator_cost, seq_page_cost ) |
| |
| make nj_innerTotalCost = cost_index( innerBaserel, nj_quals, ... |
| indexPages, indexCorrelation, ... |
| cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, ... |
| effective_cache_size, random_page_cost, modelVersion ) |
| |
| make (hj_nbuckets, hj_nbatch) = ExecChooseHashTableSize(innerRows, innerRowSize, ... |
| work_mem, NTUP_PER_BUCKET, ... |
| modelVersion) |
| |
| function |
| {@ |
| function ... |
| joinCost_draw( hj_innerTotalCost, nj_innerTotalCost, ... |
| innerRows, innerRowSize, outerRowSize, ... |
| nj_quals, hj_quals, ... |
| cpu_tuple_cost, cpu_operator_cost, seq_page_cost, ... |
| hj_nbuckets, hj_nbatch, ... |
| BLCKSZ, modelVersion ) |
| scale loglog; |
| scale('lock', [1,1e9,1,1e9]); |
| sc = scale; |
| outerRowSamples = 10.^[log10(max(1,sc(1))):.1:log10(max(1,sc(2)))]; |
| for outerRows = outerRowSamples |
| njCost(end+1) = cost_nestloop( nj_innerTotalCost, ... |
| outerRows, innerRows, nj_quals, ... |
| cpu_tuple_cost, cpu_operator_cost ); |
| (hjCost(end+1), hjIO(end+1)) = cost_hashjoin( hj_innerTotalCost, ... |
| outerRows, innerRows, ... |
| innerRowSize, outerRowSize, ... |
| hj_quals, ... |
| cpu_tuple_cost, cpu_operator_cost, seq_page_cost, ... |
| hj_nbuckets, hj_nbatch, ... |
| BLCKSZ, modelVersion ); |
| end |
| plotoption('brlegend'); |
| plot(outerRowSamples, njCost, 'r', xytrace_id); |
| plot(outerRowSamples, hjCost, 'b', xytrace_id); |
| plot(outerRowSamples, hjIO, 'g', xytrace_id); |
| label('Outer Rows', 'Cost'); |
| legend('NJ Cost\nHJ Cost\nHJ I/O Cost', 'rbg'); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // "Hash table" plot // |
| //--------------------------------------------------------------------// |
| |
| figure "Hash Table" |
| mouseover |
| (_msg,_cursor) = xy_mouseover(_id,_x0,_y0) |
| mousedrag innerRows_id |
| (_msg,innerRows) = intLine_drag(_id, _x1) |
| mouseover innerRows_id |
| (_msg,_cursor) = intLine_mouseover(_id, _x0) |
| |
| draw hashTable_draw(innerRows, innerRowSize, work_mem, BLCKSZ, NTUP_PER_BUCKET, modelVersion) |
| |
| function |
| {@ |
| function ... |
| hashTable_draw(innerRows, innerRowSize, work_mem, BLCKSZ, NTUP_PER_BUCKET, modelVersion) |
| scale loglog; |
| scale([1,1e8,1,1e6]); |
| sc = scale; |
| rowSamples = 10.^[log10(max(1,sc(1))):.1:log10(max(1,sc(2)))]; |
| for rows = rowSamples |
| (nbuckets(end+1), nbatch(end+1)) = ExecChooseHashTableSize(rows, innerRowSize, ... |
| work_mem, NTUP_PER_BUCKET, ... |
| modelVersion); |
| hashpages(end+1) = (nbatch(end) <= 1) ? 0 : page_size(rows, innerRowSize, BLCKSZ, modelVersion); |
| end |
| plotoption('brlegend'); |
| plot(rowSamples, nbatch, 'h(ff8000)', xytrace_id); |
| plot(rowSamples, nbuckets, 'c', xytrace_id); |
| plot(rowSamples, hashpages, 'g', xytrace_id); |
| line([1,0], innerRows, 'y', innerRows_id); |
| label('Inner Rows'); |
| legend('Batches\nBuckets\nOverflow Pages', 'h(ff8000)cg'); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // "Inner Index Scan" plot // |
| //--------------------------------------------------------------------// |
| |
| figure "Inner Index Scan" |
| mouseover |
| (_msg,_cursor) = xy_mouseover(_id,_x0,_y0) |
| mousedrag innerBaserelRows_id |
| (_msg,innerBaserel) = innerBaserelRows_drag( innerBaserel, slipBaserel, _id, _x1 ) |
| mouseover innerBaserelRows_id |
| (_msg,_cursor) = intLine_mouseover(_id, _x0) |
| |
| draw indexScanCost_draw( innerBaserel, nj_quals, ... |
| indexPages, indexCorrelation, ... |
| cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, ... |
| effective_cache_size, random_page_cost, modelVersion ) |
| |
| function |
| {@ |
| function ... |
| indexScanCost_draw( baserel, nj_quals, ... |
| indexPages, indexCorrelation, ... |
| cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, ... |
| effective_cache_size, random_page_cost, modelVersion ) |
| scale loglog; |
| scale('lock', [1,1e7,1,1e9]); |
| sc = scale; |
| relTuplesSamples = 10.^[log10(max(1,sc(1))):.1:log10(max(1,sc(2)))]; |
| for relTuples = relTuplesSamples |
| ixCost(end+1) = cost_index( baserel_set_rows( baserel, baserel_pages_id, 0, relTuples ), ... |
| nj_quals, ... |
| indexPages, indexCorrelation, ... |
| cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, ... |
| effective_cache_size, random_page_cost, modelVersion ); |
| end |
| plotoption('brlegend'); |
| plot(relTuplesSamples, ixCost, 'r', xytrace_id); |
| line([1,0], baserel.rows, 'y', innerBaserelRows_id); |
| label('Rows', 'Cost'); |
| legend('Index Cost', 'r'); |
| |
| function ( msg, baserel ) = ... |
| innerBaserelRows_drag( baserel, slipBaserel, id, x1 ) |
| ( msg, rows ) = intLine_drag( id, x1 ); |
| baserel = baserel_set_rows( baserel, slipBaserel, 0, rows ); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // common UI functions // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function (msg, v) = ... |
| intLine_drag(id, x1) |
| v = round(x1); |
| msg = intLine_mouseover(id, v); |
| |
| function (msg,cursor) = ... |
| intLine_mouseover(id, x0) |
| switch id |
| case innerRows_id |
| fmt = 'innerRows=%d'; |
| case innerBaserelRows_id |
| fmt = 'inner baserel rows=%d'; |
| otherwise |
| fmt = '%d'; |
| end |
| msg = sprintf(fmt, x0); |
| cursor = true; |
| |
| function (msg,cursor) = ... |
| xy_mouseover(id, x0, y0) |
| if isempty(id) |
| cancel; |
| end |
| msg = sprintf('x=%g y=%g', x0, y0); |
| cursor = false; |
| @} |
| |
| |
| //********************************************************************// |
| // // |
| // Cost model // |
| // // |
| //********************************************************************// |
| |
| |
| //--------------------------------------------------------------------// |
| // Nested Join cost // |
| //--------------------------------------------------------------------// |
| // Last updated 24 March 2007. |
| function |
| {@ |
| function njCost = ... |
| cost_nestloop( innerTotalCost, outerRows, innerRows, nj_quals, ... |
| cpu_tuple_cost, cpu_operator_cost ) |
| joininfactor = 1; |
| run_cost(end+1) = outerRows * innerTotalCost * joininfactor; |
| ntuples = outerRows * innerRows * joininfactor; |
| restrict_qual_cost = cost_qual_eval(nj_quals.postjoin, cpu_operator_cost); |
| cpu_per_tuple = cpu_tuple_cost + restrict_qual_cost; |
| run_cost(end+1) = cpu_per_tuple * ntuples; |
| njCost = sum(run_cost); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // Hash Join cost // |
| //--------------------------------------------------------------------// |
| // Last updated 24 March 2007. |
| |
| function |
| {@ |
| function (nbuckets, nbatch) = ... |
| ExecChooseHashTableSize(ntuples, tupwidth, work_mem, NTUP_PER_BUCKET, modelVersion) |
| 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 = floor(hash_table_bytes / tupsize / NTUP_PER_BUCKET); |
| dbatch = ceil(inner_rel_bytes / hash_table_bytes); |
| nbatch = 2; |
| while nbatch < dbatch |
| nbatch = nbatch * 2; |
| end |
| else |
| switch modelVersion.enum_id |
| case ver_gp240 : ver_gpmax |
| // 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 |
| for prime = [1033, 2063, 4111, 8219, 16417, 32779, 65539, 131111, ... |
| 262151, 524341, 1048589, 2097211, 4194329, 8388619, 16777289, 33554473, ... |
| 67108913, 134217773, 268435463, 536870951, 1073741831] |
| if prime >= nbuckets |
| nbuckets = prime; |
| break; |
| end |
| end |
| |
| function (total_cost, io_cost) = ... |
| cost_hashjoin( innerTotalCost, ... |
| outer_path_rows, inner_path_rows, innerRowSize, outerRowSize, ... |
| hj_quals, ... |
| cpu_tuple_cost, cpu_operator_cost, seq_page_cost, ... |
| numbuckets, numbatches, ... |
| BLCKSZ, modelVersion ) |
| num_hashclauses = hj_quals.hash.nquals; |
| |
| // cost and selectivity of the hashquals and other restriction clauses |
| hash_qual_cost = cost_qual_eval(hj_quals.hash, cpu_operator_cost); |
| qp_qual_cost = cost_qual_eval(hj_quals.postjoin, cpu_operator_cost); |
| hash_selec = hj_quals.hash.selectivity; |
| |
| // approx # tuples passing the hash quals |
| hashjointuples = max(1, hash_selec * outer_path_rows * inner_path_rows); |
| |
| // cost of source data |
| startup_cost(end+1) = innerTotalCost; |
| |
| // cost of computing hash function |
| switch modelVersion.enum_id |
| case [ver_pg822 : ver_pgmax, ver_gp240 : ver_gpmax] |
| // REL8_2_STABLE:tgl:20070108160931, MAIN:tgl:20070108160922 |
| 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 |
| innerbucketsize = ceil(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); |
| 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; |
| switch modelVersion.enum_id |
| case ver_pg822 : ver_pgmax |
| // searching within hash chains |
| // REL8_2_STABLE:tgl:20070108160931, MAIN:tgl:20070108160922 |
| run_cost(end+1) = hash_qual_cost * ... |
| outer_path_rows * ceil(inner_path_rows * innerbucketsize) * ... |
| joininfactor * 0.5; |
| case [ver_pgmin : ver_pg822 - 1] |
| // searching within hash chains |
| run_cost(end+1) = hash_qual_cost * ... |
| outer_path_rows * ceil(inner_path_rows * innerbucketsize) * ... |
| joininfactor; |
| case ver_gp2302 : ver_gpmax |
| // MAIN:kharriman:20070227025348, Release-2_3_0-branch:kharriman:20070227024315, Release-2_3_0-branch:kharriman:20070227023643 |
| // evaluating hash join quals |
| run_cost(end+1) = hash_qual_cost * hashjointuples; |
| // searching within hash chains |
| run_cost(end+1) = 0.05 * cpu_operator_cost * outer_path_rows * inner_path_rows * innerbucketsize * joininfactor; |
| case ver_gp230 : ver_gp2302 - 1 |
| // bad mistake: MAIN:kharriman:20061105005913, Release-2_3_0-branch:kharriman:20061105010029 |
| run_cost(end+1) = hash_qual_cost * hashjointuples * inner_path_rows * innerbucketsize * joininfactor; |
| end |
| |
| // 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_gp230 : ver_gpmax |
| resultRows_punt = hashjointuples; // assume all rows pass |
| run_cost(end+1) = qp_qual_cost * hashjointuples + 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_pg822 : ver_pgmax, ver_gp240 : ver_gpmax] |
| // REL8_2_STABLE:tgl:20070108160931, MAIN:tgl:20070108160922 |
| // deleted the bias |
| case ver_pgmin : ver_pg822 - 1 |
| bias = sqrt(innerbytes/outerbytes); |
| case ver_gpmin : ver_gp240 - 1 |
| bias = 1 + 0.1*log(innerbytes/outerbytes); |
| end |
| end |
| |
| total_cost = sum(startup_cost) + bias*sum(run_cost); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // Index Scan cost // |
| //--------------------------------------------------------------------// |
| function |
| {@ |
| function total_cost = ... |
| cost_index( baserel, nj_quals, ... |
| indexPages, indexCorrelation, ... |
| cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, ... |
| effective_cache_size, random_page_cost, modelVersion ) |
| // call index-access-method-specific code |
| indexTotalCost = btcostestimate( baserel.rows, indexPages, nj_quals, ... |
| cpu_index_tuple_cost, cpu_operator_cost ); |
| run_cost(end+1) = indexTotalCost; |
| |
| // clamp index correlation to 99% or less (greenplum) |
| if ismember( modelVersion.enum_id, [ver_gpmin : ver_gpmax] ) |
| indexCorrelation = min(max(indexCorrelation, -0.99), 0.99); |
| end |
| |
| // estimate number of main-table tuples and pages fetched |
| tuples_fetched = max(1, nj_quals.key.selectivity * nj_quals.index.selectivity * baserel.rows); |
| T = max(1, baserel.pages); |
| b = max(1, effective_cache_size); |
| if T <= b |
| pages_fetched = min(T, 2 * T * tuples_fetched / (2 * T + tuples_fetched)); |
| else |
| lim = (2.0 * T * b) / (2.0 * T - b); |
| if tuples_fetched <= lim |
| pages_fetched = (2.0 * T * tuples_fetched) / (2.0 * T + tuples_fetched); |
| else |
| pages_fetched = b + (tuples_fetched - lim) * (T - b) / T; |
| end |
| end |
| |
| // total disk I/O cost for main table accesses |
| min_IO_cost = ceil( nj_quals.key.selectivity * nj_quals.index.selectivity * T ); |
| max_IO_cost = pages_fetched * random_page_cost; |
| min_IO_cost = min_IO_cost + random_page_cost; |
| csquared = indexCorrelation * indexCorrelation; |
| run_cost(end+1) = max_IO_cost + csquared * (min_IO_cost - max_IO_cost); |
| |
| // cpu costs per tuple |
| baserestrictcost = cost_qual_eval( nj_quals.baserel, cpu_operator_cost ); |
| cpu_per_tuple = cpu_tuple_cost + baserestrictcost; |
| // if !is_injoin then cpu_per_tuple -= cost_qual_eval( nj_quals.index, cpu_operator_cost ); |
| run_cost(end+1) = cpu_per_tuple * tuples_fetched; |
| total_cost = sum(run_cost); |
| |
| function indexTotalCost = ... |
| btcostestimate( relTuples, indexPages, nj_quals, ... |
| cpu_index_tuple_cost, cpu_operator_cost ) |
| numIndexTuples = max(1, nj_quals.key.selectivity * relTuples); |
| indexTotalCost = genericcostestimate( numIndexTuples, relTuples, indexPages, nj_quals, ... |
| cpu_index_tuple_cost, cpu_operator_cost ); |
| |
| function totalCost = ... |
| genericcostestimate( numIndexTuples, relTuples, indexPages, nj_quals, ... |
| cpu_index_tuple_cost, cpu_operator_cost ) |
| numIndexTuples = max(1, ceil(numIndexTuples)); |
| |
| if indexPages > 1 && relTuples > 0 |
| numIndexPages = numIndexTuples / relTuples * (indexPages - 1); |
| numIndexPages = ceil(numIndexPages) + 1; // count the metapage too |
| else |
| numIndexPages = 1; |
| end |
| |
| qual_op_cost = cpu_operator_cost * nj_quals.index.selectivity; |
| qual_arg_cost = 0; |
| totalCost = numIndexPages + qual_arg_cost + numIndexTuples * (cpu_index_tuple_cost + qual_op_cost); |
| @} |
| |
| //--------------------------------------------------------------------// |
| // Sequential Scan cost // |
| //--------------------------------------------------------------------// |
| // Last updated 29 March 2007. |
| |
| function |
| {@ |
| function run_cost = ... |
| cost_seqscan( baserel, qualset, ... |
| cpu_tuple_cost, cpu_operator_cost, seq_page_cost ) |
| baserestrictcost_per_tuple = cost_qual_eval( qualset, cpu_operator_cost ); |
| cpu_per_tuple = cpu_tuple_cost + baserestrictcost_per_tuple; |
| run_cost = seq_page_cost * baserel.pages + cpu_per_tuple * baserel.rows; |
| @} |
| |
| //--------------------------------------------------------------------// |
| // common cost model functions // |
| //--------------------------------------------------------------------// |
| // Last updated 24 March 2007. |
| |
| function |
| {@ |
| function per_tuple = ... |
| cost_qual_eval( qualset, cpu_operator_cost ) |
| // costsize.c |
| per_tuple = qualset.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 ); |
| |
| function ntuples = ... |
| MaxHeapTuplesPerPage( width, BLCKSZ, modelVersion ) |
| // htup.h |
| // NB: sizeof_HeapTupleHeaderData has already been MAXALIGNed |
| ntuples = floor( (BLCKSZ - sizeof_PageHeaderData) / ... |
| (MAXALIGN( width ) + modelVersion.sizeof_HeapTupleHeaderData + sizeof_ItemIdData) ); |
| |
| function nbytes = ... |
| MaxHeapTupleWidth( tuplesPerPage, BLCKSZ, modelVersion ) |
| // htup.h |
| nbytes = BLCKSZ - MAXALIGN( sizeof_PageHeaderData + tuplesPerPage * sizeof_ItemIdData ) - tuplesPerPage * modelVersion.sizeof_HeapTupleHeaderData; |
| nbytes = bitand( floor( nbytes / tuplesPerPage ), -MAXIMUM_ALIGNOF ); |
| |
| function npages = ... |
| heap_page_size(tuples, width, BLCKSZ, modelVersion) |
| npages = ceil( tuples / MaxHeapTuplesPerPage( width, BLCKSZ, modelVersion ) ); |
| @} |
| |
| |
