Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
781
questions
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 = @...
