Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
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, ...
Shahid Thaika's user avatar
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 ...
Marcus's user avatar
  • 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 ...
Tolure's user avatar
  • 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 ...
Aaron Gonzalez's user avatar
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,.....
101001000100001's user avatar
-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 ...
AndysPythonStuff's user avatar
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 ...
Francis Ducharme's user avatar
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 |...
richie's user avatar
  • 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, ...
Sreenivas Kolanthayappan's user avatar
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' ...
VarunAgw's user avatar
  • 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: { ...
Bear Bile Farming is Torture's user avatar
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 ...
Fajela Tajkiya's user avatar
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 (...
Alexander Dmitriev's user avatar
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 ...
Keith's user avatar
  • 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 ...
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 ...
shiwanginio's user avatar
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 ...
Twisted Tea's user avatar
-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 ...
Hasan Can Saral's user avatar
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 ...
sujeet's user avatar
  • 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 ...
sujeet's user avatar
  • 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: (...
Akaisteph7's user avatar
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 ...
Nicole's user avatar
  • 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 ...
Charlie Clark's user avatar
-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 $...
Bear Bile Farming is Torture's user avatar
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 ...
Ajith Kumar's user avatar
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 ...
sujeet's user avatar
  • 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 ...
sujeet's user avatar
  • 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 ...
mjaggard's user avatar
  • 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 (...
Luatic's user avatar
  • 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) ); ...
Hasan Can Saral's user avatar
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 ...
nam's user avatar
  • 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 ...
lifeisajourney's user avatar
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 ...
Rio Fajar's user avatar
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,...
Wolf1777's user avatar
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`....
Marisa's user avatar
  • 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 ...
Eventeos's user avatar
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: ...
Brian's user avatar
  • 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 ...
Leon's user avatar
  • 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 `...
Niyaz's user avatar
  • 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: ...
Jeff's user avatar
  • 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 ...
Sidney's user avatar
  • 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 ...
Luck3R's user avatar
  • 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 ...
Clemens K's user avatar
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 ...
K.Banks's user avatar
  • 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 ...
lifeisajourney's user avatar
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 ...
Andrey Stukalin's user avatar
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 ...
John K. N.'s user avatar
  • 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 ...
Alexis Wilke's user avatar
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 ...
starting dba jr's user avatar
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) ...
Mark B.'s user avatar

1
2 3 4 5
18