Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
810
questions
0
votes
0
answers
35
views
Missing index large include field [closed]
I am running sp_BlitzIndex (V8.09) on a SQL Server 2014 database. Mode 4 suggests adding an index with 2 Include fields, one of which is Varchar (250). The estimated benefit is 1,538,104. There are &...
- 11
2
votes
2
answers
287
views
how to find out the `Key Lookups` in the current query plan?
I am putting a query to list the key lookups that are present in the current requests being executed, I basically would like to tackle then and see if I could eliminate these key lookup from the ...
- 14.8k
1
vote
1
answer
41
views
Moving from Amazon RDS to own server made queries slow (maybe because heap fetches increased?)
Hope you're doing good.
I've recently moved from Amazon RDS to my own PostgreSQL instance in Hetzner (in order to colocate it with the server and reduce the roundtrip).
However I think that some ...
- 11
0
votes
0
answers
20
views
Database design for fixed records
Potentially trivial database design which I'm trying to overthink.
Let's say that I have:
A fixed number of records (on the order of the 1000s, you can think about a static product catalog).
The ...
- 1
2
votes
2
answers
310
views
Need help tuning a complicated query
I've inherited a rather complex stored procedure that is causing timeouts in our Production environment. I've used SQL Sentry Plan Explorer to help me look at some of the issues. I've identified a ...
- 121
1
vote
1
answer
28
views
New Index creation can benefit from existing Index
I was wondering if a new index and consequentially its creation might benefit from an existing index.
It is something just out of curiosity but extremely helpful in the case when you have to create a ...
- 121
1
vote
2
answers
43
views
How to make Postgres use the correct index here?
Here's a test setup:
CREATE TABLE t (
filename int,
cropped bool not null default false,
resized bool not null default false,
create_date date not null default '1970-01-01'
);
INSERT ...
- 415
0
votes
1
answer
24
views
I have a GIST index and a regular index, one intended for filtering, one intended for sorting, but Postgres WILL NOT use both
I have a table with three columns: two can be used to filter the table, and the last can be used to sort it:
create table thing(
id serial primary key,
location geometry(Point, 4326) not null,
...
0
votes
1
answer
42
views
Optimize join without denormalization
I have two tables:
orders(order_id, scan_timestamp) and
order_products(order_product_id, order_id, pick_number, quantity)
and I want to know how many of a particular product (identified by ...
- 415
0
votes
1
answer
18
views
Using Indexing When Performing JOINs on Part of a Composite Key
I have a table in my database (hosted on MariaDB) that looks like this (I have given the definition in SQLAlchemy),
class Address(Base):
__tablename__ = 'address'
__table_args__ = {'schema': DB_NAME}
...
1
vote
2
answers
29
views
PostgresSQL multiple indexes tuning
I have a table with those columns: id, uuid, customer_id, building, dept, values
I have a combined index on those columns: building, dept.
If I run this query: select count(*) from my_table where ...
- 121
1
vote
1
answer
77
views
How to index a query with many joins
I have this query with this schema that takes ~8s on my laptop and longer on the vm running the service.
SELECT DISTINCT `mofs`.*
FROM `mofs`
INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `...
- 111
2
votes
2
answers
96
views
Postgres : Indexing switches to Parallel Seq Scan from index scan when queried for date range exceeding 150 days
I have two tables - exam_data and series_data, with 300M and 900M records each. I have a query which joins the two tables. If I pull the records for the last 90 days, then the query returns within a ...
- 121
1
vote
2
answers
80
views
What indexing can help improve speed of multi join query with where clause on nvarchar field?
I have a multi-join SQL query.
SELECT A.COMPANYEMAIL, A.CONTRACTNAME,
B.CLAIMNAME, C.PARTNAME, C.PARTPRICE,
C.DAMAGEAMOUNT, C.DAMAGEDATE
FROM CONTRACT A
JOIN CLAIM B ON A.ID=B.CONTRACTID
JOIN PART C ...
- 2,076
9
votes
3
answers
424
views
How to make my query use the available index
I have the following time-series Table in SQL:
CREATE TABLE [dbo].[SensorData](
[DateTimeUtc] [datetime2](2) NOT NULL,
[SensorId] [int] NOT NULL,
[Key] [varchar](20) NOT NULL,
[Value] [decimal](19,...
- 709
3
votes
1
answer
164
views
Optimizing performance when searching on multiple columns with a single search string
We have a system that imports Waivers that customers fill out from a kiosk or online. Since the data entry is performed by the customer, the input format on fields can be very inconsistent. I want to ...
2
votes
2
answers
40
views
Index not picked up on nullable boolean column
I have 2 TimescaleDB Databases (PROD and DEV), both have a hypertable with the same schema and almost the same data (44M rows for the first one, 40M for the 2nd one).
I have to query some data on a ...
- 23
2
votes
2
answers
54
views
Allocation scans in heaps and indexes
I'm trying to comprehend the difference between an allocation scan in a heap and in a B-tree, performance-wise.
Let's say, that we have an Orders table, with a clustered index defined on the OrderDate ...
- 23
0
votes
2
answers
47
views
How to drop all indexes not used for primary keys or constraints
I have an old dataset with a lot of unused indexes. I would like to clean it up and bring it back to what it looked like before custom indexes were added.
Is there a way to drop all indexes except the ...
- 199
1
vote
0
answers
41
views
How to prove Unique index could be better than non Unique index by the execution plan [closed]
I know Unique index will be faster than non Unique index theoretically if data is unique.
Because Unique index be able to provide more information let query optimizer choose more effective execution ...
- 141
0
votes
2
answers
78
views
Why Is There a Sort on this Column
This query has a plan which includes a SORT and the sort is taking up the majority (77%) of the cost:
SELECT
LFTYLM_STATUS
FROM
LFTYLM
WHERE
LFTYLM_LIS_ID = 138998 AND
LFTYLM_LFTY_ID = 78 AND
((...
- 173
0
votes
2
answers
55
views
Order of Key Columns in Index
The execution plan for this query appears to be making a very bad choice (of course I know it is not):
SELECT TOP 1 E_PER_ID FROM E WHERE E_CUS_ID = 1912 AND E_TYPE = 'R' ORDER BY E_ID DESC
The two ...
- 173
0
votes
2
answers
120
views
What index should be used if all queries only need a perfect match (no partial text searches)
I want to improve the performance of the query var dbObj = dbSet.Where(x => x.Name == name).FirstOrDefault(); done on the table pasted below. I was thinking about adding an index on [Name]. What ...
- 113
2
votes
1
answer
46
views
Reasons for Column as Included
We have a simple query:
SELECT COUNT(1)
FROM E
WHERE E_CUS_ID = CUS_ID
AND E_DATE_OPENED IS NOT NULL
AND E_DATE_OPENED < DATEADD(DAY, 7, E_DATE_SENT)
SQL Server SSMS suggests the ...
- 173
2
votes
5
answers
230
views
Is a unique index required for a sequential (autoincrementing) id?
If a sequential AUTO_INCREMENT column is not also the primary key of a MySQL table, does it have to have its own UNIQUE index to ensure long-term data integrity?
I'm working on reindexing a particular ...
- 489
2
votes
2
answers
82
views
Why there's unnecessary 2 index join of the same table?
I have a table named [Job] with 2 indexes as below. (Due to confidentiality, I can't post the whole execution plan with the exact name, sorry about that.)
[IX_Job_WillTest] : (ColA ASC, ColB ASC) ...
- 21
2
votes
2
answers
116
views
Unused Indexes - How Few Is Too Few?
When looking at seeks, scans and updates for indexes in SQL Server, what's a good rule of thumb for determining whether an index is 'unused enough' to be dropped? Maybe some ratio of updates to seeks ...
-1
votes
1
answer
50
views
Database Tuning for 3rd Party Software Database [closed]
I'm starting out as a SQL Server consultant trying to focus on maintenance and performance improvements. I'm using Brent Ozar's first responder's kit and tools. I've had good success with one client ...
- 101
0
votes
2
answers
178
views
SQL index with parent child design
I am looking to understand a typical approach to indexes in a database parent-child design. This is a basic financial calculator for calculating complex commissions used monthly and driven by stored ...
- 3
-2
votes
1
answer
70
views
Should I alter an Index to include a filter in Postgresql 9.6
I have a query which I am looking to improve on with potentially a new index.
SELECT COUNT(price_id)
FROM price.price
WHERE (
(
(userid = '3081217')
AND (
...
- 143
0
votes
2
answers
124
views
Why is this UPDATE query not using an index?
I have a query:
EXPLAIN UPDATE myTable SET col2 = 'foo' WHERE col1 = 'bar';
+----+-------------+-------------+------------+-------+-----------------------+---------+---------+------+--------+---------...
- 119
0
votes
2
answers
57
views
Optimal index for a nested query?
I have a nested query and I am not sure if the index I've chosen fits it's needs. Currently I am not happy with it's performance.
The table contains > 2 Mio rows counting. I am working with Oracle.
...
- 113
0
votes
1
answer
101
views
postgres set null will improve performance in sparse data?
I have ~50 mil data entries, while most of them(maybe 80%) have null values in a column(currently I assigned a dummy value to those null entries). However, I'm doing frequent searching on non-null(...
- 113
1
vote
1
answer
47
views
Can I improve this query by adding indexes or tuning mariadb config?
We're having a problem with a query that keeps executing very very slowly
SELECT `e`.*,
IF(at_news_from_date.value_id > 0, at_news_from_date.value, at_news_from_date_default.value) AS `...
- 147
0
votes
0
answers
49
views
Created an index for a table, adding just one more column, and SQL still suggests a new index
I got a recommendation for SQL Server for this query:
SELECT field1 FROM table WHERE ( field2 < getdate() - 2557)
Obviously the recomendation was
CREATE INDEX idx_name on TABLE (field2) INCLUDE (...
- 7,100
-2
votes
1
answer
41
views
For how long will a query wait, and what will it do post the wait when offline index rebuild is running?
When an offline index rebuild is running, and a CRUD query is fired, assuming the rebuild takes couple of hours.
How long will the query wait for rebuild to complete?
What will it do once it ...
- 2,076
0
votes
1
answer
42
views
What happens when there is a query during an index rebuild in 2017 standard edition? [duplicate]
I know that an online rebuild allows CRUD query to be executed while the index is rebuilding.
Assuming I am using sql 2017 standard edition, it doesn't support online index rebuild. I want to ...
- 2,076
1
vote
1
answer
89
views
Query a table of domain names to get matching top level domains using index
I need to have a table that will contain a single column with a domain+suffix that will looks something like this:
domain_and_suffix
google.com
ynet.co.il
360.cn
I want to be able to query this ...
- 113
0
votes
2
answers
51
views
How to optimise this IN query?
Lets say I have this schema
Resource(resource_mapping_id:Uuid,
reaource_id: uuid,
node_id: varchar,
date: date,
available: boolean,
resource_start: varchar,
resource_end: varchar)
So i have the ...
0
votes
1
answer
97
views
How to index when partitioning for start and stop time columns in PostgreSql 12
I have a log table that takes inserts at a rate of 5k-10k rows per second and number of records grow upwards of 20 Million every 24 hrs. Here is the table structure:
CREATE TABLE IF NOT EXISTS public....
- 1
0
votes
3
answers
46
views
MySQL query performance on single table
I've got a slow query (about 6-10 minutes) that I've been trying to speed up and I'd really appreciate any pointers. I've followed various guides to creating indexes based on cardinality of columns ...
0
votes
0
answers
20
views
Full Text Search Launcher service and process
I am puzzled by the full text search in sql server. I am trying to break down the issue in multiple sub questions, maybe that would help to bring some light into the situation.
I created an ...
- 820
0
votes
1
answer
47
views
Could hardware RAM disks (with battery) have any benefit in database servers?
For database servers with many writes, I am wondering why can't these ramdisk/ramdrive be a solution to avoid wearing out SSDs and to avoid losing data if too much is kept in normal ram.
Can these ...
- 121
2
votes
1
answer
285
views
How can I improve query performance with filter on datetime column using an indexed view?
I have this table:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL PRIMARY KEY,
amount NUMERIC(18, 2) ...
- 135
0
votes
1
answer
21
views
Struggling to create an index for my PSQL Query
I'm running a query on a dataset that's got 30 million rows in and will only continue to grow, the table is
customer_actions (Table size is 2416 MB)
create table customer_actions
(
id ...
- 3
0
votes
2
answers
45
views
Simple example on column order for indexing "where" "order by" and "join"
Here are two large tables and a related query, currently there are no non-clustered indexes:
Table1 has 4 columns
ID (cluster index)
JOINCOLUMN
SELECTCOLUMN
WHERECOLUMN
ORDERCOLUMN
Table2 has many ...
- 93
0
votes
2
answers
63
views
Indexing on a fixed-length string (10 characters - phone numbers) for a large number of entries (performance)?
I understand that a lot of similar questions have already been asked but I could not find a definitive answer for my question.
Briefly,
I have to authenticate users using a REST API, with their phone ...
- 9
1
vote
2
answers
102
views
Why doesn't an OFFSET query benefit more from the index?
I've got a simple example books table with an integer id primary key ("books_pkey" PRIMARY KEY, btree (id)) and 100,000,000 random rows. If I run:
EXPLAIN
SELECT *
FROM books
ORDER BY id
...
- 741
0
votes
0
answers
51
views
How to Properly Configure a Time History Table?
I'm migrating a poorly planned table (in an Azure PostgreSQL db) that stores time history data, and I'm a bit overwhelmed. This history table has a composite primary key on 5 columns, 4 of which are a ...
- 13
4
votes
1
answer
282
views
Why does COALESCE prevent the use of an index on a varchar, but not on a text column?
Imagine a view which contains a COALESCE of two varchar columns from two different tables.
The underlying varchars are indexed in both tables.
In Postgres 11.6, filtering this view on the result of ...
- 191