Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
2
votes
2answers
615 views

Why is this query with WHERE, ORDER BY and LIMIT so slow?

Given this table posts_lists: Table "public.posts_lists" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+-...
0
votes
1answer
76 views

PostgreSQL 9.5 query performance depends on JOINed column in SELECT clause

Data: Over the following two tables: Table "public.response_logs" Column | Type | Collation | Nullable | Default --------...
1
vote
1answer
153 views

How to index two tables for JOINed query optimisation

I'm using PostgreSQL 9.5 on High Sierra. Over the two tables: request_logs - ~ 26K rows response_logs - ~ 9K rows I've the following query (with JOIN): SELECT req.uuid, res.status, req....
5
votes
3answers
1k views

Why does this Index seek cause a lot more reads than index scan

As I've read that index seeks most of the time are preferred to index scans, I'm trying some stuff out. I have a query that does 993 reads (checked with SQL Profiler) when it's using an index scan. ...
-2
votes
1answer
61 views

Can rebuilding an index create fragmentation?

Its my understanding that when you rebuild an index SQL will sort the pages into order at the end of the data file, growing the data file until all pages are ordered and then drop the old index ...
0
votes
1answer
102 views

Query and index optimization for a table containing 10 million records [closed]

I was trying to retrieve data from a table A to retrieve data for 1 month, which has around 10 million records and 21 indexes built on it, It took me 3 min to retrieve around 20 columns and total of ...
5
votes
2answers
6k views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
1
vote
1answer
41 views

Index Management

I have two tables in MySQL 8, table A with primary key and table B with a foreign key of the same type on column 1. table B has an index on column 1. If I want to change the index on table/column B1, ...
0
votes
1answer
26 views

MongoDb use indexes partially

Can mongodb take advantage of an index partially? I'm not talking about using a "partial index" ( https://docs.mongodb.com/manual/core/index-partial/ ), but using a regular index partially. For ...
1
vote
1answer
1k views

Indexing strategy for VARCHAR2 LIKE search

Scenario Database Oracle Database 11g Table Users ID Number, PK CODE Number, Indexed, Unique, Not null FIRST_NAME VARCHAR(100), Not null LAST_NAME VARCHAR(100), Not null EMAIL VARCHAR(50), Not ...
3
votes
3answers
429 views

Does Index Rebuild Reduces physical read from disk

Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index ...
1
vote
2answers
162 views

Database Space Used Decreased after Index Rebuild (Ola Script)

I have rebuilt some indexes which were highly fragmented. After the index rebuild I am seeing my database space used reduced by almost 50% (from 77gb to 33 gb) Is that normal behavior? I dont have ...
3
votes
1answer
156 views

Indexes: Normal tables vs memory optimized

If two tables have the same schema but one is "normal" and the other is memory optimized, if I research on how indexes should be for the "normal", can I do a direct extrapolation for the memory ...
0
votes
1answer
74 views

SQL Server - Non-reindexed Database [closed]

I have a database that has not been reindexed in the last 5 years. I want a yes or no answer if databases without index maintenance and "Missing Rows" are related to each other.
0
votes
1answer
40 views

Indexes for select performance

I have a table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` enum('new','paid') NOT NULL DEFAULT 'new', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `...
2
votes
2answers
100 views

How to optimize a query for < operator

I have a SELECT that is very slow when using < operator I'm looking for fixes or workarounds to perform this operation: EXPLAIN (ANALYZE) SELECT * FROM "users" WHERE (engagement_level(social) &...
1
vote
1answer
707 views

Database table as a queue, order by another table's column

I have two tables. One table serves as the queue from which multiple users simultaneously dequeue items one at a time, by flagging the item's row. The other table is joined and used for ordering. It ...
24
votes
4answers
5k views

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 ...
2
votes
2answers
156 views

Combine indexes to optimize “OR” queries

I know that since version 8.1 Postgres can use multiple indexes for queries with or at query time. I wonder if it is possible to combine multiple columns into one index column at index time. That is ...
-2
votes
1answer
51 views

Index not hitting while querying [closed]

I have a query on a table tab and this table has a column a: select * from tab where a = :a; but the index is not hitting. Please let me know what could be possible reason (It was asked during an ...
1
vote
1answer
38 views

Are many two column indexes a bad smell?

I have table that contains failures. (15000 rows + on a abstract embedded system) A failure has the following fields: id PK BIGINT errorNumber INT source VARCHAR raisedAt BIGINT clearedAt BIGINT ...
1
vote
1answer
241 views

SQL Queries Slow in the Upgraded environment

I setup and configured an new SQL database server with SQL Server 2016 Standard Edition. This is going to replace our existing SQL database server which is 2008 R2 database server. I restored recent ...
2
votes
2answers
1k views

Optimizing a simple query joining two big tables

I don't understand why the following query is so slow (with Postgresql 9.6): SELECT s.pkid AS pkid_site , s.geom AS geom_site , z.pkid AS pkidEmprise, z.geom AS geom_emprise, z.précision_contour FROM ...
-2
votes
2answers
98 views

Add Argument to IndexOptimize.sql to Execute Custom Command After Each Index Is Processed [closed]

I need some way to check the space usage and backup the transaction log of the database before it gets full when we run your IndexOptimize.sql script. Our databases are mirrored, so I cannot change ...
2
votes
1answer
305 views

Query using a filtered index but wrong estimated number of rows

I have a table with a nonclustered index on a datetime2 field. On that same table i have a field (char[1]) which is used to logically delete records and can have 2 distinct values : A(active) or D(...
0
votes
2answers
308 views

Worse performance after create empty table

I'm working with MySQL 5.7.10 and I have this issue. I have this table to trace requests: CREATE TABLE `invoice_requests` ( `REQUEST_ID` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_spanish_ci',...
0
votes
1answer
25 views

Mysql - optimal indexing for query using IN operator

I have this query: SELECT * FROM table1 WHERE foo IN (1,2,3,4) AND bar = 123; I have 3 indices: ALTER TABLE table1 ADD INDEX IDX_foo (foo); ALTER TABLE table1 ADD INDEX IDX_bar (bar); ALTER TABLE ...
0
votes
2answers
73 views

Dealing with varying fill factors in a SQL Server 2014 environment

I've recently become a DBA of sorts for our company's ERP system. I've been making small changes to the server & improvements have been steady. One area I'm concerned with is the index fill factor....
0
votes
2answers
138 views

a filtered index to support a query - is it possible to consider nulls?

I have been dealing with this query: SELECT @amount = sum(accountAmount) FROM dbo.accountTransaction WHERE invoiceID = @invoiceID AND (notCountedInTotal = 0 OR notCountedInTotal IS NULL) ...
0
votes
3answers
133 views

what columns to include in the index to improve the performance of the following update?

I have the following update: UPDATE dbo.fees SET amount = @amount, currencyCode = @currencyCode, feeType = @feeType, countryCode = @countryCode, gatewayCode = @gatewayCode, ...
5
votes
3answers
768 views

Order of composite index

Suppose I have a query like: SELECT * FROM table_a JOIN table_b USING (id) WHERE table_b.column = 1 I have an index on id and an index on column but often I add a composite index with both that ...
35
votes
3answers
18k views

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?
15
votes
1answer
10k views

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

“Inner” Group By Query sequential scan

I have inner-query in left-join that is using sequential scan instead of index scan, and I have no idea on how to improve it. I have two tables: pages - columns: id shares - each page has many ...
0
votes
1answer
28 views

Is it a good idea to create an index just for new data to increase index creation performance?

I have a table with about 350 M rows with 45 GB, it has around 10 columns ,simplified version below CREATE TABLE PAYMENT ( ID NUMERIC(20) PRIMARY KEY, CREATION TIMESTAMP, VALUE DECIMAL(10,...
4
votes
1answer
2k views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
1
vote
0answers
60 views

What queries read my indexes and what queries apply inserts / updates to my indexes?

I'm looking indexes with 0 reads with SP_blitzindex, from there I am able to grab the indexes names that are probably not helping my system. Using the top query posted by Kendra: SELECT ...
2
votes
2answers
78 views

Why does the first query scan and the second seek?

Please could somebody explain why the first query below scans on every join to the REFERENCE_VALUES table but the second query seeks on the same joins? The first query does have other joins, but I ...
1
vote
1answer
1k views

Slow Calculated sort in MS SQL Server

I have a big query (Entity Framework generated one) and I'm using a Calculated Field. Performance has already improved by adjusting the Clustered Index to include the [On] (datetime) column. The sort ...
8
votes
3answers
355 views

Index tuning question

I am tuning some indexes and seeing some issues would like to take your advice On 1 table there are 3 indexes dbo.Address.IX_Address_ProfileId [1 KEY] ProfileId {int 4} Reads: 0 Writes:10,519 dbo....
1
vote
2answers
52 views

Does an index continue to be used after it is dropped (Database Polling investigation)

The folly of developers who use database polling is shown below. I have identified a query below which did not have an index in place on a particular column In addition to polling the database every ...
3
votes
1answer
8k views

Search for nested values in jsonb array with greater operator

Here is the table definition (simplified): CREATE TABLE documents ( document_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, data_block jsonb NULL ); Sample values: INSERT INTO documents ...
0
votes
1answer
929 views

postgres 9.6 - jsonb & indexing - poor performances

On a table (used by a django model) I'm using jsonb column data to store arbitrary data fetched from a webservice: abs=# \d data_importer_rawdata; Table "...
1
vote
1answer
370 views

Unnecessary Hash Match in Query

I've just migrated a database from SQLExpress (Entity, Database First) to Azure (Entity, Code First) and some of the indexes are missing because they werent migrated (on purpose, I think a lot of them ...
5
votes
2answers
761 views

How to speed up querying last values in a time series?

I have a time series table prices in a PostgreSQL 10 DB. Here is a simplified test case to illustrate the problem: CREATE TABLE prices ( currency text NOT NULL, side boolean NOT NULL, ...
2
votes
2answers
232 views

Index Maintenance on Indexed View using Ola Hallengren Script

I’m using Ola Hallengren's below script in my environment and it fails with the error: USE DBA EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’, @FragmentationLow = NULL, @...
10
votes
1answer
244 views

Optimize index on a 2,135,044,521 row table

I have an I/O problem with a large table. General stats The table has the following main characteristics: environment: Azure SQL Database (tier is P4 Premium (500 DTUs)) rows: 2,135,044,521 1,275 ...
0
votes
1answer
71 views

why is the Log File is 5 times the database overnight when deleting?

The log file alone is close to 500 GB while the database is 80 GB. This is not a production system so I tried to delete a table which had 780,000 records. this table has 5 individual indexes that ...
5
votes
2answers
1k views

SQL deadlock on nonclustered key caused by two INSERTs and a CHECK CONSTRAINT on the same table

Been struggling with deadlocking on a table during INSERTs. It's a multi-tenant database and Read Committed Snapshot Isolation (RCSI) is enabled. There is a CHECK CONSTRAINT upon INSERT to ensure ...
1
vote
1answer
42 views

Change an index order from ascending to descending

I have a table index in my postgres 9.6 db that I think would be more efficient if the table was sorted descending rather than ascending (as it currently is). The data that I want to test is not in ...

1
3 4
5
6 7
14