Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
893
questions
0
votes
2
answers
28
views
MySQL 8 query doesn't prefer index with higher cardinality
I have a query similar to the following:
FROM example_table
WHERE
`date` BETWEEN '2023-11-26' AND '2023-11-28'
AND location_id IN (3, 4, 6, 7, 8, 10, 11, 12, 14, 18, 19, 22, 23, 24, 28, 29, ...
- 211
2
votes
2
answers
590
views
Why are these columns specified in the include clause of an index rather than as key columns
For the below query, I'm trying to figure out why the index suggested by SQL Server on the sysjobhistory table, which is also the index that results in a seek, was created on the Job_Id column with ...
- 47
0
votes
1
answer
44
views
What are good metrics for determining the usefullness/impact of an index in a SQL server database?
Background
I'm reviewing a database currently where someone applied indexes to almost everything in the database. Since we are now experiencing performance issues related to updating records, I'm ...
- 111
0
votes
1
answer
34
views
What is the difference between CREATE INDEX and CREATE INDEX CONCURRENTLY in PostgreSQL?
I would like to know what the difference is between CREATE INDEX and CREATE INDEX CONCURRENTLY (if any) after the creation of the index has built. I have been having a discussion with a colleague ...
0
votes
0
answers
42
views
Complex day-to-day difference of historical data in SQL server
Setting:
I have a table (let's call it "HistoricalData") with 10 attribute columns, a date (in string format) and a value. The table has a unique clustered index on (date, idcol1, idcol2,.....
-1
votes
0
answers
24
views
Best way to set up tables for EOD stock database
Im a newbie with postgre, and the course I did focused on querying already existing databases, not creating a database from scratch.
Im aware that using csv tables might be the best way to go for ...
2
votes
2
answers
63
views
Where to get the error/info logs of what happens when committing several statements?
I had a problem with an index missing on a table and the only way I had to gather clues was this way:
BEGIN TRANSACTION;
-- OVER 100 DELETES
COMMIT;
In DBeaver, I selected only the first line and the ...
- 123
1
vote
1
answer
132
views
Can PostgreSQL use indexes of both columns in a query condition and order by clause in a single query?
I'm running PostgreSQL 11 with shared_buffers set to 3 GB on my Mac. I have a table job with 5 million rows. The table structure is
Table "public.job"
Column |...
- 113
0
votes
2
answers
35
views
MySQL is not utilizing the index when the IN operator is used
I have a mysql 8 query like below. the ch_site_user table is indexed on user_id, site_id, status.
Explain plan shows the correct index is used. But usersite.site_id IN (2315, 2316, 2324, 2323, 2320, ...
0
votes
2
answers
81
views
How to choose index to optimize this MySQL query?
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' AND symbol = 'ZROZ' AND blacklisted = 0
ORDER BY high DESC LIMIT 1;
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' ...
- 101
0
votes
2
answers
38
views
The effectiveness of compound index where the prefix is continuous / high cardinality
schema:
{
time_utc: "milliseconds",
city: "string",
age: "integer"
}
index:
{
time_utc: 1,
city: 1,
age: 1
}
sample query:
col.aggregate([
{
$match: { ...
4
votes
2
answers
922
views
Balancing Indexing and Database Performance: How Many Indexes Are Too Many?
I'm part of the database team at my company, and I'm currently facing a dilemma regarding query optimization and performance. Whenever some of my colleagues come across a slow query, their default ...
- 1,149
0
votes
2
answers
40
views
Full-text search performance is input-dependent and is extremely slow
I am trying to speedup a fulltext search query. The testing table has 2 million rows, their values are md5 strings. I have created several indices to see, what performs best, but the planner chooses (...
0
votes
2
answers
33
views
What is the correct index on a large dataset with group by?
I've been managing this very large data set for mlb data in mysql for awhile and am trying to rework the indexes to increase efficiency.
I have several instances I need to manage where I need to sort ...
- 185
1
vote
3
answers
100
views
SQL Server Query Performance: Index Seek in SSMS but Index Scan in Java Application
I'm facing an issue with SQL Server query performance and I'm hoping to get some insights from you all.
Problem Statement:
I have a SQL query that performs well (~6 ms) when executed directly in SQL ...
- 113
0
votes
0
answers
17
views
Mysql system tables showing incorrect cardinality for columns
I’m new to mysql. I have encountered a weird problem on our prod database. We have a table with primary key a and an index on some other b column. Below’s the query:
select b from my_table where a ...
- 348
0
votes
2
answers
58
views
Is a Composite UNIQUE Constraint On an Already UNIQUE Column Redundant?
I'd just like to clarify something pretty simple regarding single and composite UNIQUE constraints that I can't find a clear answer to.
Assume I have the following table:
CREATE TABLE person (
id ...
-2
votes
2
answers
88
views
Which databases implement a difference between index scan and index seek?
Let's say I have a table:
CREATE TABLE transactions (
id NUMERIC(20, 0) PRIMARY_KEY,
account_id VARCHAR(512),
...
transaction_date TIMESTAMP
);
So with SQL Server, an index:
CREATE ...
- 169
0
votes
1
answer
53
views
Optimizing PostgreSQL Query Using Composite Index and GIN Index
I have a PostgreSQL query that involves multiple conditions and joins. While I've created a composite index to speed up some of the filtering, I'm still experiencing extra rows being fetched due to ...
- 213
0
votes
1
answer
61
views
Optimizing PostgreSQL Query with DISTINCT ON, Filtering, and Ordering
I'm working on optimizing a query for a PostgreSQL database used in an educational platform.
The query retrieves distinct question_id values along with their is_seen status from the ...
- 213
-2
votes
1
answer
65
views
What considerations should be made when deciding on indexes for PostgreSQL databases?
My understanding is that you want to decide on what to include in your index based what will trigger the index being used.
The following considerations are what I am aware of, in order of relevance:
(...
- 97
0
votes
1
answer
74
views
Same query but different execution plans for postgresql query
I am struggling with a query running for 30 seconds on one of my db hosts.
Same query ( same configuration for db in postgresql.conf ) runs in 3 seconds with a different execution plan.
Yes the ...
- 1
0
votes
1
answer
47
views
Performance of (functional) indices for datetimes and window functions in Postgres
I think this is a fairly standard type of task but I haven't seen anything covering performance in detail, though Index Optimization for Datetime comparing day of week and hour is pretty close. From ...
- 251
-1
votes
2
answers
63
views
How to index when the fields used in the query is unpredictable?
In collection A, each document contains up to 100 fields. The database, mongoDB, only allows up to 64 indexes on a single collection.
Indexes are needed for a query on this collection. The filter, or $...
0
votes
3
answers
196
views
Mariadb querying is slow in a table of 2.6M records with indexes
TLDR: I have a table with a lot of records and my queries are slow. I have indexes, but still the query takes a long time to execute. Need help figuring out what the problem is, or atleast a pointer ...
- 11
0
votes
1
answer
206
views
PostgreSQL Partitioning and Indexing - Optimal WHERE Clause for Partition Pruning
I have a PostgreSQL table named orders, which I have partitioned based on the user_idcolumn using LIST partitioning using,
PARITION BY LIST lower(right(user_id, 2))
The table has columns customer_id ...
- 213
2
votes
1
answer
332
views
Optimal order for creating a composite index in PostgreSQL with multiple conditions
I have a table with three columns: user_id, customer_id, and order_id.
In my queries, I frequently filter the data using conditions like,
... WHERE user_id = 23434 AND customer_id = 234234 AND ...
- 213
0
votes
1
answer
78
views
How can I tell which query uses an index in MySQL?
I have investigated my indexes in MySQL using performance_schema.table_io_waits_summary_by_index_usage which has told me that one of my indexes is used in one database environment (Production EU) but ...
- 113
0
votes
4
answers
251
views
Indexes for speeding up find by rank / counting
Consider a table with a single id which has a PRIMARY KEY index:
CREATE TABLE test (id INT PRIMARY KEY)
Now consider queries like
SELECT COUNT(*) FROM test
SELECT * FROM test ORDER BY id LIMIT ?, 1
(...
- 143
1
vote
1
answer
41
views
JOIN with a small table causes execution plan to change and query to slow dramatically
I have the following tables:
CREATE TABLE acl_sid (
id bigserial not null primary key,
principal boolean not null,
sid varchar(100) not null,
constraint unique_uk_1 unique(sid,principal) );
...
- 169
0
votes
1
answer
74
views
Do I need a clustered index on a table with only few columns?
Table:
Address (
AddressID bigint,
Street VARCHAR(150),
City VARCHAR(200),
State CHAR(2),
State VARCHAR(5)
)
Table size: 5GB, 60 million rows.
AdressID is sequential.
Remark: 95-98% of ...
- 289
1
vote
1
answer
143
views
Index Seek Results in More Logical Reads in SQL Server
I have the following table and data in it. I have two indexes on it: One that contains only a few columns from the where clause and the other one contains all the columns from the where clause.
I'm ...
- 437
2
votes
2
answers
84
views
Simple query with two WHERE clauses uses inferior index scan
I have a table named "db_log" with a size of 11GB and approximately 10 million rows in PostgreSQL 13.7 version. The issue is that a simple query is not performing an full index scan for ...
- 31
2
votes
1
answer
208
views
Mariadb: Can I increase performance by having multiple column indexes with different orders?
In my Mariadb table I have two id-columns (A,B), which combined are unique, but not individually. This table is quite large. To speed up queries like
SELECT B WHERE A=?
I have added a primary key (A,...
- 23
1
vote
2
answers
75
views
MySQL - Create a Covering Index where the Sorting Option Columns are Identical to the Select Payload Columns
Something to note up front, I am using Magento 2 so if the query looks a little strange, that's why.
I have the following query and am trying to optimize the indexes for the table;
SELECT `main_table`....
- 23
1
vote
1
answer
605
views
Drop unique index of a unique constraint
I'm currently working on a postgresql 15 database.
I've created a data table named "measures" with following schema
CREATE TABLE measures (
timestamp TIMESTAMPTZ NOT NULL,
value double ...
- 31
0
votes
1
answer
43
views
Index consolidation of two similar indexes
We have a primary table that drives most of the work within our product with many columns.
Index 1 has a key consisting of: Column A, Column B, Column C, Column D
Index 2 has a key consisting of: ...
- 3
0
votes
1
answer
387
views
How to efficiently get absolute value of a time interval in Postgresql?
I have a huge table in Postgresql-11 like following:
CREATE TABLE my_huge_table(
tick_time timestamp(6) with time zone NOT NULL,
brok_time timestamp(6) with time zone,
trade_day date NOT ...
- 305
-1
votes
2
answers
102
views
Are there any differences relating to query performance between >= and <= vs. Between clause?
Are there any performance-related differences between those two queries?
SELECT * FROM `v1` WHERE `siteId`=1008 AND `date`>='2022-10-01' and `date`<='2023-02-01';
SELECT * FROM `v1` WHERE `...
- 218
1
vote
0
answers
82
views
Seeking clarification and recommendations on unexpected patterns in Postgresql index usage statistics
I am trying to prune unused and rarely used indexes based on the following stats:
pg_stat_user_indexes.idx_scan: Number of index scans initiated on this index
pg_stat_user_indexes.idx_tup_read: ...
- 111
9
votes
4
answers
1k
views
Does creating an index and then deleting it return the database to the pre-index creation state?
I'm testing some indexes and want to do a bit of A-B testing on which indexes perform better (primarily the order of the fields in the index and additional fields specified).
I have a backup of the ...
- 193
0
votes
2
answers
41
views
Indexing for multiple ORDER BY in MySQL 8
I feel like I have optimized this query to the best of my ability, but it is still slow (usually takes 0.5s - 2s in development).
It seems like the ORDER BY is the culprit, as when I get rid of it, it ...
- 3
1
vote
1
answer
155
views
Time based incremental load query optimization and index design
Problem
I try to optimize an extract/load query on a highly normalized table structure. Extract/load is implementing incremental loading using a datetime column to determine only records which have ...
- 11
2
votes
0
answers
296
views
Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?
I'm currently using Postgres 12.
A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
- 21
0
votes
1
answer
280
views
Modify an existing non clustered index or create a new index in SQL Server 2012
I have a table with 150 million records and 8 indexes. One of the indexes is on userId. A lot of the current queries I have filter on userId and therefore this index works perfectly. For one of the ...
- 437
2
votes
1
answer
118
views
Possible overhead of adding columns in a table without index in SQL Server
My question somehow relates to this one Performance considerations when adding a new column to a big table in Sql Server, but i still think there are a couple of differences so i'll ask anyways.
We've ...
- 121
10
votes
1
answer
1k
views
SELECT Statement with , * in column list is faster than same statement without *
Situation
When querying a database with a SELECT statement with a defined set of columns, the results are received in around 21 seconds.
If there is an additional asterisk (, *) at the end of the list ...
- 17.4k
0
votes
1
answer
84
views
How "trustworthy" is the cost information returned by EXPLAIN in Postgres?
I have a database with a table which is accessed with about 5 different SELECT commands. I tested each one of them with the EXPLAIN ... and EXPLAIN ANALYZE .... The database includes some data (about ...
- 125
0
votes
1
answer
331
views
Postgresql perform sequential scan instead of index scan for indexed column
I'm currently using PostgreSQL 14.0 for windows
My target table has a 900k+ rows and my query is using single column filter (status_id).
The column status_id is indexed via btree (default)
When i ...
0
votes
1
answer
343
views
When to use Clustered Index on Foreign Key vs Primary Key?
I am trying to work out where is the best place to put clustered indices on child tables.
I have a SQL Server database that looks like the following:
Journal
Column
Type
Id
bigint identity(1,1)
...
- 3