12

Randi Vertongen's answer correctly addresses how you can get the plan you want with the parameterized version of the query. This answer supplements that by addressing the title of the question in case you are interested in the details. SQL Server rewrites tail-recursive common table expressions (CTEs) as iteration. Everything from the Lazy Index Spool down ...


9

Though at the moment I don't have the title of the actual hotfix, the better query plan will be used when enabling the query optimizer hotfixes on your version (SQL Server 2012). Some other methods are: Using OPTION(RECOMPILE) so the filtering happens earlier, on the literal value. On SQL Server 2016 or higher the hotfixes before this version are applied ...


9

You can find out the storage size with SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4'); typname | typlen ---------+-------- bool | 1 int4 | 4 (2 rows) However you need to take alignment into account: SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4'); typname | typlen | typalign ---...


6

SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step boundary. I do not have the same histograms as you. On my fresh copy of AdventureWorks2014 on SQL Server 2014 SP3 CU4 with no modifications to the table since the ...


3

I have a table for statistic values, it holds millions of records ... The clustered index goes fragmeted to 90+% in a day or so. Look at your clustered index, its key is 48 bytes long, it's not a good choice because your table is big enough and you have also 5 nonclustered indexes. All of them have these 48 bytes at every index level, so every ...


2

Historically, MySQL has kept the relevant algorithms simple, even if less than optimal. In particular, OR has been poorly optimized. 5.7 and 8.0 have new code, but I don't know if your examples have improved any. EXPLAIN FORMAT=JSON sometimes gives clues. Also, see the "Optimizer Trace". If you have sample data, use the Handler counts to deduce whether ...


2

That is just as it should be. If you return a significant part of the table, the overhead of the bitmap index scan part is not worth paying, because you have to visit most of the heap blocks anyway. So PostgreSQL just skips that part and goes to the heap scan directly. The result is a sequential scan.


1

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part. It is a snippet problem. show index from test Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression :---- | ---------: | :------- | -----...


1

For people looking for other possible reasons of this error: In my case I got the same error with my query where somebody used with forceseek hint. Finally I figured out that table was a HEAP and the creation of a Clustered Index solved the problem.


1

There are two parts to your question. The first is how to make the MERGE operation more reliably performant. I think that changing your merge statement into something closer to what I have below will help keep table locks to a minimum. The use of the CTE allows for what I feel a more straightforward selecting of the data you would want to apply to the ...


1

"explode-implode" First it gathers all the colunns from flights, then joins to probably multiple rows in a second table. This leads to duplicating all the columns in flights. At then end you boil it down to exactly one copy. SO... Start by doing all the necessary sums in m2m_order_flights. This should be boiled down to one row per flight_id. You ...


1

MATCH is not the primary villain in the sluggishness; these are: OR Using LIKE / RLIKE pagination via OFFSET Most of the above prevent the use of any INDEX, hence the query must scan the entire table, thereby being slow for lots of reasons. Some can be solved in SQL; most require changes to the specifications for the query. Since the first thing the ...


1

Please don't cross post. Here are some thoughts... Increase innodb_buffer_pool_size to about 70% of available RAM. Don't try to process each message as it comes in -- 200 complex, multi-table, statements will overwhelm the disk. Instead... Gather the data that comes in. Separately batch-process groups of data. This will probably require unraveling the ...


1

See the docs for ALTER TABLESPACE. Since version 9.6, you can set "effective_io_concurrency" on a per-tablespace basis. You can do so for "random_page_cost" long before 9.6. These changes have to be made inside the database itself. For discoverability purposes, I would also add a comment inside postgresql.conf near the global settings, saying that they ...


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