Questions tagged [index-tuning]

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

82
votes
3answers
28k 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? ...
28
votes
2answers
26k views

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

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
2
votes
2answers
641 views

Can Postgres use an index-only scan for this query with joined tables?

This is a follow-up to: Do covering indexes in PostgreSQL help JOIN columns? Consider the inverse of the schema in the other question where you filter in the joined-on table: CREATE TABLE ...
18
votes
1answer
2k views

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 ...
4
votes
3answers
5k views

Optimize a query on two big tables

I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
25
votes
2answers
17k views

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 ...
5
votes
2answers
1k views

Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?

In this query: select count(*) from largetable; a secondary index is chosen: mysql> explain select count(*) from largetable; +----+-------------+------------+-------+---------------+------+------...
24
votes
1answer
17k 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 ...
7
votes
1answer
2k views

2 B-tree indices OR 1 GiST index on tsrange — which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
6
votes
1answer
1k views

Inconsistent statistics on jsonb column with btree index

I noticed that the performance of a query involving a jsonb column varied significantly between VACUUM ANALYZE runs while testing it. I get completely different execution plans seemingly randomly ...
2
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 ...
4
votes
1answer
3k views

PostgreSQL not using index during count(*)

I have a COUNT(*) query in PostgreSQL that runs often, and looks like: SELECT COUNT(*) FROM customer WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); This query takes ...
1
vote
2answers
97 views

What strategy does MySQL optimizer use when choosing between two indexes?

In question: Optimize a query with two range conditions the OP wanted to know if there were a way to optimize his query. In an answer given the query were rewritten as a LEFT JOIN, and that was the ...
16
votes
2answers
7k views

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 ...
29
votes
1answer
3k views

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?
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 ...
5
votes
2answers
777 views

Howto Identify Clustered Index Candidates

I find myself in a situation where I have inherited the db and people complain about database performance. I identified about a dozend of tables not having a clustered index or a not carefully planned ...
5
votes
4answers
2k views

postgres: index to SELECT strings like '%foo%';

I have an expensive, periodic PostgreSQL 9.3 query of the form: SELECT * from mytable where name NOT LIKE '%foo%'; foo is actually a constant here that never changes. The query is expensive because ...
4
votes
3answers
281 views

Is there any reason for an index to contain the primary key?

I inherited a project using SQL Server 2005. Recently there were some performance problems and I started looking at the indexes, and found _dta_index_survey_25_135059617__K1_K10_K19: id ASC, ...
4
votes
2answers
1k views

Extremely slow query on indexed column

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

Performance impact of including result column in indexed columns

I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of: texelid ...
2
votes
1answer
1k views

How to make unique check without creating heavy index in postgres

I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and ...
42
votes
2answers
36k views

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 ...
30
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?
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 ...
6
votes
1answer
3k views

Use GIN to index bit strings

I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
4
votes
2answers
363 views

How can I eliminate this costly index seek operation from my query?

I have two tables in Sql Server 2008 that look something like this: Transaction_Details: Dataset, Department, Charge_ID, Transaction_Type, dos_month, post_month, amount ...
15
votes
3answers
1k views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT o....
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 ...
3
votes
2answers
3k views

Is there a way I can speed up queries against this particular view?

I have three tables in a PostgreSQL database that I'm querying via a view and some joins. CREATE TABLE network_info ( network CIDR NOT NULL, some_info TEXT NULL, ...
2
votes
3answers
143 views

Why does sql server prefer the nonclustered index over the clustered index?

I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However,...
11
votes
2answers
1k 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 ...
6
votes
2answers
786 views

Indexing a large static dataset

My current situation is this: I have 328 million lines of data in a flat csv file. Not optimal. I want to be able to query this data (I'll explain in more detail). Grep is getting tired. This data is ...
5
votes
1answer
1k views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? Motivation:...
4
votes
2answers
502 views

PostgreSQL filter/aggregate performance fluctuates depending on condition value

I'm experiencing a strange variation of filter performance depending on the value in WHERE condition with this query: SELECT MIN(revenue) AS min_revenue, MAX(revenue) AS max_revenue FROM ...
4
votes
1answer
5k views

'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?

I'm running PostgreSQL 9.3 on Mac OS X Yosemite. I try to create an unaccent lowercase trigram index. To achieve it I did this: mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; ...
3
votes
2answers
4k views

Table Valued Function Very Slow

I have a TVF that when called takes 10 secs to run. However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables. In the Execution ...
3
votes
1answer
836 views

Is staggering reindexing jobs a good strategy?

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes. ...
3
votes
1answer
182 views

Does updating a partially indexed field add or remove it from an index in Postgres

We can create a partial index for a field in Postgres. I am looking at using this to stop rows which are archived from being in an index on a created_at field: First we create the index: test=# ...
3
votes
1answer
782 views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows,...
3
votes
1answer
6k views

Ola Hallengren Index Optimise Script not Rebuilding Indexes [closed]

I have downloaded Ola Hallengren's script and deployed to master database. I run it using the below... EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @...
3
votes
1answer
2k views

Using ILIKE with unaccent and with only right end wildcard

I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with ...
2
votes
1answer
237 views

How to improve this queries performance by indexing properly?

Hi here i have a query and i need to improve the performance of this query.Can anyone suggest me like how to convert the clustered index scan to index seek. DECLARE @now DateTime; DECLARE @...
10
votes
1answer
655 views

Poor query performance

We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do ...
9
votes
1answer
2k views

Is there any reason to use index for very small tables (up to 1000 rows)?

During application development I have a lot of tables that store "small" amount of data (usually 10-40 values, id + value and sometimes type) which hold attributes for "objects", like fresh/rotten, ...
7
votes
1answer
5k views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ----------------+-----------------------------+-----------------------...
6
votes
1answer
10k views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
5
votes
2answers
5k views

Can I trust the Database Engine Tuning Advisor recommendations?

Our database has become large, and we are trying to optimize performance. Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
4
votes
2answers
400 views

Compound Index on 43 Million PostgreSQL table

This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order) Rather than overload that question, I figure I can sharpen and limit my ...