Tag Info

Hot answers tagged

41

"I'm more wondering why the query optimizer would ever use the plan it currently does." To put it another way, the question is why the following plan looks cheapest to the optimizer, compared with the alternatives (of which there are many). The inner side of the join is essentially running a query of the following form for each correlated value of ...


16

I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause, and then at the end just shrink that result set down to the n number of rows that was requested. The graphical execution plan seems to indicate this is the case -- TOP is the "last" step. But it appears there is more going ...


16

When I run your script to create a statistics only database and the query in the question I get the following plan. The Table Cardinalities shown in the plan are tblFEStatsPaperHits: 48063400 tblFEStatsBrowsers : 339 So it estimates that it will need to perform the scan on tblFEStatsPaperHits 339 times. Each scan has the correlated predicate ...


15

In my book even one scan of 50M rows is unacceptable... My usual trick is to materialize the distinct values and delegate the engine with keeping it up to date: create view [dbo].[vwFEStatsPaperHitsBrowserID] with schemabinding as select BrowserID, COUNT_BIG(*) as big_count from [dbo].[tblFEStatsPaperHits] group by [BrowserID]; go create unique clustered ...


13

The query optimizer does have n-ary operators, though the execution engine has rather fewer. To illustrate, I'm going to use a simplified version of your tables - (SQL Fiddle). SELECT DISTINCT number INTO foo FROM master..spt_values WHERE number < 1000; SELECT DISTINCT number INTO boo FROM master..spt_values WHERE number between 300 ...


11

As documented in the article Statistics Used by the Query Optimizer in Microsoft SQL Server 2005 If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables ...


9

This bug has been known for years, and won't ever be fixed. I first reported it 6 years ago but it has existed far longer than that. These are just estimated costs and don't really have any bearing on the optimizer itself and whether you can trust it. It's simply the showplan output that has some questionable math. ...


8

The examples in the question do not quite produce the same results (the OFFSET example has an off-by-one error). The updated forms below fix that issue, remove the extra sort for the ROW_NUMBER case, and use variables to make the solution more general: DECLARE @PageSize bigint = 10, @PageNumber integer = 3; WITH Numbered AS ( SELECT TOP ...


8

The recent 5.6 version has added this feature. See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer Typical Usage: # Turn tracing on (it's off by default): SET optimizer_trace="enabled=on"; SELECT ...; # your query here SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; # possibly more queries... # When done with tracing, disable it: SET ...


8

Certain functions that are known to be runtime constants go through the process called constant folding. By 'folding' a constant an expression is evaluated early in the query execution, the result is cached and the cached result instead when needed. The expression in your query DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())) is, afaik, a runtime constant and thus ...


8

I have rather bad news for you in this regard MySQL Query Optimizer tends to stray away for further optimization once it sees a FULLTEXT index. I have written about this before in the StackExchange May 23, 2011 : http://stackoverflow.com/a/6092216/491757 Oct 25, 2011: FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional Jan ...


8

I've worked round the issue like this, but I'm hoping there is a less kludgy way: explain analyze with recursive w(n) as ( select 1 union all select n+1 from w where n<5 ) select * from w limit (select count(*) from w); /* QUERY PLAN ...


7

One good source to refer to on FORCE INDEX would be the book MySQL Database Design and Tuning. On page 120 paragraph 4, it says: Where does FORCE INDEX fit in? FORCE INDEX (first enabled in version 4.0.9) is very similar to USE INDEX; the main difference between the two options is that FORCE INDEX demands that MySQL use the index (if possible) in ...


7

I found the solution! It is so beautiful and I actually learned a LOT about Oracle. In one word: histograms. I started reading a lot about how Oracle's CBO works and I stumbled upon histograms. I didn't fully understand so I took a look at the USER_HISTOGRAMS table, and voilá. There were several rows for the sick table, and practically nothing for the ...


7

(This answers the other question about why the histograms are different.) Histograms are created by default based on column skew and whether the column was used in a relevant predicate. Copying the DDL and the data is not enough, the workload information is also important. According to the Performance Tuning Guide: When you drop a table, workload ...


7

It seems the CBO does not consider a skip scan at all with dynamic sampling, is this true? Actually this is really easy to verify, you can do this by enabling 10053 trace. You will see that the optimizer does not even consider skip scan at all. The reason for this, is the "_optimizer_skip_scan_guess" parameter. The default value for this parameter is ...


5

For SQL Server we have the option of the FORCE ORDER hint. The only comparable I'm aware of for MySQL is STRAIGHT_JOIN. STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order. However, I'm inclined to ...


5

When you use TOP, the Optimizer sees an opportunity to do less work. If you ask for 10 rows, then there's a good chance it doesn't need to consume the whole set. So the TOP operator can be pushed much further to the right. It will keep requesting rows from the next operator (on its right), until it has received enough. You point out that without TOP, the ...


5

Is this normal for a forced join order to make the query estimates to be completely inaccurate (and thus query times unpredictable)? The use of FORCE ORDER isn't making estimates inaccurate, the deletion of rows did. Forcing an update of statistics on the table may improve the estimation accuracy. Should I just expect that I'll have to either ...


5

As long as you are sure the server was up for that entire time, and that nobody cleared out DMV stats inadvertently. This can happen if the database is detached + re-attached / restored / offline + online / auto-close + online, or if the index has been explicitly dropped / re-created (the DMV is not affected by disable / rebuild / reorganize, except in the ...


5

I emailed Jonathan Lewis about this and got a very helpful reply: The oddity in the calculation is a consequence of the limits on character-based histograms, see particularly: http://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/ http://jonathanlewis.wordpress.com/2010/10/19/frequency-histograms-6/ Looking at the example, the ...


4

With a slight fiddling of your query I get an equal cost estimate (50/50) and equal IO stats: ; WITH cte AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #objects ) SELECT * FROM cte WHERE r >= 30 AND r < 40 ORDER BY r SELECT * FROM #objects ORDER BY object_id OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY This avoids the additional ...


4

The result of the query seems to be correct and the server executes it quickly. But the root node of the execution plan has a warning: Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice What does that mean in this context? Is such a complex filter ...


4

You can get some of that information by turning on the configuration parameter log_planner_stats. Most of that information, however, doesn't really exist, because the planner does not fully compute all alternative plans and their costs. It only explores an alternative plan until it can determine that it is slower than the current best plan. So alternative ...


4

No, there is no way to save a query plan to disk, force a query plan, etc. Fix your optimizer parameters and make sure your stats are accurate. You didn't bother to mention your version, show explain analyze output, etc so I can't help you with that in detail. Start here: http://wiki.postgresql.org/wiki/Slow_Query_Questions Check your rowcount estimates ...


3

OPTIMIZE TABLE basically does three(3) things Shrinks the data pages Shrinks index pages Computes Fresh Index Statistics Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable USE mydb CREATE TABLE mytabletmp LIKE mytable; INSERT INTO mytabletmp SELECT * FROM mytable; ALTER TABLE mytable RENAME mytablezap; ALTER TABLE mytabletmp ...


3

The two queries have a very big difference: ----- query 1 SELECT COUNT(*) FROM customers WHERE ID > 10000 AND country = 'US' ; ----- query 2 SELECT * FROM customers WHERE ID > 10000 AND country = 'US' ; While the second query returns all rows that match the WHERE conditions, the first one has an aggregate function (COUNT()) in the SELECT ...


3

I posted this answer in response to the same question on StackOverflow, so here is a re-post: An important distinction to make is that according to IBM, zIIP is only available for "eligible database workloads", and those "eligible" loads are mostly targeted for large BI/ERP/CRM solutions that run on distributed servers, which are connecting through DDF ...


3

You have to keep in mind two major aspects about using the MEMORY Storage Engine ASPECT #1 The MEMORY table behaves like a MyISAM table in that it performs a full table lock when doing INSERTs, UPDATEs, and DELETEs (MySQL Documentation says Locking granularity : Table). I would be very concerned with a high lock ratio if it bottlenecks your I/O ...


3

First of all, note that not all databases support all three (Loop, Hash, Merge) types of join. For example, you cannot hash join in all version MySQL (thanks for the comment pointing out that this now works in MariaDb). Second, databases will often have vendor specific syntax to force certain join strategies and orders. For example, SQL Server uses the ...



Only top voted, non community-wiki answers of a minimum length are eligible