97

It certainly is. We discussed that in great detail under this related question: Working of indexes in PostgreSQL Space is allocated in multiples of MAXALIGN, which is typically 8 bytes on a 64-bit OS or (much less common) 4 bytes on a 32-bit OS. If you are not sure, check pg_controldata. It also depends on data types of indexed columns (some require ...


57

You can keep the following in mind when caring about updating statistics (copied from Rebuilding Indexes vs. Updating Statistics (Benjamin Nevarez) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table. By default, the UPDATE STATISTICS statement updates both ...


44

When SQL Server creates a missing index recommendation for a particular query plan, it separates possible key columns into 2 groups. The first set contains all of the recommended columns that are part of an EQUALITY predicate. The second set contains all of the recommended columns that are part of an INEQUALITY predicate. Within each set, the columns are ...


40

Answer Since you refer to the website use-the-index-luke.com, consider the chapter: Use The Index, Luke › The Where Clause › Searching For Ranges › Greater, Less and BETWEEN It has an example that matches your situation perfectly (two-column index, one is tested for equality, the other for range), explains (with more of those nice index graphics) why @...


36

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows. string ... 20 characters long, which in memory is roughly 5x that of the integer (if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per ...


36

Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider. Since you're on SQL Server 2017, loading once, and running reports, why not just use a clustered column store index instead? That seems to be the ideal solution to your need to index every possible column combination. Columnstore ...


34

Short rules of thumb. (Some of these are created automatically, but can possibly be manually dropped later, depending on your dbms. Don't assume you will always work on PostgreSQL.) Index every primary key. Index every foreign key. Index every column used in a JOIN clause. Index every column used in a WHERE clause. Study your documentation to learn the "...


27

A nonclustered index that has the same key(s)* as the clustered index, may still be useful, because the nonclustered index will usually be smaller and denser. Remember, a clustered index includes all in-row data, so it is normally the widest (least dense) index possible. * The same key columns, in the same sequence, sorted the same way (asc/desc). For a ...


27

If you have N columns in a table, every possible column combination is 2^N-1 (removing the empty set). For 10 columns that would mean 1023 indexes, for 20 columns we end up with a whopping 1048575 indexes. Most of the indexes will never be used but will have to be taken into consideration by the optimizer. It is possible that the optimizer will choose a sub-...


26

Matching indexed views is a relatively expensive operation*, so the optimizer tries other quick and easy transformations first. If those happen to produce a cheap plan (0.05 units in your case) optimization ends early. The bet is that continued optimization would consume more time than it saved. Remember the optimizer's primary goal is a 'good enough' plan ...


25

Why this index is not "covering" for this query: No good reason. That is a covering index for that query. Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku And as a workaround include the WhereColumn in the filtered index: CREATE NONCLUSTERED ...


21

The primary problems with GUIDs, especially non-sequential ones, are: Size of the key (16 bytes vs. 4 bytes for an INT): This means you're storing 4 times the amount of data in your key along with that additional space for any indexes if this is your clustered index. Index fragmentation: It is virtually impossible to keep a non-sequential GUID column ...


19

Playing a bit with pg_buffercache, I could get answers to some of your questions. This is quite obvious, but the results for (5) also show that answer is YES I am yet to set up a good example for this, for now it is more yes than no :) (See my edit below, the answer is NO.) Since the planner is who decides whether to use an index or not, we can say YES, it ...


19

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong? At the least it is incomplete and potentially misleading advice (I didn't bother to read the whole article). If you're going to read stuff on the Internet (including this), you should adjust your amount of trust according to how well you already know and trust the author, but always then ...


18

Typically indexes will be used by SQL Server if it deems it more expedient to use the index than to directly use the underlying table. It would seem likely the cost-based optimizer thinks it would be more expensive to actually use the index in question. You may see it use the index if instead of doing SELECT *, you simply SELECT T1Col1. When you SELECT * ...


18

Index A is better for this query. When all the conditions in the WHERE are equality checks except one that is using a range condition or IN operator on a column, then that last column should be last in the index, after all the columns that have an equality check. This allows the optimizer to use an index seek to the first row that matches the conditions and ...


17

In a search, I would like to get all the rows that exactly match the bit string. Use a B-Tree index, the default type. I don't see a case for a GIN index here. Up to 1000 bits result in up to 133 bytes (or slightly more) storage size on disk for a bit varying type. SELECT pg_column_size(repeat('1', 1000)::varbit) -- 133 Not that much. A plain B-Tree index ...


17

The activity of altering big tables are done in phases: Create a new table with required fields and indexes say in test DB (just structure) Dump the data from the existing table and load the same to the newly created table in test DB Now announce your downtime :) Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ...


16

It is legacy from SQL Server 2000 0 and 100 were different back then 100 meant "fill all pages including all b-tree index levels" 0 meant "leave some space at higher levels in the b-tree index" Since SQL Server 2005, both mean "fill all pages including all b-tree index levels" Quotes from BOL (My bold) SQL Server 2000: A fill factor value of 0 does ...


16

By default the PK is clustered and in most cases, this is fine. However, which question should be asked: should my PK be clustered? which column(s) will be the best key for my clustered index? PK and Clustered index are 2 differences things: PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by default (...


16

If you want good results from the query optimizer, it pays to be careful about data types. Your variables are typed as datetime2: DECLARE @OrderStartDate datetime2 = '27 feb 2016'; DECLARE @OrderEndDate datetime2 = '28 feb 2016'; But the column these are compared to is typed smalldatetime (as the sdtm prefix suggests!): [sdtmOrdCreated] SMALLDATETIME ...


16

I'm frequently involved in code reviews for the dev team, and I need to be able to give general guidelines for them to follow. The environment I'm currently involved in has 250 servers with 2500 databases. I've worked on systems with 30,000 databases. Guidelines for indexing should revolve around the naming convention, etc, not be "rules" for what columns ...


15

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO. I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation ...


15

As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right? Essentially, yes. The read operation (select) accesses the nonclustered index first, then the clustered index (lookup). The write operation (insert) accesses ...


15

This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch. More importantly, the rows in this view for any specific index are removed when that index is rebuilt (but not when it is reorganized). If you are performing regular index maintenance, it might be useful to look at the ...


15

If the subquery in that update consistently uses those two predicate values, a filtered index should help a lot. Something like this (which Erik Darling kindly provided as a comment): CREATE INDEX IX_ntID_ResponseWindow_Includes ON dbo.Notifications (ntID, ResponseWindow) INCLUDE (NotificationType, Status) WHERE (Status = 'Done' AND NotificationType = '...


14

In addition to what @Catcall already provided, and to add a small corrective: I also covered some basics in this closely related answer on SO recently. Answers so far seem to indicate you need to create indexes on primary keys, but that's not the case in PostgreSQL (partial exceptions apply). I quote the manual here: PostgreSQL automatically creates a ...


14

Generally yes. In lack of an index the access pattern has to inspect every row, just to see if is the one you need or not. The issue is not the table size, but concurrency. Depending on your isolation level, your scans may block behind uncommitted transaction just to wait for rows that ultimately are 'uninteresting' to be unlocked. Because your scan is ...


14

Very short version: Yes, sometimes. PostgreSQL can use bitmap index scans to combine multiple indexes. A predicate like WHERE a > 50 AND a < 50000 is a specialisation of the more general form: wHERE a > 50 and b < 50000 for a = b. PostgreSQL can use two indexes here, one for each part of the predicate, and then bitmap AND them. It doesn't ...


14

Index Hint You can use an index hint to do that: SELECT * FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index) WHERE u.Reputation = 2; The downsides are: Potentially changing a lot of code If you rename an index, this breaks If you change an index definition, it might not be the best index to use anymore Plan Guides You can also use a Plan Guide, ...


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