Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
936 questions
0
votes
0
answers
45
views
Advice for managing indexes in a large single-tenant environment (aka, many duplicate schema databases)
I've already asked this question in the SQL Server Community Slack as well as on Reddit. I know these communities overlap quite a bit, so just to clarify - I'm simply trying to do a bunch of ...
- 213
0
votes
1
answer
51
views
Does MongoDB's ESR (Equality, Sort, Range) guideline apply to SQL databases too?
MongoDB has an Equality, Sort, Range (ESR) guideline that has helped me tune my MongoDB indices in the past.
Now I'm using a SQLite database on a side-project of mine and I'm curious if ESR guidelines ...
- 103
0
votes
0
answers
74
views
Increased query latency in Postgres after reindex
I reindexed (concurrently) an index on the biggest table used in a query, but for some reason the latency of the query has increased from aroun 6 ms to 150-200 ms. The reindex was performed because ...
- 1
0
votes
2
answers
90
views
optimise postgresql query with group by and calculated value on a big table
In PostgreSQL 16.9 I have a table Time (duration, resourceId, date, companyId) representing timesheet entries and table Resources (id, name);
I want to list sum of Time durations per week and employee ...
- 11
3
votes
2
answers
554
views
Partitioning heaps - Why?
I know, at least theoretically, that heap tables can be partitioned. As I learn more about table partitioning, I realise that the use case is very limited. Similarly, the use case for heaps in SQL ...
- 1,362
0
votes
1
answer
39
views
Mongo DB Tree Constant Balance Due High Delete/Chunk Migration/Shard Adding
We have huge collections that are having multiple indexes in mongodb with multiple shards. Since the deletion process is time consuming , and ttl mongo simply does not works, we have switched to semi ...
1
vote
1
answer
127
views
Creating efficient indexes on SQL Server with a big WHERE clause
I have a database that contains two tables, containing these columns (some irrelevant columns have been omitted):
Containers table
Id nvarchar(20) NOT NULL primary key, value generated before insert
...
- 111
1
vote
1
answer
59
views
working of index in join query
My question is regarding the Explain output. Please refer the below query and its Explain command output
Explain
update
test.table1 t1
join test.table2 t2
on t1.field1=t2.field1
and t1.field2=t2....
- 209
0
votes
1
answer
127
views
Postgres Query Slow 0.05% of the time
I have the following tables:
CREATE TABLE IF NOT EXISTS users
(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('users_sequence') PRIMARY KEY,
list_id ...
- 175
2
votes
1
answer
117
views
Optimizing Pagination Query with Multiple User Filters on Large Table in SQL Server
I have a table with over 10 million rows tracking user activities. I created a nonclustered index on (UserID, DtModified DESC), which performs well for queries filtering a single user. However, when ...
- 821
0
votes
1
answer
70
views
How does PostgreSQL Use my composite index without the middle column in the query?
I have the following table:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PRIMARY KEY,...
- 175
0
votes
0
answers
60
views
Why does Postgresql use a Seq Scan on the first column of the PK?
This is my table:
CREATE TABLE IF NOT EXISTS public.ob_samples_sc5555 (
stamp timestamp(6) with time zone NOT NULL,
oblvl smallint NOT NULL,
olots integer NOT NULL,
CONSTRAINT ...
- 413
1
vote
1
answer
158
views
Kitchen Sink Problem - Index Strategy in SQL Server
I have a large table in SQL Server that is queried using a stored procedure with dynamic SQL to handle various filter combinations and dynamic ordering. I’m unsure whether I should create indexes for ...
- 821
1
vote
1
answer
129
views
Is there a performance benefit to having a partitioning column not be the first key of an index?
If you are well read on partitioning, then you will be completely aware that partitioning is not a performance feature for rowstore indexes. However, whenever I see arguments made for this, as most ...
- 1,362
0
votes
3
answers
130
views
Are there any edge cases to consider before dropping a duplicate non-clustered index?
I am working on cleaning up/optimizing indexes. I have run Find-DbaDbDuplicateIndex in PowerShell on one of my SQL Instances and identified 5 sets of "duplicate" indexes.
1 is a non-...
