Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
690
questions
3
votes
0answers
41 views
Why is PostgreSQL not using composite Index on JSONB Fields?
Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example:
SELECT id from table where (j1->>'f1')::int = 1 AND (...
-6
votes
0answers
27 views
Is there yet an “auto index” feature in PostgreSQL? [closed]
I've struggled all my database life with indexes. Am I supposed to apply it on the ORDER BY column(s), on the ones being compared, a combination of them (and if so, what combination/order?), which ...
1
vote
0answers
20 views
Why does adding an index increase the execution time in SQLite?
I'll just show you an example. Here candidates is a table of 1000000 candidates from 1000 teams and their individual scores. We want a list of all teams and whether the total score of all candidates ...
0
votes
1answer
15 views
Why is this (postgres) composite gin(scalar, scalar, array gin__int_ops) index not using the array criteria?
I have a table
create table r_prot_search(
openprot_release_id integer,
annotation_group_sig text,
dataset_ids integer[]
);
With a composite index:
create index my_index on r_prot_search ...
1
vote
2answers
97 views
Why is index not being used? Can I replicate plan?
I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment.
Specifically, in the UAT environment the ...
0
votes
1answer
27 views
Receiving “Query processor could not produce a query plan because of the hints defined in this query” when using FORCESEEK but can use FORCESCAN?
I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total).
One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that ...
4
votes
1answer
123 views
Why is a GiST index used for filtering on non-leading column?
I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query....
0
votes
1answer
25 views
Unable to create partial indexes using bloom
I'm new to bloom indexes and currently my table has 3 partial indexes for the same column in the WHERE clause and with different column combinations for the same table. I was trying to replace them ...
1
vote
1answer
103 views
Index or query tuning for complex predicates
I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates:
JOIN on a date from one table being in range - between two date ...
2
votes
1answer
30 views
Indexing a date column in MySQL doesn't work if a date function is used
I have a table with the following schema
table_name
id int
task_id int
completed_date datetime (INDEX IX_TBL_NAME_COMPLETED_DATE)
I run EXPLAIN on this query
EXPLAIN
SELECT
*
FROM ...
0
votes
1answer
16 views
Create partial index with single value
I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-...
0
votes
0answers
37 views
MySql Group By Query Taking More 5 seconds On An Indexed Table
I have a simple analytics table which has a little over 2 Million rows and currently growing at a pace of 20k rows per day. Please check the table
CREATE TABLE IF NOT EXISTS `affiliate_analytics` (
`...
1
vote
1answer
27 views
Optimising query: not sure why LOWER() index isn't used
I've got comments that have 1..N comment_notifications.
Each notification basically contains a username of the user notified.
Each notification can be resolved_at when the user is done with it.
This ...
0
votes
1answer
34 views
Use both b-tree and BRIN indexes in PostgreSQL
I have rather large tables (> 1000 M rows) where I have to do quick lookups. They are typically indexed with composite indexes to enable index-only scans like this CREATE INDEX table (study, ...
1
vote
2answers
30 views
MYSQL Query Index Check
I have a table with the following structure;
CREATE TABLE `operations_copy` (
`id` bigint(22) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`zoneId` int(11) unsigned DEFAULT NULL,
`...
0
votes
2answers
39 views
Postgres 10.6: Why is a particular partition's index not being used?
I'm truly stumped as to what's causing this issue and hoping for some guidance.
Firstly, this is in our STAGE environment and we partition this table based on the createdTime every month. I have ...
3
votes
2answers
59 views
Why is creating and dropping a random index fixing this performance problem?
I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/...
1
vote
2answers
33 views
postgres includes listed column in sort key and thus fails to use index
This is a query that occurs reasonably frequently:
SELECT DISTINCT id, published_year, first_author
FROM publication
ORDER BY published_year DESC NULLS LAST
LIMIT 10;
There is an index that could ...
0
votes
0answers
57 views
SQL Server Full-Text Search Order by another Column Performance
I have two tables, Entity and EntityDetails.
Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime).
EntityDetails table has columns EntityId, Details1, Details2.
Where ...
0
votes
1answer
59 views
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.
Do you think that indexes going to reduce query run time?
Any ideas how ...
0
votes
1answer
95 views
Optimise primary key and index settings for multiple link tables
I have this tables stated as below.
orders is the main table that will store all the details such as the customerID, merchantID, which riderID it was assign. The order details like total, subTotal, ...
0
votes
1answer
47 views
Indexing a GROUP BY Select with Multiple Inequalities
I have a pair of linked issues I need help with:
Issue #1: How to Index a Table to Optimize for a "Group By" SELECT
I have a table of stock price information with the following Create statement:
...
0
votes
1answer
18 views
When using blitzindex should are the reads the number of times the index was used to read or is that the op stats?
Im using blitz index to identify some indexing issues. Im concerned that I am reading the output incorrectly. In my output, im seeing reads, compared to writes in the usage column, and then im ...
0
votes
1answer
24 views
query doesnt use column dedicated index after a certain amount of rows
I have the following table:
create schema test;
CREATE TABLE test.foo (
info_datetime timestamp NOT NULL,
asset_id int4 NOT NULL,
price float8 NULL,
CONSTRAINT minute_prices_pkey ...
1
vote
0answers
31 views
Which order should the columns in a composite key as a foreign key be specified?
Looking at the code from this answer which uses a composite key to control which child tables can contain the parent key, I noticed he specifies the primary key of the foreign table first, then the ...
0
votes
1answer
71 views
Speeding up “duplicate” checks
We have an events table that is reasonably large (~35 million rows)
The original table is like this,
CREATE TABLE [dbo].[Events](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[...
1
vote
2answers
60 views
Very large table makes the application performance too slow
We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.
I found one table referenced in this app with 1,332,730,...
3
votes
1answer
136 views
How can I improve the performance of this query on a table with millions of trigrams?
In an effort to make searching across many string fields in certain tables faster, I've been attempting to use trigrams.
I've created a separate table to hold them, and a query to search against them ...
0
votes
0answers
18 views
Would a set of non-clustered index work better than a clustered index for my large table?
I'm trying to create an index over a table with potentially 100 million rows. Right now I have the following columns:
name="date", type ="DATE"
name="account_id", type ="INT64"
name="group_id", type =...
0
votes
1answer
30 views
Does the order for clustered index have the same constraints as non-clustered index on search?
I have a table filled with a bunch of data, and I want to use the following clustered index:
[account_id, group_id, run_id, page_id, date]
For a non-clustered index, if I were to to use WHERE ...
1
vote
2answers
60 views
Retrieving data from PostgresQL database with millions of rows takes very long
I've been working on a system where users can register as an user, create a book club and invite other people (members) to join. The user and member can all add books to the club, and also vote for ...
0
votes
3answers
65 views
Why does SQL Server not use the Index it recommends?
The query
I had a specific query I wanted to optimize:
SELECT * /*12 columns*/
FROM [dbo].[EnterpriseGroup]
WHERE
(EnterpriseGroup.ChildId = 123 OR EnterpriseGroup.FatherId = 234)
AND StatusCd &...
0
votes
1answer
20 views
Performance of query from this schema
Essentially all comments are stored in this schema (dbml)
table comments {
commentID BINARY(16) [not null] //UUID
post_date timestamp
postID BINARY(16) [not null] //UUID
has_reply bool [...
0
votes
1answer
68 views
How does an index speed up insertion in a table
How does index speed up insertion in a table?
I have an idea how it happens for retrieving the rows and selection of data, but how does it helps in insertion?
CREATE TABLE persons(
id NUMBER ,
...
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
34 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
28 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
29 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
158 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
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 "...
0
votes
1answer
67 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 ...
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 ...
0
votes
3answers
59 views
Indexing only for most recent entries in PostgreSQL
Consider the following table:
|-----------------------------------------------------|
| raffle |
|----|---------|----------|-----|---------------------|
| ...
-1
votes
3answers
171 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 ...
1
vote
1answer
250 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 ...
2
votes
1answer
206 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.
...
