Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
50 views

Optimise XOR search on int list

I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int). Another large table (TableB) contains the same ID column with some other ...
0
votes
1answer
191 views

Performance recommendations stuck on 'Validating'

I have been tasked with migrating a 'database from hell' to Azure SQL. It has some complicated and long-running queries (20 seconds on the S6 tier, >600 seconds on S4 tier) and I'd like to optimise ...
0
votes
1answer
78 views

Optimize query with multiple IN statements

So I have a large query which I have been trying to optimize as over time it has gotten fairly slow (~116k records taking a few seconds to get a result). The context is a social network feed where ...
1
vote
1answer
163 views

SQL Server Database Tuning Advisor - Temp index being dropped

Recently I ran a trace on our database so I could then plug it into Database Engine Tuning Advisor and see what queries needed to be improved and where there might be a real need for indexes. While ...
0
votes
0answers
48 views

Update stats as default or with a sample value

I have been reading many blogs on this and cannot come to a conclusion on update stats for VLDB;s On SQL server 2012Sp4 and 2014Sp2 we have databases over 15 TB with 4-5 tables covering most of the ...
14
votes
2answers
867 views

Computed Column Index Not Used

I want to have a fast lookup based on if two columns are equal. I tried to use a computed column with an index, but SQL Server doesn't seem to use it. If I just use a statically populated bit column ...
0
votes
1answer
97 views

Postgres index not being used

Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
0
votes
1answer
105 views

MySQL Finding index size for compound unique indexes

I'm using following query to get index and data sizes for tables in database. But unfortunately it doesn't show index size for compound unique indexes. How can i get index sizes for compound unique ...
1
vote
1answer
176 views

Tuning read-intensive queries - Oracle

Now this might be very vague but Im sure I can provide more info on them.. my query is that is it recommended to use tablespaces if different block size because I have a table in which number of reads ...
0
votes
1answer
33 views

Results in sys.dm_db_index_usage_stats

I have this query below that I use to clean up indexes that are not being used anymore to reduce runtime and overhead during index maintenance and during inserts. On some tables that I've ran this ...
0
votes
1answer
182 views

Query Tuning and Index Tuning - SQL Server

I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones. The table structures can be found in this SQL Fiddle The row sizes of ...
0
votes
1answer
84 views

MYSQL Multiple table optimization for advanced search

I have a users table where there is the basic information of each user: CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `account_type` varchar(10) NOT NULL, `email` varchar(...
3
votes
1answer
376 views

PostgreSQL is not using index with large data

Enviroment My PostgreSQL (9.2) schema looks like this: CREATE TABLE first ( id_first bigint NOT NULL, first_date timestamp without time zone NOT NULL, CONSTRAINT first_pkey PRIMARY KEY (...
0
votes
1answer
27 views

Does a unique clustered index give array-like lookup performance?

Suppose I have a MySQL table with a unique integer index id that is auto-incremented for each row. id | PlanetName ---+----------- 0 | Mercury 1 | Venus 2 | Neptune Suppose that I am primarily ...
-1
votes
1answer
26 views

Is there a usecase to create separate indices on the foreign keys of junction/associative table?

Imagine an associative/junction table junction_table_ with (pseudocode) columns like: id_of_table_x REFERENCES TABLE X id_of_table_y REFERENCES TABLE Y PRIMARY KEY (id_of_table_x, id_of_table_y) So ...
2
votes
1answer
157 views

How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on?

In the Books Online for Reorganize and Rebuild Indexes Microsoft mentions: Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation. What magnitude of number of rows ...
1
vote
2answers
30 views

mysql innodb index: better one big or many compact?

I've a question for an expert in how innodb indexes are structured (mysql 8.0.18) Say I have 4 varchar columns on a table with one billion rows: country, state, city, attraction I have queries ...
0
votes
1answer
24 views

Sentry One Plan Explorer History Tab

While I'm working on index tuning I would love the history bar to populate with each run so I can see the differences, I'm probably using the tool incorrectly. My current workflow is: Create New Plan ...
0
votes
1answer
66 views

Non-clustered index covering a clustered index

I have a table that has the following indexes: IXC clustered index that includes a single column : A asc. IX1 that includes two columns : A asc, B asc. In this case ICX seems like a duplicate index ...
0
votes
1answer
50 views

Should a filtered index theoretically perform faster in this example?

I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown". It also has columns called "...
1
vote
0answers
85 views

Oracle Context Index - finding unsynchronised records

Context indexes can be update with the procedure: Ctx_Ddl.Sync_Index (index_name); But what exactly is synchronised by this function? How does Oracle know what is unsynchronised (i.e. how does it ...
13
votes
6answers
10k views

Rebuild Very Large Primary Key Index

I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes. I'm able to rebuild all ...
0
votes
3answers
58 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
-1
votes
3answers
139 views

ID, UUID and “deleted_at” columns, How to avoid performance degradation?

Consider this table, but with thousands of records, on PostgreSQL: | id | uuid | color | ... | deleted_at | |----|---------|----------|-----|---------------------| | 1 | 4fc1... | red ...
4
votes
3answers
2k views

Extremely slow query on indexed column in Postgres

I'm getting a extremely slow query on an indexed column. Given the query SELECT * FROM orders WHERE shop_id = 3828 ORDER BY updated_at desc LIMIT 1 explain analyze returned: QUERY PLAN ----...
1
vote
1answer
191 views

Postgres: How can I force index usage for view with latest rates?

We import currency rates into DB: CREATE TABLE currency_rate ( id int8 NOT NULL, date date NOT NULL, currency varchar(3) NOT NULL, rate numeric(12,6) NOT NULL, CONSTRAINT ...
1
vote
4answers
2k views

Find and Remove Duplicate Indexes

I have more then 1000 tables in my database. On few tables I found duplicate Indexes. Now I want to find duplicate indexes in the database and remove them. Means, if a table have 2 or more indexes on ...
2
votes
1answer
150 views

Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

I'm getting the following error when I try to use the FORCESEEK hint with the query below: Msg 8622, Level 16, State 1, Line 96 Query processor could not produce a query plan because of the hints ...
0
votes
2answers
58 views

non clustered index coverage with INCLUDED columns

If I have two indexes: IDX_1 = (COL1), (COL2) DESC IDX_2 = (COL1) INCLUDE (COL3) will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE ...
1
vote
1answer
27 views

Impact of partition scan on large and small data

I have a table SampleTable which has a clustered index on Date column, and is also partitioned on Date column. However the Date column is of type DateTime. While querying from this table i.e. ...
0
votes
1answer
54 views

Can I drop an overlapping index if the larger index is unique

I have 2 nonclustered indexes: IX1 = [col1] IX2 = [col1],[col2] (unique). I want to drop the IX1 index as the optimizer can stop use IX2 for the first column and stop using when it has satisfied ...
1
vote
1answer
115 views

Understanding composite BTREE + GIN_TRGM_OPS index prioritization & odd lower() behavior

hoping someone can try to help me decrypt some index behavior. I'm working on enabling some simple contains type lookups on various user-data columns (~varchar < 255) and trying to understand the ...
1
vote
3answers
71 views

When possible, are clustered indexes that are declared as unique more performant than clustered indexes that aren't declared as unique?

When applicable, will using the UNIQUE constraint for a clustered index help improve performance in any way under any scenario?
0
votes
1answer
39 views

What makes for a good index in this scenario?

I have a pretty big temp table (~5 million rows) that I need to update a few fields on. Here's an example update statement: UPDATE T SET CarModel = CASE WHEN CarModel = '350z' AND ...
4
votes
2answers
2k views

Find unused indexes

I'm using the following query to find unused indexes: SELECT PSUI.indexrelid::regclass AS IndexName ,PSUI.relid::regclass AS TableName FROM pg_stat_user_indexes AS PSUI JOIN pg_index AS PI ON ...
2
votes
1answer
43 views

Postgres not using multicolumn index for queries on leftmost columns?

I'm working in Postgres 9.5.15 and have a table called project: id | integer | | not null | nextval('project_id_seq'::regclass) user_id ...
0
votes
1answer
33 views

Mysql - query optimization

I have a DB schema as such, SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='...
14
votes
0answers
883 views

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 ...
-1
votes
1answer
541 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 cores,...
18
votes
2answers
3k views

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 ...
-1
votes
1answer
254 views

postgresql reindex frequency and monitoring

What's up guys. I have a couple of tables where i inserting much of data (and almost never deleting). Periodically i see that insert gains ExclusiveLock on index of table. So i guess that sometimes ...
-1
votes
1answer
844 views

How can Mysql server auto re-index tables

I have a table which has very minimal data. However the index_length is very very huge, because of frequent DML operations on this table. Ex : +-----------------------+--------+---------+------------...
-1
votes
2answers
786 views

MySQL query optimization not using index

I'm running into an issue with my MySQL database and I would like to know if any of you could please shed some light. Background: I currently have entire schema configured with InnoDB. This database ...
1
vote
1answer
85 views

Indexing Strategy for the query performance

I have a question on indexing strategy as i am trying to do indexing for a database. I understand the basic indexing strategy which are: Clustered index on PrimaryKey Add Non Clustered index for ...
0
votes
1answer
233 views

Postgres text column too large for an index - searching for IP address, so I don't think text search will work

I have a Postgres database with a column that has a large amount of text in it. There are potentially multiple IP addresses in the text, as well as a lot of other text. I need to do a query WHERE ...
1
vote
1answer
93 views

Do I need to update an index include columns every time I add a new column

I work on a team where we have millions of records of data, but not 100s of millions anything crazy. We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
0
votes
1answer
71 views

Determine least number of columns required for unique (clustered) index

I'm in the situation where I am encouraged to tune an existing database and in the process either introduce or remove indexes as seem best fit. I was looking at a certain table and noticed that the ...
-3
votes
1answer
65 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
5
votes
3answers
265 views

Postgres sometimes uses inferior index for WHERE a IN (…) ORDER BY b LIMIT N

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations. The problem generally ...
94
votes
3answers
34k views

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? ...

1
2
3 4 5
14