Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [index-tuning]

The process of determining which indexes are useful and which are not.

The process of determining which indexes are useful and which are not. This may include removing indexes, adding indexes, re-arranging the column order of index, and changing which columns are referenced.

932 questions
132
votes
3 answers

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? Additionally, I created an index on A, but Postgres still…
Luciano
  • 1,771
  • 3
  • 12
  • 8
54
votes
1 answer

Indexes: integer vs string performance if the number of nodes is the same

I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is searching for very specific attributes on a model. I…
Chris Cirefice
  • 643
  • 1
  • 5
  • 7
53
votes
2 answers

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online arguing both that it does and that it doesn't.
Thor Erik
  • 645
  • 1
  • 5
  • 6
52
votes
3 answers

How do I know what indexes to create for a table?

Is there a way I can figure out the best way to know which indexes to create for a table?
Nick Ginanto
  • 1,009
  • 3
  • 10
  • 10
42
votes
2 answers

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one million rows Values in column c1 can have about 100…
jap1968
  • 721
  • 1
  • 6
  • 12
40
votes
1 answer

Should I use many single field indexes, instead of specific multi column indexes?

This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection". I'm working with an existing SQL Server (2008) application that has a number of performance and stability issues. The…
RaoulRubin
  • 979
  • 2
  • 10
  • 12
34
votes
1 answer

How does SQL Server determine key column order in missing index requests?

How does SQL Server determine the order of key columns in its missing index recommendations for a query plan?
Bryan Rebok
  • 1,219
  • 9
  • 16
29
votes
3 answers

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added index can make an SQL SELECT query faster but also…
Uwe Keim
  • 887
  • 2
  • 13
  • 24
27
votes
1 answer

Splitting Snapshot files with MaxBCPThreads for Transactional Replication

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm expecting it to split the files so the threads on the…
b1kjsh
  • 281
  • 2
  • 7
25
votes
3 answers

Why filtered index on IS NULL value is not used?

Assume we have a table definition like this: CREATE TABLE MyTab ( ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY ,GroupByColumn NVARCHAR(10) NOT NULL ,WhereColumn DATETIME NULL ) And a filtered non-clustered index like…
jericzech
  • 977
  • 8
  • 22
24
votes
4 answers

If a database only ever has one insert, is it bad to index every possible column combination?

I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is probably a better way to design a system like this,…
Lopsided
  • 365
  • 2
  • 7
20
votes
1 answer

How to index a query with `WHERE field IS NULL`?

I have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, setting uploaded_at to current date. How should I index the…
Kirill Zaitsev
  • 303
  • 1
  • 2
  • 7
20
votes
1 answer

What factors go into an Indexed View's Clustered Index being selected?

Briefly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this asked before, but the OP wasn't too well received.…
EBarr
  • 690
  • 6
  • 13
19
votes
3 answers

Adding index to large mysql tables

I have a table | base_schedule_line_items | CREATE TABLE base_schedule_line_items ( id int(10) unsigned NOT NULL AUTO_INCREMENT, installment int(10) unsigned NOT NULL, on_date date NOT NULL, actual_date date DEFAULT NULL, payment_type int(11) NOT…
user2294477
  • 353
  • 1
  • 2
  • 6
19
votes
2 answers

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk but may be cached. An index may be entirely in the…
davetapley
  • 953
  • 4
  • 9
  • 16
1
2 3
…
62 63