Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
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 &...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
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 ...
user avatar
  • 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} ...
user avatar
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 ...
user avatar
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` = `...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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,...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ((...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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) ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
-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 ( ...
user avatar
  • 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'; +----+-------------+-------------+------------+-------+-----------------------+---------+---------+------+--------+---------...
user avatar
  • 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. ...
user avatar
  • 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(...
user avatar
  • 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 `...
user avatar
  • 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 (...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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....
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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) ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 191

1
2 3 4 5
17