From 658096e12470ed69be9843aae6e4fd120a2c6cef Mon Sep 17 00:00:00 2001 From: drh <> Date: Fri, 15 Sep 2023 14:06:44 +0000 Subject: [PATCH] The view-scan optimization was added to enhance the performance of one specific query, but it causes performance regressions on a host of others. Disable it (at least temporarily) in order to try to find an alternative way of fixing the one specific query that it was created to fix - an alternative way that does not cause performance problems for other unrelated queries. FossilOrigin-Name: d9625a9eafe88859c3255849ee750cc6322d5a13ddad45107f9b2085c20eff50 --- manifest | 20 +++++++++++--------- manifest.uuid | 2 +- src/where.c | 11 ----------- src/whereInt.h | 2 +- test/with3.test | 4 ++-- 5 files changed, 15 insertions(+), 24 deletions(-) diff --git a/manifest b/manifest index 2cc282e161..f758c7f204 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Do\snot\stry\sto\sconvert\sa\sdouble\sinto\san\sunsigned\s64-bit\sinteger,\sas\sthat\sdoes\nnot\swork\son\sall\splatforms.\s\sA\sdouble\scan\sonly\sbe\sconverted\sinto\sa\ssigned\n64-bit\sinteger. -D 2023-09-15T10:24:29.345 +C The\sview-scan\soptimization\swas\sadded\sto\senhance\sthe\sperformance\sof\sone\sspecific\nquery,\sbut\sit\scauses\sperformance\sregressions\son\sa\shost\sof\sothers.\s\sDisable\sit\n(at\sleast\stemporarily)\sin\sorder\sto\stry\sto\sfind\san\salternative\sway\sof\sfixing\sthe\none\sspecific\squery\sthat\sit\swas\screated\sto\sfix\s-\san\salternative\sway\sthat\sdoes\nnot\scause\sperformance\sproblems\sfor\sother\sunrelated\squeries. +D 2023-09-15T14:06:44.625 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -795,8 +795,8 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9 F src/wal.c 01e051a1e713d9eabdb25df38602837cec8f4c2cae448ce2cf6accc87af903e9 F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452 F src/walker.c 7c7ea0115345851c3da4e04e2e239a29983b61fb5b038b94eede6aba462640e2 -F src/where.c 86f901cdbc80bd8fd50c89f72c16a0ae8501c5df03caaa9a99ca72676ad79b56 -F src/whereInt.h c7d19902863beadec1d04e66aca39c0bcd60b74f05f0eaa7422c7005dfc5d51a +F src/where.c faa2798d7607827a21758802949033934adfd4414e3360ecf3ac603cd2906929 +F src/whereInt.h 4b38c5889514e3aead3f27d0ee9a26e47c3f150efc59e2a8b4e3bc8835e4d7a1 F src/wherecode.c 5d77db30a2a3dd532492ae882de114edba2fae672622056b1c7fd61f5917a8f1 F src/whereexpr.c dc5096eca5ed503999be3bdee8a90c51361289a678d396a220912e9cb73b3c00 F src/window.c b7ad9cff3ce8ae6f8cc25e18e1a258426cb6bd2999aace6f5248d781b2a74098 @@ -1991,7 +1991,7 @@ F test/windowfault.test 15094c1529424e62f798bc679e3fe9dfab6e8ba2f7dfe8c923b6248c F test/windowpushd.test d8895d08870b7226f7693665bd292eb177e62ca06799184957b3ca7dc03067df F test/with1.test b93833890e5d2a368e78747f124503a0159aa029b98e9ed4795ebf630b2efd3d F test/with2.test a1df41b987198383b9b70bf5e5fda390582e46398653858dbc6ceb24253b28df -F test/with3.test fe15975c0b53c9098a757902a908e3f8d6d80ce47c5363ac600f28a79ef8c0ca +F test/with3.test e30369ea27aa27eb1bda4c5e510c8a9f782c8afd2ab99d1a02b8a7f25a5d3e65 F test/with4.test 257be66c0c67fee1defbbac0f685c3465e2cad037f21ce65f23f86084f198205 F test/with5.test 6248213c41fab36290b5b73aa3f937309dfba337004d9d8434c3fabc8c7d4be8 F test/with6.test e097a03e5c898a8cd8f3a2d6a994ec510ea4376b5d484c2b669a41001e7758c8 @@ -2121,9 +2121,11 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 858fa236f8b673eb03cf2242e8202cd33ae1f13a43b59bdbb2eecdb21d42ef55 0ab05871ba36e2a0eddfbc700c1d39891c54c453727a4effcdac3327efec163b -R 471f1110fdf5d26a5080351103b3b730 -T +closed 0ab05871ba36e2a0eddfbc700c1d39891c54c453727a4effcdac3327efec163b +P ce339046416e9ddeffe77f71a8bfb8c5b302160c3207f919aebd62902618ed59 +R babd19c6e7de52d779b950e0bf75e81a +T *branch * rethink-viewscan +T *sym-rethink-viewscan * +T -sym-trunk * U drh -Z 5b5a1b021f1cd0ac8b138bf678d74c57 +Z a05c488a130b226d147d3f39ce159578 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index a478c84dce..6621cde6ef 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -ce339046416e9ddeffe77f71a8bfb8c5b302160c3207f919aebd62902618ed59 \ No newline at end of file +d9625a9eafe88859c3255849ee750cc6322d5a13ddad45107f9b2085c20eff50 \ No newline at end of file diff --git a/src/where.c b/src/where.c index 01ae180ae7..1d8ba88ba9 100644 --- a/src/where.c +++ b/src/where.c @@ -3700,9 +3700,6 @@ static int whereLoopAddBtree( #else pNew->rRun = rSize + 16; #endif - if( IsView(pTab) || (pTab->tabFlags & TF_Ephemeral)!=0 ){ - pNew->wsFlags |= WHERE_VIEWSCAN; - } ApplyCostMultiplier(pNew->rRun, pTab->costMult); whereLoopOutputAdjust(pWC, pNew, rSize); rc = whereLoopInsert(pBuilder, pNew); @@ -5121,14 +5118,6 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ rUnsorted -= 2; /* TUNING: Slight bias in favor of no-sort plans */ } - /* TUNING: A full-scan of a VIEW or subquery in the outer loop - ** is not so bad. */ - if( iLoop==0 && (pWLoop->wsFlags & WHERE_VIEWSCAN)!=0 && nLoop>1 ){ - rCost += -10; - nOut += -30; - WHERETRACE(0x80,("VIEWSCAN cost reduction for %c\n",pWLoop->cId)); - } - /* Check to see if pWLoop should be added to the set of ** mxChoice best-so-far paths. ** diff --git a/src/whereInt.h b/src/whereInt.h index 759e774e3a..343aca9f36 100644 --- a/src/whereInt.h +++ b/src/whereInt.h @@ -633,7 +633,7 @@ void sqlite3WhereTabFuncArgs(Parse*, SrcItem*, WhereClause*); #define WHERE_BLOOMFILTER 0x00400000 /* Consider using a Bloom-filter */ #define WHERE_SELFCULL 0x00800000 /* nOut reduced by extra WHERE terms */ #define WHERE_OMIT_OFFSET 0x01000000 /* Set offset counter to zero */ -#define WHERE_VIEWSCAN 0x02000000 /* A full-scan of a VIEW or subquery */ + /* 0x02000000 -- available for reuse */ #define WHERE_EXPRIDX 0x04000000 /* Uses an index-on-expressions */ #endif /* !defined(SQLITE_WHEREINT_H) */ diff --git a/test/with3.test b/test/with3.test index 650740dcc1..9b110debf3 100644 --- a/test/with3.test +++ b/test/with3.test @@ -132,8 +132,8 @@ do_eqp_test 3.2.2 { | | `--SCALAR SUBQUERY xxxxxx | | `--SCAN w2 | `--RECURSIVE STEP - | |--SCAN c - | `--SCAN w1 + | |--SCAN w1 + | `--SCAN c |--SCAN c |--SEARCH w2 USING INTEGER PRIMARY KEY (rowid=?) `--SEARCH w1 USING INTEGER PRIMARY KEY (rowid=?) -- 2.47.2