Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
1 vote
1 answer
52 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 ...
Miro's user avatar
  • 113
-2 votes
0 answers
17 views

How can I calculate the block access required in Secondary multi level indexing [closed]

Table structure Given below are four relational schemas taken from the same University database. Student(Std Id, S_name, Address, Gender, Dept_Id, GPA) Department(Dept Id, D_name, Location) Enrol(...
LNTR's user avatar
  • 1
2 votes
2 answers
258 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 ...
olleicua's user avatar
  • 121
2 votes
1 answer
70 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 ...
Ivaylo's user avatar
  • 133
0 votes
0 answers
20 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 ...
Herr Derb's user avatar
  • 113
1 vote
1 answer
17 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 ...
Bart Jonk's user avatar
  • 305
0 votes
0 answers
21 views

Execution plan showing high hash match

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].[...
deanpillow's user avatar
0 votes
2 answers
36 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 ...
Kyle Chamberlin's user avatar
1 vote
0 answers
62 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 ...
Ali Mehdi's user avatar
1 vote
0 answers
28 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": { "...
Vinaya Nayak's user avatar
0 votes
1 answer
34 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 ...
Nguyen Nguyen's user avatar
0 votes
2 answers
67 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....
HardCode's user avatar
  • 276
0 votes
0 answers
47 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,...
Daulet's user avatar
  • 1
1 vote
1 answer
77 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,...
Claudio's user avatar
  • 11
0 votes
1 answer
57 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 ...
user3239193's user avatar
0 votes
2 answers
76 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, ...
Shahid Thaika's user avatar
2 votes
2 answers
691 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 ...
Marcus's user avatar
  • 59
0 votes
1 answer
49 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 ...
Tolure's user avatar
  • 111
0 votes
2 answers
287 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 ...
Aaron Gonzalez's user avatar
0 votes
0 answers
46 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,.....
101001000100001's user avatar
0 votes
0 answers
36 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 ...
AndysPythonStuff's user avatar
2 votes
2 answers
186 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 ...
Francis Ducharme's user avatar
1 vote
1 answer
221 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 |...
richie's user avatar
  • 113
0 votes
2 answers
40 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, ...
Sreenivas Kolanthayappan's user avatar
0 votes
2 answers
85 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' ...
user avatar
0 votes
2 answers
64 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: { ...
Bear Bile Farming is Torture's user avatar
4 votes
2 answers
1k 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 ...
Fajela Tajkiya's user avatar
0 votes
2 answers
56 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 (...
Alexander Dmitriev's user avatar
0 votes
2 answers
39 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 ...
Keith's user avatar
  • 185
1 vote
3 answers
127 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 ...
Vipin's user avatar
  • 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 ...
channa's user avatar
  • 350
0 votes
2 answers
70 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 ...
Twisted Tea's user avatar
-2 votes
2 answers
99 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 ...
Hasan Can Saral's user avatar
0 votes
1 answer
133 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 ...
sujeet's user avatar
  • 235
0 votes
1 answer
85 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 ...
sujeet's user avatar
  • 235
-2 votes
1 answer
76 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: (...
Akaisteph7's user avatar
0 votes
1 answer
232 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 ...
Nicole's user avatar
  • 1
0 votes
1 answer
80 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 ...
Charlie Clark's user avatar
-1 votes
2 answers
85 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 $...
Bear Bile Farming is Torture's user avatar
0 votes
3 answers
550 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 ...
Ajith Kumar's user avatar
0 votes
1 answer
572 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 ...
sujeet's user avatar
  • 235
2 votes
1 answer
697 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 ...
sujeet's user avatar
  • 235
0 votes
1 answer
173 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 ...
mjaggard's user avatar
  • 125
0 votes
4 answers
378 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 (...
Luatic's user avatar
  • 143
1 vote
1 answer
48 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) ); ...
Hasan Can Saral's user avatar
0 votes
1 answer
90 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 ...
nam's user avatar
  • 291
1 vote
1 answer
223 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 ...
lifeisajourney's user avatar
2 votes
2 answers
88 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 ...
Rio Fajar's user avatar
2 votes
1 answer
258 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,...
Wolf1777's user avatar
1 vote
2 answers
87 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`....
Marisa's user avatar
  • 23

1
2 3 4 5
19