Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
593 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 ...
23
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
114 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
43 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
133 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
373 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
83 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
223 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
213 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
20 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
58 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
111 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
109 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
416 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 ...
25
votes
1answer
18k views

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 ...
31
votes
3answers
14k 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?
12
votes
1answer
9k 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
80 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
27 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
1k 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
54 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
75 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 ...
2
votes
1answer
9k views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
1
vote
1answer
796 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
340 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
46 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
4k 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
544 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
339 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
489 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
198 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
203 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
63 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 ...
4
votes
2answers
800 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
36 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
vote
1answer
400 views

How to create some GIN index concurrently in Postgresql

I have a large table: CREATE TABLE hh(h int8[] not null, file int8 not null), and GIN-index over h field (CREATE INDEX ON hh USING gin(h)). Index was created is about 8 minutes, so I decide to split ...
2
votes
2answers
636 views

NonClustered Index not being Used

I have a table (created below) that contains 321 rows. I expect the last query below to use the NonClustered Index and then a Key Lookup. However, instead it uses a Clustered Index scan. Only a ...
0
votes
1answer
45 views

Best index for a join table?

I have two tables that are joined using a join table. I tried to found the best index(es). DROP TABLE IF EXISTS jobs CASCADE; DROP TABLE IF EXISTS devices CASCADE; DROP TABLE IF EXISTS jobs_devices ...
2
votes
1answer
54 views

EDW index question

I have been assigned the task of designing a dimension or fact table for an EDW. I know that a SQL Server table supports only one rowstore clustered index (conventional clustered index) and that you ...
2
votes
2answers
802 views

Can I use Database Engine Tuning Advisor for Azure?

I am trying to use "Database Engine Tuning Advisor" for queries in Azure SQL. According to https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-...
0
votes
1answer
43 views

What would be the most efficient indexes to make to speed up these 4 queries?

I have a few queries that I want to speed up, but the columns they filter on are all different, but related, and I'm having trouble figuring out the best way to make indexes for them. So the first ...
6
votes
1answer
1k views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
10
votes
2answers
939 views

Unused Indexes Best Practices

Based on this query, if I see a low amount of total reads (very close to 0 or 0, like 1 or 2) and a high or moderate amount of user updates (I couldn't find inserts or deletes with this query) with a ...
6
votes
2answers
272 views

Does an index with multiple columns make a similar index redundant?

Let's say I have a table that looks like this: CREATE TABLE Activity ( ActivityID int primary key identity(1,1) , ActivityName nvarchar(10), InactiveFlag bit ) with an index that looks ...
4
votes
1answer
460 views

Why are Op Stats all 0's and yet Usage Stats show 29 seeks

Why are Op Stats all 0's and yet Usage Stats show 29 seeks. This was returned from Brent Ozar's sp_blitzindex. Usage Stats Op Stats Reads: 29 (29 seek) Writes:14 0 ...
6
votes
3answers
853 views

Questionable duplicate index suggested by optimization

Good day, I have the following sql server database table: Please note the compound primary key. This was done for 3 reasons: Prevent duplicate entries Improve query performance as all queries will ...
4
votes
4answers
668 views

Why is there no predicate applied to the Index Scans?

We're having problems with some queries which are similar to this: SELECT COUNT('A') FROM [dbo].[OINV] T0 INNER JOIN [dbo].[OCRD] T2 ON T2.[CardCode] = T0.[CardCode] WHERE T0.[CardCode] = (@...
1
vote
1answer
87 views

Optimize view (and underlying table) for averaging timestamps into hours

I have this table: CREATE TABLE spp.rtprices ( "interval" timestamp without time zone NOT NULL, rtlmp numeric(12,6), rtmcc numeric(12,6), rtmcl numeric(12,6), node_id integer NOT NULL, ...
1
vote
1answer
899 views

Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL. The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...