Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
921
questions
0
votes
1
answer
28
views
Given dm_db_missing_index_details output, what index(es) would support the following recommendations
FullyQualifiedObjectName
EqualityColumns
InEqualityColumns
IncludedColumns
table
[bool1], [bool2]
NULL
NULL
table
[bool1], [bool2]
NULL
[decimal1], [int1]
table
[bool1]
NULL
[int2], [int3]
table
[...
- 297
0
votes
0
answers
31
views
Are multi column indexes for JSONB columns better?
we are using PostgreSQL and we have a table we are using JSONB due to the unstructured nature of the data. We are integrating with a ton of different CRMs, so a part of the records data is pretty ...
0
votes
1
answer
44
views
Huge index size on table
I have a large index on a specific table, and I wonder if it's better to drop and recreate it or use the REINDEX command. The table's total size is 322GB, with an index size of 166GB and an actual ...
- 35
0
votes
1
answer
56
views
Can I optimize UPDATE statements using EXPLAIN?
I have a few UPDATE calls on my production system that are running slow and I've been instructed to try and optimize them. I'm reading into how to understand EXPLAIN with (ANALYZE, BUFFERS) as well to ...
- 103
1
vote
1
answer
76
views
When is it safe to drop redundant or duplicated indexes on AWS RDS MySQL?
To identify redundant and duplicate indexes while ignoring primary and foreign keys and checking their cardinality. What is the best practice for dropping indexes on AWS RDS MySQL databases in the AWS ...
1
vote
1
answer
60
views
Impact of Reordering Composite Index on PostgreSQL: Performance, Space, and Overhead Considerations
I am optimizing SQL query performance by reordering a composite index in PostgreSQL. I need to understand potential repercussions, including space usage, data saving overhead, and any other impacts of ...
- 13
1
vote
1
answer
60
views
MySQL bug -- MySql is choosing incorrect indexes
This is the last update to this very LONG post.
Problem Summary:
In a database containing a field for datetime and a field for milliseconds (and indexes for both), MySQL picks an index of milliseconds ...
- 111
2
votes
3
answers
87
views
Best way to test index changes outside of production
We have a massive MS SQL Server production environment with thousands of tables in dire need of index tuning & pruning. Any suggestions on the best ways to test proposed changes outside of ...
- 175
0
votes
1
answer
27
views
PostgreSQL - How to optimize slow Bitmap Heap Scan?
My app is calculating the SuperTrend indicator over the price of multiple cryptocurrencies. I have the following table and indices:
-- DDL generated by Postico 2.1.1
-- Not all database features are ...
- 23
0
votes
1
answer
103
views
AI Tools for Tuning SQL Server? [closed]
Are there any strong and accurate AI-based utilities out there that can help identify problems with and suggest better-tuned alternatives for deficient queries, and that can analyze and provide useful ...
- 157
3
votes
3
answers
344
views
Dropping a unique clustered index on identity colum
I'm currently working with a SQL Server database that came from an inherited system. The previous system used a lot of auto incrementing bigint identity fields, let's call the field "KeyID". ...
- 31
1
vote
1
answer
70
views
Index not being used for date predicate [duplicate]
I'm doing abit of self learning on indexes & performance in a SQL Server. I'm using stackoverflow's database as my sample DB for this activities.
For my testing, I'm using the Users table & ...
- 13
1
vote
0
answers
33
views
Very slow to work with table using PostgreSQL [closed]
I have a table labelled SALES. It has indexes like this list
"sales_pkey" PRIMARY KEY, btree (id)
"sales_order_date_idx" btree (order_date)
"sales_product_id_idx&...
- 111
1
vote
0
answers
81
views
Poor Postgres Query Performance with a Partitioned Table - pg_hint_plan does better
Simplified, I have a table with this layout:
CREATE TABLE IF NOT EXISTS public.scans
(
id bigint NOT NULL DEFAULT nextval('new_scans_id_seq'::regclass),
geo geometry(Point) NOT NULL,
...
- 11
0
votes
1
answer
45
views
Is expected that indexes on binary columns perform slower than those on bigint columns?
Running MariaDB 10.8.8
I've had to implement geo-targeting on my site. For this each night I pull the free IP-to-country dataset from ipinfo.io. I have a PHP script that then creates two tables, ...
- 265
0
votes
0
answers
14
views
MongoDB - optimizing compound index by using executionStats
I am visiting the MongoDB learn DBA course. In one of the labs, it was shown how to optimize the compound index by examining the explain plan.
The lab used the sample training dataset. Following query ...
- 166
1
vote
1
answer
138
views
Postgres BRIN index for time-series table
I have multiple time-series tables with Pkey on timestamp column.
I'd like to test performance improvement if I change the index to be BRIN instead of BTREE.
Yet, I'd also like to have a UNIQUE ...
- 113
2
votes
2
answers
331
views
Optimize query performance for a filter on col_a > col_b
I have a large table in a PostgreSQL database (~100 columns, millions of rows). My query has no joins but it does have several where clauses. All of the where clauses are covered by appropriate ...
- 123
2
votes
1
answer
138
views
Composite index with a range column in the middle
My understanding is that when designing a composite index a column, which will be used for range tests should be placed at the end of this index, because the usage of the columns in composite indexes ...
- 133
0
votes
0
answers
21
views
Index field with all same value. Is this bad?
I have the following index
create unique index open_issues_unique on issue (status, type, reference_id) where status = 'OPEN';
This means the value of the column status will always be OPEN.
Does this ...
- 113
1
vote
1
answer
47
views
Is there a way to check the conditions for using a BRIN index?
BRIN indexes work best (or only) on tables where the data is banded together in physical storage.
Is there a way to check this beforehand, apart from testing or considering the logic that creates the ...
- 317
1
vote
0
answers
32
views
Execution plan showing high hash match [closed]
i have a sql query running from database 1 that joins table company on to a view
SELECT DISTINCT RTRIM([K].[cmpCompanyCode]) AS [cmpCompanyCode]
FROM [dbo].[view] AS [K]
JOIN [database2].[dbo].[...
- 11
0
votes
2
answers
43
views
Need help identifying opportunities for improvement in table config and query design
I am working on a project with a fairly large data set. We require arbitrary aggregates on this dataset, these are generated at request time from a user. Here is a basic description of our current ...
1
vote
0
answers
109
views
Force index not working in Aurora MySQL 8
I have recently upgraded to Aurora MySQL engine version 8.0.mysql_aurora.3.05.2 from Aurora MySQL 5.7. I see huge performance issue after upgrading. Recently I found that I have queries whose indexes ...
- 11
1
vote
0
answers
32
views
what is the best way to create database index on json cloumn in postgress [closed]
I want to create a database index on json column on field customer_number which has the follwoing data..
{
"version": "3.0",
"order_header": {
"...
- 111
0
votes
1
answer
119
views
Is it possible to use both GIN index and pgvector index for querying in postgres?
I have a table like below in postgres:
create table posts (
id bigserial,
tags text[],
content text,
content_embedding vector(512)
);
create index on posts using GIN(tags);
-- from ...
0
votes
2
answers
166
views
Multi-Column Indexes with a Low Variance Column
Scenario
I have a middleware software product called Pro2SQL by Progress, copying data from a Progress database to an SQL Server real-time. Pro2SQL manages the SQL Server schema through the middleware....
- 288
0
votes
0
answers
63
views
Designing Tables for Regular Updates in PostgreSQL
I have a PostgreSQL table that contains information about transactions, currently totaling 50 million rows. The table has 16 columns, with 4 of them being indexed. Transactions are frequently inserted,...
- 1
1
vote
1
answer
107
views
Table filtered for multiple column: eav support table can be the best solution for search performance improvement?
I have a table with 10~15 columns (some varchar, some int, some double, and some date). Table can contain tens of millions of records.
User can filter from all columns using all filter operator (equal,...
- 11
0
votes
1
answer
69
views
mysql min with where clause optimization
I want to get min user_id for a tenant_id.
mysql engine - innodb
table - users(id, tenant_id, is_deleted, creation_date)
pk (id)
sk (tenant_id, creation_date)
query1 select min(id) from users where ...
0
votes
2
answers
96
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, ...
- 223
2
votes
2
answers
730
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 ...
- 69
0
votes
1
answer
55
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
2
answers
1k
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
59
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,.....
0
votes
0
answers
49
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
458
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
296
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
43
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
88
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' ...
user280563
0
votes
2
answers
95
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
2k
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,189
0
votes
2
answers
143
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
45
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
161
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
23
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 ...
- 362
0
votes
2
answers
123
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 ...
- 101
-2
votes
2
answers
118
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 ...
- 179
0
votes
1
answer
207
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 ...
- 247
0
votes
1
answer
102
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 ...
- 247