From: <di...@us...> - 2011-01-20 07:00:23
|
Revision: 52168 http://firebird.svn.sourceforge.net/firebird/?rev=52168&view=rev Author: dimitr Date: 2011-01-20 07:00:17 +0000 (Thu, 20 Jan 2011) Log Message: ----------- Fixed CORE-3312: Sub-optimal join plan when the slave table depends on the master one via the OR predicate. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2011-01-20 04:41:10 UTC (rev 52167) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2011-01-20 07:00:17 UTC (rev 52168) @@ -2067,6 +2067,12 @@ matches.add(currentInv->matches[j]); } } + if (currentInv->boolean) + { + if (!matches.exist(currentInv->boolean)) { + matches.add(currentInv->boolean); + } + } invCandidate->matches.join(matches); if (acceptAll) { continue; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2011-01-29 11:30:28
|
Revision: 52224 http://firebird.svn.sourceforge.net/firebird/?rev=52224&view=rev Author: dimitr Date: 2011-01-29 11:30:22 +0000 (Sat, 29 Jan 2011) Log Message: ----------- These checks don't seem required for the new ODS 11 optimizer logic. But they may cause bad effects, see the second example in CORE-3312: Sub-optimal join plan when the slave table depends on the master one via the OR predicate. Let's remove them and see whether any regressions arise. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2011-01-28 18:58:44 UTC (rev 52223) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2011-01-29 11:30:22 UTC (rev 52224) @@ -1300,9 +1300,6 @@ // First, handle "AND" comparisons (all nodes except nod_or) for (OptimizerBlk::opt_conjunct* tail = opt_begin; tail < opt_end; tail++) { - if (tail->opt_conjunct_flags & opt_conjunct_matched) { - continue; - } jrd_nod* const node = tail->opt_conjunct_node; if (!(tail->opt_conjunct_flags & opt_conjunct_used) && node && (node->nod_type != nod_or)) { @@ -1319,9 +1316,6 @@ InversionCandidate* invCandidate = NULL; for (OptimizerBlk::opt_conjunct* tail = opt_begin; tail < opt_end; tail++) { - if (tail->opt_conjunct_flags & opt_conjunct_matched) { - continue; - } jrd_nod* const node = tail->opt_conjunct_node; if (!(tail->opt_conjunct_flags & opt_conjunct_used) && node && (node->nod_type == nod_or)) { This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2011-02-25 12:48:35
|
Revision: 52462 http://firebird.svn.sourceforge.net/firebird/?rev=52462&view=rev Author: dimitr Date: 2011-02-25 12:48:29 +0000 (Fri, 25 Feb 2011) Log Message: ----------- Fixed the regression I introduced recently. This is the simplest version of the fix. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2011-02-25 12:26:57 UTC (rev 52461) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2011-02-25 12:48:29 UTC (rev 52462) @@ -2520,6 +2520,9 @@ **************************************/ bool forward = true; + if (boolean->nod_flags & nod_deoptimize) + return false; + jrd_nod* match = boolean->nod_arg[0]; jrd_nod* value = (boolean->nod_count < 2) ? NULL : boolean->nod_arg[1]; jrd_nod* value2 = (boolean->nod_type == nod_between) ? boolean->nod_arg[2] : NULL; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2012-01-10 05:27:25
|
Revision: 53833 http://firebird.svn.sourceforge.net/firebird/?rev=53833&view=rev Author: dimitr Date: 2012-01-10 05:27:19 +0000 (Tue, 10 Jan 2012) Log Message: ----------- The simplest possible fix for CORE-3722: IS NOT DISTINCT FROM NULL doesn't use index. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2012-01-10 05:26:49 UTC (rev 53832) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2012-01-10 05:27:19 UTC (rev 53833) @@ -2580,7 +2580,7 @@ CMP_get_desc(tdbb, optimizer->opt_csb, match, &desc1); CMP_get_desc(tdbb, optimizer->opt_csb, value, &desc2); - if (!BTR_types_comparable(desc1, desc2)) + if (value->nod_type != nod_null && !BTR_types_comparable(desc1, desc2)) return false; // if the indexed column is of type int64, we need to inject an This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ro...@us...> - 2012-03-09 08:27:25
|
Revision: 54115 http://firebird.svn.sourceforge.net/firebird/?rev=54115&view=rev Author: robocop Date: 2012-03-09 08:27:15 +0000 (Fri, 09 Mar 2012) Log Message: ----------- Misc. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2012-03-09 08:12:28 UTC (rev 54114) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2012-03-09 08:27:15 UTC (rev 54115) @@ -1812,7 +1812,7 @@ // ASF: Order is more precise than equivalence class. // We can't use the next segments, and we'll need to use // INTL_KEY_PARTIAL to construct the last segment's key. - scratch.fuzzy = true;; + scratch.fuzzy = true; } } } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2012-06-10 13:23:05
|
Revision: 54620 http://firebird.svn.sourceforge.net/firebird/?rev=54620&view=rev Author: dimitr Date: 2012-06-10 13:22:59 +0000 (Sun, 10 Jun 2012) Log Message: ----------- Optimizer level solution for CORE-2790/CORE-3449. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2012-06-10 12:35:58 UTC (rev 54619) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2012-06-10 13:22:59 UTC (rev 54620) @@ -1795,6 +1795,24 @@ for (int j = 0; j < scratch.idx->idx_count; j++) { IndexScratchSegment* segment = scratch.segments[j]; + + // Special case: IS NULL against the in-the-middle segment of the ascending compound index. + // In ODS11, such a retrieval is not better than the one for the prior segment. So stop before + // utilizing that segment and leave the boolean for other possible matches. + if (!(scratch.idx->idx_flags & idx_descending) && + segment->scanType == segmentScanMissing && + j > 0 && j < scratch.idx->idx_count - 1) + { + IndexScratchSegment* const next_segment = scratch.segments[j + 1]; + + if (next_segment->scanType != segmentScanEqual && + next_segment->scanType != segmentScanEquivalent && + next_segment->scanType != segmentScanMissing) + { + break; + } + } + if (segment->scope == scope) { scratch.scopeCandidate = true; } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2013-06-29 13:47:03
|
Revision: 58272 http://sourceforge.net/p/firebird/code/58272 Author: dimitr Date: 2013-06-29 13:47:00 +0000 (Sat, 29 Jun 2013) Log Message: ----------- Fixed CORE-4118: Expression index may be not used for derived fields or view fields. Also, generalized the old trick with CAST in expression indices. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2013-06-29 11:32:55 UTC (rev 58271) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2013-06-29 13:47:00 UTC (rev 58272) @@ -456,30 +456,54 @@ dsc dsc1, dsc2; dsc *desc1 = &dsc1, *desc2 = &dsc2; - if (node1->nod_type == nod_cast && node2->nod_type == nod_field) + if (node1->nod_type == nod_cast) { + jrd_nod* const source = node1->nod_arg[e_cast_source]; + CMP_get_desc(tdbb, opt->opt_csb, node1, desc1); - CMP_get_desc(tdbb, opt->opt_csb, node2, desc2); + CMP_get_desc(tdbb, opt->opt_csb, source, desc2); if (DSC_EQUIV(desc1, desc2, true) && - OPT_expression_equal2(tdbb, opt, node1->nod_arg[e_cast_source], node2, stream)) + OPT_expression_equal2(tdbb, opt, source, node2, stream)) { return true; } } - if (node1->nod_type == nod_field && node2->nod_type == nod_cast) + if (node2->nod_type == nod_cast) { - CMP_get_desc(tdbb, opt->opt_csb, node1, desc1); - CMP_get_desc(tdbb, opt->opt_csb, node2, desc2); + jrd_nod* const source = node2->nod_arg[e_cast_source]; + CMP_get_desc(tdbb, opt->opt_csb, node2, desc1); + CMP_get_desc(tdbb, opt->opt_csb, source, desc2); + if (DSC_EQUIV(desc1, desc2, true) && - OPT_expression_equal2(tdbb, opt, node1, node2->nod_arg[e_cast_source], stream)) + OPT_expression_equal2(tdbb, opt, source, node1, stream)) { return true; } } + if (node1->nod_type == nod_derived_expr) + { + jrd_nod* const expression = node1->nod_arg[e_derived_expr_expr]; + + if (OPT_expression_equal2(tdbb, opt, expression, node2, stream)) + { + return true; + } + } + + if (node2->nod_type == nod_derived_expr) + { + jrd_nod* const expression = node2->nod_arg[e_derived_expr_expr]; + + if (OPT_expression_equal2(tdbb, opt, expression, node1, stream)) + { + return true; + } + } + return false; } @@ -666,6 +690,14 @@ } break; + case nod_derived_expr: + if (OPT_expression_equal2(tdbb, opt, node1->nod_arg[e_derived_expr_expr], + node2->nod_arg[e_derived_expr_expr], stream)) + { + return true; + } + break; + case nod_list: { jrd_nod** ptr1 = node1->nod_arg; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2014-01-24 14:38:21
|
Revision: 59073 http://sourceforge.net/p/firebird/code/59073 Author: dimitr Date: 2014-01-24 14:38:17 +0000 (Fri, 24 Jan 2014) Log Message: ----------- I hope this will finally close the bad planning issue during restore. To be field tested. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2014-01-24 00:59:14 UTC (rev 59072) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2014-01-24 14:38:17 UTC (rev 59073) @@ -2196,7 +2196,8 @@ // This flag disables our smart index selection algorithm. // It's set for any explicit (i.e. user specified) plan which // requires all existing indices to be considered for a retrieval. - const bool acceptAll = csb->csb_rpt[stream].csb_plan; + // It's also set for internal (system) requests used by the engine itself. + const bool acceptAll = csb->csb_rpt[stream].csb_plan || (csb->csb_g_flags & csb_internal); double totalSelectivity = MAXIMUM_SELECTIVITY; // worst selectivity double totalIndexCost = 0; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2014-08-25 13:40:39
|
Revision: 59996 http://sourceforge.net/p/firebird/code/59996 Author: dimitr Date: 2014-08-25 13:40:36 +0000 (Mon, 25 Aug 2014) Log Message: ----------- Fixed CORE-4530: DB_KEY based join of two tables may be ineffective. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2014-08-25 13:28:19 UTC (rev 59995) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2014-08-25 13:40:36 UTC (rev 59996) @@ -2839,10 +2839,14 @@ // Find the side of the equality that is potentially a dbkey. If // neither, make the obvious deduction - if (dbkey->nod_type != nod_dbkey && dbkey->nod_type != nod_concatenate) + if ((dbkey->nod_type != nod_dbkey || ((USHORT)(IPTR) dbkey->nod_arg[0]) != stream) && + dbkey->nod_type != nod_concatenate) { - if (value->nod_type != nod_dbkey && value->nod_type != nod_concatenate) + if ((value->nod_type != nod_dbkey || ((USHORT)(IPTR) value->nod_arg[0]) != stream) && + value->nod_type != nod_concatenate) + { return NULL; + } dbkey = value; value = boolean->nod_arg[0]; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2015-03-06 10:20:19
|
Revision: 60844 http://sourceforge.net/p/firebird/code/60844 Author: dimitr Date: 2015-03-06 10:20:17 +0000 (Fri, 06 Mar 2015) Log Message: ----------- Minor adjustments to the join order selection. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2015-03-06 09:02:34 UTC (rev 60843) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2015-03-06 10:20:17 UTC (rev 60844) @@ -3462,24 +3462,29 @@ * is cheaper as withRelationship. * **************************************/ - if (checkRelationship->cost == 0) { + if (checkRelationship->cost == 0) return true; - } - if (withRelationship->cost == 0) { + + if (withRelationship->cost == 0) return false; - } const double compareValue = checkRelationship->cost / withRelationship->cost; if (compareValue >= 0.98 && compareValue <= 1.02) { - // cost is nearly the same, now check on cardinality - if (checkRelationship->cardinality < withRelationship->cardinality) { + // cost is nearly the same, now check uniqueness and cardinality + + if (checkRelationship->unique == withRelationship->unique) + { + if (checkRelationship->cardinality < withRelationship->cardinality) + return true; + } + else if (checkRelationship->unique) return true; - } + else if (withRelationship->unique) + return false; } - else if (checkRelationship->cost < withRelationship->cost) { + else if (checkRelationship->cost < withRelationship->cost) return true; - } return false; } @@ -3786,20 +3791,22 @@ indexRelationship->stream = testStream->stream; indexRelationship->unique = candidate->unique; indexRelationship->cost = candidate->cost; - indexRelationship->cardinality = csb_tail->csb_cardinality * candidate->selectivity; + indexRelationship->cardinality = candidate->unique ? + csb_tail->csb_cardinality : csb_tail->csb_cardinality * candidate->selectivity; // indexRelationship are kept sorted on cost and unique in the indexRelations array. - // The unique and cheapest indexed relatioships are on the first position. + // The unique and cheapest indexed relationships are on the first position. size_t index = 0; for (; index < baseStream->indexedRelationships.getCount(); index++) { - if (cheaperRelationship(indexRelationship, baseStream->indexedRelationships[index])) { + if (cheaperRelationship(indexRelationship, baseStream->indexedRelationships[index])) break; - } } + baseStream->indexedRelationships.insert(index, indexRelationship); testStream->previousExpectedStreams++; } + delete candidate; delete optimizerRetrieval; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2016-02-21 09:00:13
|
Revision: 62994 http://sourceforge.net/p/firebird/code/62994 Author: dimitr Date: 2016-02-21 09:00:11 +0000 (Sun, 21 Feb 2016) Log Message: ----------- Corrected my old commit re. optimization of the system requests. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2016-02-21 08:54:31 UTC (rev 62993) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2016-02-21 09:00:11 UTC (rev 62994) @@ -2192,11 +2192,13 @@ // when the table grows. In this case, let's consider all available indices. const bool smallTable = (streamCardinality <= THRESHOLD_CARDINALITY); - // This flag disables our smart index selection algorithm. - // It's set for any explicit (i.e. user specified) plan which - // requires all existing indices to be considered for a retrieval. - // It's also set for internal (system) requests used by the engine itself. - const bool acceptAll = csb->csb_rpt[stream].csb_plan || (csb->csb_g_flags & csb_internal); + // These flags work around our smart index selection algorithm. Any explicit + // (i.e. user specified) plan which requires all existing indices to be + // considered for a retrieval. Internal (system) requests used by the engine + // itself are often optimized using zero or non-actual statistics, so they are + // processed using somewhat relaxed rules. + const bool customPlan = csb->csb_rpt[stream].csb_plan; + const bool sysRequest = (csb->csb_g_flags & csb_internal); double totalSelectivity = MAXIMUM_SELECTIVITY; // worst selectivity double totalIndexCost = 0; @@ -2273,7 +2275,7 @@ } } invCandidate->matches.join(matches); - if (acceptAll) { + if (customPlan) { continue; } @@ -2291,7 +2293,7 @@ } } - if (anyMatchAlreadyUsed && !acceptAll) + if (anyMatchAlreadyUsed && !customPlan) { currentInv->used = true; // If a match on this index was already used by another @@ -2443,7 +2445,7 @@ // Test if the new totalCost will be higher than the previous totalCost // and if the current selectivity (without the bestCandidate) is already good enough. - if (acceptAll || smallTable || firstCandidate || + if (customPlan || sysRequest || smallTable || firstCandidate || (totalCost < previousTotalCost && totalSelectivity > minimumSelectivity)) { // Exclude index from next pass @@ -2520,7 +2522,7 @@ if (invCandidate->unique) { // Single unique full equal match is enough - if (!acceptAll) + if (!customPlan) break; } } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2016-02-21 09:03:15
|
Revision: 62996 http://sourceforge.net/p/firebird/code/62996 Author: dimitr Date: 2016-02-21 09:03:12 +0000 (Sun, 21 Feb 2016) Log Message: ----------- Misc. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2016-02-21 09:02:11 UTC (rev 62995) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2016-02-21 09:03:12 UTC (rev 62996) @@ -2193,10 +2193,10 @@ const bool smallTable = (streamCardinality <= THRESHOLD_CARDINALITY); // These flags work around our smart index selection algorithm. Any explicit - // (i.e. user specified) plan which requires all existing indices to be - // considered for a retrieval. Internal (system) requests used by the engine - // itself are often optimized using zero or non-actual statistics, so they are - // processed using somewhat relaxed rules. + // (i.e. user specified) plan requires all existing indices to be considered + // for a retrieval. Internal (system) requests used by the engine itself are + // often optimized using zero or non-actual statistics, so they are processed + // using somewhat relaxed rules. const bool customPlan = csb->csb_rpt[stream].csb_plan; const bool sysRequest = (csb->csb_g_flags & csb_internal); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2016-02-29 19:19:22
|
Revision: 63049 http://sourceforge.net/p/firebird/code/63049 Author: dimitr Date: 2016-02-29 19:19:19 +0000 (Mon, 29 Feb 2016) Log Message: ----------- Fixed CORE-5122: Expression index may not be used by the optimizer if created and used in different connection charsets. Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2016-02-29 18:17:26 UTC (rev 63048) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2016-02-29 19:19:19 UTC (rev 63049) @@ -522,12 +522,9 @@ case nod_literal: { const dsc* const desc1 = &((Literal*) node1)->lit_desc; - const UCHAR* const ptr1 = desc1->dsc_address; - const dsc* const desc2 = &((Literal*) node2)->lit_desc; - const UCHAR* const ptr2 = desc2->dsc_address; - if (DSC_EQUIV(desc1, desc2, true) && !memcmp(ptr1, ptr2, desc1->dsc_length)) + if (desc1 && desc2 && !MOV_compare(desc1, desc2)) { return true; } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <di...@us...> - 2016-02-29 19:41:56
|
Revision: 63050 http://sourceforge.net/p/firebird/code/63050 Author: dimitr Date: 2016-02-29 19:41:54 +0000 (Mon, 29 Feb 2016) Log Message: ----------- Misc (simplified the code). Modified Paths: -------------- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp Modified: firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp =================================================================== --- firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2016-02-29 19:19:19 UTC (rev 63049) +++ firebird/branches/B2_5_Release/src/jrd/Optimizer.cpp 2016-02-29 19:41:54 UTC (rev 63050) @@ -524,7 +524,7 @@ const dsc* const desc1 = &((Literal*) node1)->lit_desc; const dsc* const desc2 = &((Literal*) node2)->lit_desc; - if (desc1 && desc2 && !MOV_compare(desc1, desc2)) + if (!MOV_compare(desc1, desc2)) { return true; } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |