Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
906
questions
1
vote
1
answer
1k
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
46
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
593
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 ...
- 347
-1
votes
2
answers
222
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
112
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
8
votes
4
answers
2k
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 ...
- 183
0
votes
2
answers
47
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
189
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
347
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
635
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 ...
- 505
2
votes
1
answer
140
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.7k
0
votes
1
answer
115
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
1k
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
528
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
0
votes
1
answer
173
views
can date datatype varchar use index as date datatype datetime index?
I have an existing table that have a column date but varchar datatype and also a date datetime datatype
example:
tbl_name: trx1
columns: trx_id, activity, name, date(datetime)
index: date_of_birth
...
- 25
2
votes
1
answer
65
views
MariaDB counting matching rows in table ~50m rows
Running MariaDB 10.9.4 on Debian 11, I have a table of flights with approximately 50 million rows
My structure looks like:
id int(11) auto increment
flight_number varchar(16)
aircraft char(6)
...
- 41
3
votes
0
answers
100
views
Optimizing queries using indexes
I'm doing an exercise, where we have to improve several queries:
(30%) Give me FH and FC per month, filtered by the aircraft model (e.g., "777").
(30%) Give me ADOSS, ADOSU per year, ...
- 131
2
votes
2
answers
114
views
To INCLUDE or Not INCLUDE in an Index
Current Situation
I am observing a simple statement that is querying one table and accessing multiple indexes to retrieve the data:
SELECT DISTINCT
feld16,
zahl4,
feld12,
feld19 FROM
object1 WHERE
(...
- 17.7k
3
votes
1
answer
154
views
Create Index for specific value in mysql column
I have a column in my table with TinyInt type, which stores millions of numbers ranging from 0 to 50.
However a great proportion of them have the exact value of 50.
Now I need to set an index to ...
1
vote
1
answer
797
views
Optimizing indexes for large table when ordering and filtering with different columns
I have a table with approximately 20 million rows and ~20 columns, and I'm struggling to index it effectively for a query that filters by one column (updated_at) and orders by a second column (...
- 131
5
votes
1
answer
383
views
How to optimize PostgreSQL OR query on two indexed columns
I have a large partitioned table that stores monetary transactions between accounts.
CREATE TABLE "transactions" (
"from" BYTEA NOT NULL -- sender account
,"to&...
- 147
2
votes
1
answer
143
views
PostgreSQL imbalanced index query optimization
I am having performance issues with my query because the index is heavily imbalanced. I have a column family_id on a table. The table has about 150 million records. This family_id column identifies ...
- 121
1
vote
3
answers
433
views
What is best practice on creating an index?
what is the best practice on creating an index?
example 1:
createdd_at_indx (created_at)
updated_at_indx (updated_at)
OR
example 2:
date_indx (created_at, updated_at)
- 25
0
votes
1
answer
62
views
Table design for 1:N relations with ordering and filtering on both sides of the relation in mysql
we have some tables with a lot of records (millions) and try to keep everything up to speed, but on the other hand we are setting our application up in new countries and keep some data per country. At ...
- 1
0
votes
2
answers
3k
views
How to speed up single-column date range query in Postgres?
How would you speed up a Postgres query that's trying to filter on a date column between a start and end date?
I'm running a query like:
SELECT * FROM record WHERE tag_id IN (1,2,3) AND person_id = 1 ...
- 1,355
0
votes
3
answers
2k
views
How to split a large table in SQL Server
We have a table that consists of 150 million records and the data goes back all the way to 2010. I'm considering splitting the table into two to improve performance - a history table with data prior ...
- 87
0
votes
0
answers
76
views
Query optimisation postgres
I have one partitioned table and I have some performance issues.
Here is DB fiddle with schema and query: https://www.db-fiddle.com/f/u3YABXJXUyAbkx54M3Fnjf/0
Some comments about explain this select.
...
- 142
0
votes
1
answer
57
views
How to optimize index if expected result is limited to 50k?
I have a collection where we are checking the recently active 50k users.
The collection has around 1m data.
And the query that we are using is this:
db.getCollection("UserLastActive").find({ ...
- 139
1
vote
1
answer
563
views
Query performance tuning on partitioned table
I have a simple table partitioned by Date for each day
so ranges are like ('2023-03-07 23:59:59.997', '2023-03-08 23:59:59.997') etc
with about few millins records per each range
the table is as ...
- 13
1
vote
1
answer
94
views
Creating a non-clustered index on clustering key with some columns included
I have 3 tables:
test_productInfo: contains information about the product
test_productCreator: contains all the users in our system
test_productOwner: contains information about who is the owner of a ...
- 505
6
votes
2
answers
467
views
Seek predicates with inequalities - Residual Predicate
Could someone please guide me to a better understanding of index seeks with multiple inequality predicates.
Consider the following table:
CREATE TABLE [dbo].[table_1](
[DBINVHDID] [int] IDENTITY(1,1) ...
- 329
0
votes
1
answer
77
views
Can a Clustered Index based on a key field be more performant than a non-clustered covering index for a big table
I am researching a DW reporting table that will grow very large. For simplicity, I will show the table as follows:
BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL
...
- 505
5
votes
2
answers
1k
views
Is the IN() condition always an equality search?
I am trying to understand if the IN() condition is an equality or inequality search when it comes to indexing.
I have been looking on the internet for an answer and have found responses ranging from, ...
- 153
9
votes
3
answers
2k
views
Is there a performance penalty when there are two unique indexes on a table with all of the colums in one index are also in the other index?
In my database I have a table with two indexes. There is one unique index #1 on columns a,b which I need for data integrity reasons. I have another index #2 with columns c,a,b that is used for ...
- 225
0
votes
2
answers
96
views
How to optimize SQL Server query that might be doing parameter sniffing
This simplified query on SQL 2017 takes 40+ sec to complete, I suspect its parameter sniffing issue, but not 100% sure.
exec sp_executesql N'
SELECT
T.[TicketRecId]
, T.[Title]
FROM dbo.Ticket ...
- 574
1
vote
0
answers
55
views
Wrong query plan for GIN index
I have the following repeatable case.
I have an uuid[] column with a gin index, which stores all unqiue uuids, without doubles and repeats.
Test table:
CREATE TABLE test
(
id serial not null, ...
- 11
0
votes
1
answer
62
views
Creating an index or not creating an index
I have a SQLite database and a table, which can grow up to 100.000+ rows.
I need to query data in the table really (!) fast. There are two ways to query the data.
I have an index of type string, ...
- 177
3
votes
2
answers
204
views
Can a redundant index improve SELECT performance?
Is it possible that adding a redundant index improves the performance of a query? I encountered a case like that several days ago and since then I've been scratching my head: how?
The MariaDB 10.4 ...
- 153
5
votes
1
answer
1k
views
Index Design: clustered index using a DATE
Every once in a while, one needs a check. This is such a time.
I have tables that are used for reporting. They are deleted/inserted once a day or multiple times a day to "refresh the data" ...
- 177
1
vote
1
answer
152
views
Indexes to help optimize queries on nested sets in InnoDB?
I represent trees in MySql using nested sets.
There are several different "types" of tree and every user can have one tree of each type.
The table looks something like this:
CREATE TABLE ...
- 139
1
vote
2
answers
265
views
TSQL Slow Query, not using index as expected
I have a wide table, relatively large, 14,264,775 rows, running on Azure SQL database.
The following query is in need of some TLC.
IF EXISTS (
SELECT 1/0
FROM dbo.table1 src
INNER JOIN ...
- 431
0
votes
3
answers
2k
views
Check index fragmentation without using analyze index command?
I've researched and a lot of scripts need to use analyze index command first. Is there some other way to check index fragmentation without populate those columns in index_stats table? some alternative ...
- 713
14
votes
8
answers
6k
views
Order By causes a scan on a large table
I have the following query;
SELECT TOP 100 ID
FROM [dbo].[TableName] WITH (NOLOCK)
WHERE TypeId = 2
AND DateTimeUTC < '2022-Aug-04 07:02:40'
AND DateTimeUTC > '4/26/2022 7:36:36 AM'
...
- 191
0
votes
1
answer
536
views
Can MySQL use a where clause to inform a join?
For context, I'm using an ORM (ActiveRecord for Ruby On Rails), but having to dip down into MySQL to run some rather complex queries. As a result, it's more difficult to do some things than it would ...
- 103
0
votes
0
answers
1k
views
Filtering timestamp record by range
So i am new at optimizing SQL and here is my query:
COUNT (SI_2_STRIPPED.RESOURCE_UUID) AS COMPLETIONS
FROM
(SELECT SI2.STATISTIC_ID,
SS.RESOURCE_UUID
FROM ...
- 101
3
votes
2
answers
617
views
Is a clustered index of 3 columns too big?
My goal is to design a table, that can be queried trough an external id(uniqueidentifier), an internal id(bigint), always in combination with companyId(bigint), userId(bigint) and dashboardId(bigint) ...
- 133
2
votes
1
answer
10k
views
Recommended way to index a timestamp column in postgres?
I have a PostgreSQL table called tickets_details, which has many columns and is constantly updated only on the rows of the current day, it also inserts thousands of rows of the current day that have ...
2
votes
2
answers
444
views
Optimal Indexing Strategy for Datawarehouse and Data Lake Updates
We have a sql server database we use as a data lake and a datawarehouse. Each table in the database has some standardized definition as we are at 600 or so tables now, so maintenance needs to be ...
- 158
0
votes
1
answer
1k
views
Effect of deleting index while table is being use in postgresql
What is the effect of removing or deleting a index while the table is being use?
sample:
from
table: order_tbl
index: order_id_index(not btree)
to
need to change it to
index:order_id_index(btree)
...
- 25