Hot answers tagged

11

You generally followed a delusion - that partitioning will give you a significant performance boost for queries compared to a standard index. This is not the case. There is little difference between filtering with an index and with a partition. Partitions are not there to make your queries faster, but to allow fast DELETE - by swapping out a partition with ...


11

Hmmm... The leaf level of a clustered index is the table in SQL Server. This is comparable to a phone book (to borrow from @BrentO) in that all entries in the book are stored in the order of the index - i.e. The index is ordered logically alphabetically (and physically if there is no fragmentation). This is somewhat similar to a physically contiguous in-...


10

I'd like to know why the optimizer does not use the clustered index, but is using the non-clustered one? This will be a decision of the cost based optimizer. It estimates that it is cheaper to fully scan the narrow index. It seems that you were expecting a nested loops with seeks on the clustered index? The execution plan shows that the table #ToPurge ...


8

TomTom has a great answer with which I completely agree. sp_BlitzErik rightly cites Kendra Little as a good source for acknowledging that partitioning is not a performance feature: Why Table Partitioning Doesn’t Speed Up Query Performance Partitioning is, in fact, a data management feature. As noted by TomTom, bulk DELETE operations that encompass the data ...


7

Execution plan still shows ClusterIndex Seek why? The initial seek down the b-tree is to find the first row where CustomerID >= 1. From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon as ...


7

I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them. This is a knee-jerk response to a performance problem. Your page counts are less than 10 ! Rebuilding the indexes will be a least help in your case. I would start by reading and understanding How to analyse SQL Server performance ...


4

Yes, adding indexes could cause IO wait to increase. Perhaps without the index, you are doing a lot of full scans of the table, thousands or millions of blocks, to get just one piece of data. But the IO wait is very low, because the kernel read ahead keeps the pump primed so your process doesn't wait on IO (instead it uses a lot of User CPU to filter ...


3

Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans? Yes, the optimizer should recompile the plans since changes to an underlying table are one of the triggers that result in a plan recompilation. Since checking a constraint ...


2

The answer would be simple here, assuming you just have one clustered index on table and your query is like select * from customer where CustomerID between 1 and 70000 In above case seek would be preferred by optimizer because first the index would search the data based on condition CustomerID=1 and would find the first row which matches the predicate. ...


2

The difference is not like arrays vs. linked lists (Both are data structures - But not the correct ones.). Both types of indexes use B-Trees (Data Structure) to organize the data. The difference is how the B-Tree is organized. For a clustered index, the data is organized in the data structure with the leafs being physically organized on the disk. This ...


2

The first query is logical nonsense for your case: SELECT count(DISTINCT a.id) from the_view a; It makes no sense to use DISTINCT with the id defined unique. The second query is typically faster with a sequential scan: SELECT count(*) from the_view a; Indexes don't contain visibility information. Due to the MVCC model of Postgres it needs to check ...


2

If the index contains both cid and email, there is no need to look at the records. All the data will be retrieved from the index. This is a common optimization. The where clause should (on average) reduce the record count to less than four percent of the records. This should be a small enough set to make using the index faster than table scanning. ...


2

According to the official ALTER TABLE documentation, there should be no issue with such an operation and there is no window when there is no UNIQUE constraint at all. See (emphasis mine): Storage, Performance, and Concurrency Considerations In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that ...


1

First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to extract keys, but that's not so. Assuming all your JSON arrays are either empty or hold integer numbers as demonstrated. And the scalar values (non-arrays) are also ...


1

This is working as intended. The indexes are objects written to the disk. The index creation is also written to the online logs. Creating indexes is expected to take up disk space and cause I/O operations.


1

That happens when InnoDB compressed tables with large amount of rows are being updated (no matter if the algorithm is in place). Large operations for ALTER tables, will use the "online log", which is expected to consume space. You can check this using SHOW ENGINE INNODB STATUS in the History Length line. This happens always that you have large operations ...


1

(This answer is probably more than you wanted. It is an in-depth discussion of the source of the problem, plus multiple solutions.) Long ago, when MySQL added CHARACTER SET utf8, it needed up to 3 bytes per character. And indexing was limited to 767 bytes, enough for VARCHAR(255). When utf8mb4 was added (because utf8 was incomplete), the encoding needed ...


1

If you solve it by adding a secondary index then imho MyISAM or InnoDB won't matter much, as both use B-Tree indexes. Append both additional columns to the index: (col3, col1, col2) or (col3, col2, col1) should behave the same for your query. The reason to add all the columns is that such index covers your query - all data needed to resolve the query are ...


1

The MSDN page says this about reorganizing and rebuilding indexes: The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when ...


1

You can change the tablespace of the index explicitly with alter index command. CREATE TABLESPACE superfastssdtablespace LOCATION '/path/to/super/fast/ssds'; ALTER INDEX name_of_the_index SET TABLESPACE superfastssdtablespace; Before you do that you may (also) want to look at partitioning your table or using BRIN indexes depending on the use case.


1

3 problems with that partitioning: BY HASH is useless. SUBPARTITIONing is useless. Hundreds of partitions is inefficient. Stick with only PARTITION BY RANGE(TO_DAYS(rq_date)). The big benefit is in the 'sliding window'. More comments in my partitioning blog. What percentage of rows have t_id <> -1? If more than about 20%, it won't use an index ...


1

The "best" index for this query seems to be (cat_id, visible, primary, updated). Thanks to jkavalik


1

The large number of nscanned key comparisons is explainable by the skip value: the query is skipping 903,462 documents (ntoskip) in order to return 21 (ntoreturn). The nscanned value in your output is the sum of ntoskip and ntoreturn. The number of nscannedObjects (identical to nscanned) is because the skip stage in MongoDB 2.6.x query processing happens ...



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