Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
57 views

Why is this UPDATE query not using an index?

I have a query: EXPLAIN UPDATE myTable SET col2 = 'foo' WHERE col1 = 'bar'; +----+-------------+-------------+------------+-------+-----------------------+---------+---------+------+--------+---------...
0
votes
2answers
50 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. ...
0
votes
1answer
26 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(...
1
vote
1answer
28 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 `...
0
votes
0answers
45 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 (...
-2
votes
1answer
36 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 ...
0
votes
1answer
25 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 ...
1
vote
1answer
77 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 ...
0
votes
2answers
48 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
1answer
92 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....
0
votes
3answers
37 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
0answers
17 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 ...
0
votes
1answer
44 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 ...
2
votes
1answer
105 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) ...
0
votes
1answer
19 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 ...
0
votes
2answers
42 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 ...
0
votes
2answers
27 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 ...
1
vote
2answers
55 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 ...
0
votes
0answers
35 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 ...
3
votes
1answer
96 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 ...
2
votes
2answers
77 views

Is the order of columns in a clustered index important?

Does it matters which field I specify first in the index? would CREATE SET TABLE my_table ( validity_date DATE , branch_id SMALLINT , account_class_id BYTEINT , ...
0
votes
1answer
25 views

MySQL FULLTEXT BOOLEAN Query result differs between identical DBs

I have two Windows MySQL 5.7 InnoDB databases. One is a production db, and the other is a test db that was created from an image of the prod db. Both have equal data loaded from an Oracle merge script....
0
votes
2answers
40 views

Index Drop for Partial Overlap?

I'm working with a 50mil row table with the following: Clustered Index keyed on [Col1] Nonclustered Index keyed on [Col1], [Col2] (no includes) It seems like the right call here would be to drop #2 ...
-1
votes
1answer
31 views

Efficient dimension and fact joining

I have large fact table, and a much smaller dimension table in a simple star schema: --1. CREATE TABLE dbo.Dim ( Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, CustomerName VARCHAR(2000) ) --index ...
1
vote
0answers
39 views

How to interpret pg_stat_user_indexes and pg_statio_user_indexes results?

I have this query to analyze index usage in Postgres: SELECT idx.* , idxio.idx_blks_read , idxio.idx_blks_hit FROM pg_stat_user_indexes idx JOIN pg_statio_user_indexes idxio USING (...
3
votes
1answer
81 views

Best sort order in B-tree index to support query on recent rows?

Suppose that I have a table which have a description like: create table my_table ( id serial, create_date timestamp with time zone default now(), data text ); and a query like: select * from ...
0
votes
1answer
38 views

Same query shows significant differences in performance on different data [closed]

I'm having a problem with writing a Postgres query that really appreciates anyone's help. I have 3 tables related to each other as below: Order: id (pk) updated_at (datetime) owner (foreign key, to ...
0
votes
2answers
56 views

Do wide included column indexes affect inserts?

I had to create indexes with included columns due to high I/O, nested loops joins, and key look ups that blocked inserts: SELECT * FROM Table WHERE (Column1 = 'ED69K') I created an index on ...
0
votes
0answers
30 views

Searching text using LIKE, and Included Column

SELECT * FROM Table WHERE (Column1 = 'ED69K') SELECT * FROM Table WHERE (Column1 LIKE '% ED69K%') I have a wide table with 25 columns on the table. The above queries do need to do key lookup ...
1
vote
2answers
54 views

Optimize table using columnstore index?

I am new to table performance tuning. I have a flat table with millions-billions of records, with no delete-update operations. Only insert and read operations. Its structure is like this: Column1 ...
3
votes
2answers
95 views

Correct composite indexing indexing order

I have MySQL (MariaDB) database. I have a table 'sensors' ,which collects data from IoT devices. Each device may have 4-6 parameters that it records, like temperature, humidity, air quality, etc. Each ...
0
votes
0answers
43 views

SQL2005 Index exists in sys.indexes but not on table

I have a routine that runs through all the non-clustered indexes in a given database on SQL2005 (SP3), and reorganizes and rebuilds them (if fragmentation > 20%). This process is erroring out on ...
0
votes
3answers
70 views

Slow query with where and index on some fields

I'm working on a project with the PostgreSQL 12 database. I have a table of about 500 MB. The structure of the table is as follows: create table table_name ( id serial not null constraint ...
0
votes
1answer
62 views

"Filter:" instead of "Index cond:" in index production usage

An index to optimize fetching multiple external identifiers and update underlying data was created. The index is a simple btree of the fields. The external identifier needs to be a string as it ...
1
vote
1answer
37 views

Improving indexing of jsonb data when the json structure is well defined

I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
0
votes
2answers
53 views

Storing Live Timeseries Data in SQL Server Azure Database

I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors. Data Source:- 5-minute data is obtained via an API. Current table structure: - Timestamp |...
1
vote
2answers
60 views

Why is ASC 100x faster than DESC and what can I do about it?

I'm on the cusp of understanding this, but I don't understand the fix that I need. The following query takes about minute, give or take, to return 20 records: explain (analyze, buffers) select "...
2
votes
3answers
116 views

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's ...
0
votes
1answer
42 views

INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario?

When you are in the position of inserting data into a table if the data does not already exist ...what is the best way to go with indexes? I'm specifically thinking of SQL Server here but I assume the ...
1
vote
1answer
55 views

Will PostgreSQL use parts of a composite index even if all parts aren't listed in the where clauses?

I could obviously test this empirically but I've found that without loading fake data EXPLAIN shows that Postgres just loads the full page of memory containing the table and scans. I also haven't ...
9
votes
2answers
232 views

Why is SQL doing an index scan and seek with the same XML query structure?

I've set up a test to benchmark xml performance in SQL server. Test Setup One Million rows of data XML defined Column with primary key A primary XML index A secondary XML index on the path XML data ...
0
votes
3answers
60 views

Index Maintenance on busy OLTP database

I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition. There is no budget for enterprise nor ...
0
votes
1answer
26 views

Query ignores index resulting full table scan

I have two tables from django in mysql 5.7, auth_user and account_account: CREATE TABLE `auth_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(150) NOT NULL, `first_name` varchar(...
-1
votes
1answer
49 views

When was my indexes in the database fragmented?

I am trying to create a script that finds the index fragmentation, have it log to a table that I can query each time it runs. I need to fins out specifically what time my indexes in a table get ...
0
votes
1answer
94 views

Use index in ORDER BY query SQLite

I have a table that store messages for my Chat app: CREATE TABLE message ( currentUserId, threadId, timestamp, message, msgType, msgState ) Then I used this query to load 20 ...
0
votes
1answer
24 views

Indexes and windowing functions

We have a design in which a table stores the history of changes to an entity, and a view queries the table for the latest row for that entity. Say, employee_history and employee (the view with latest ...
7
votes
6answers
560 views

SQL Server Index Drops - Considerations

I've been doing some index usage reviews (using DMV seek, scan and lookup stats) and have identified a number of 'unused' indexes in our biggest and busiest DB. I am interested in the opinions of ...
0
votes
1answer
38 views

Indexing very similar character varying field in postgres [closed]

I have a table with a column "name", the table is describing a media asset and the name is a character varying field containing the file name. The table is generated and used by a CMS (...
0
votes
3answers
59 views

Difference between the following postgresql indexes

On a table with roughly 15 million rows, I've got a bunch of boolean fields indicating performed actions and also timestamps of when the action happened for a row. The following 2 indexes, I would ...
0
votes
2answers
54 views

Optimize performance for query with lots of OR's

I have the following query select Id from Table1 where column1 = @inputparameter1 or column2 = @inputparameter1 or column3 = @inputparameter1 or column4 = @inputparameter1 or column5 = @...

1
2 3 4 5
16