Questions tagged [index]

A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns sorted but structures the data differently to allow faster access.

Filter by
Sorted by
Tagged with
0
votes
1answer
29 views

Is there a way to tell optimizer not to use index for reads?

Of course I can just drop and recreate or disable and rebuild it. But for large tables it can take time, and for critical tables it can cause issues. Is there any "more sophisticated" way to ...
0
votes
1answer
40 views

Index Reorganize fails

I am using the default SQL Server Maintenance task to reorganize index only which was running until I got the below error. Executing the query "ALTER INDEX [PK_abc] ON " failed with the ...
1
vote
1answer
30 views

Different (much worse) query plan when using one view

I am running the following query: SELECT m.entityid, m.alias::text AS alias, m.measurement_ts, m.volume_m3::double precision AS volume_m3, m.pressure_mca, m.battery_volt, (m....
0
votes
0answers
24 views

Non cluster indexing with combination or on single entity for multiple columns

I am new to db design I also read concepts of non cluster indexing and know about the combined non cluster indexing but in my scenario I am having user table with multiple search on column table ...
0
votes
1answer
23 views

What is the most performant way to group by and join high cardinality columns in column based table?

I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
0
votes
0answers
9 views

Can MongoDB support TTL per document, not per index?

I am trying to do a Redis-like collection in MongoDB. I need a collection of documents that expire and are automatically deleted after a certain time. I can use TTL Indexes but with this, every ...
0
votes
1answer
18 views

Disk space requirement for REINDEX in Postgresql

There is a server with postres 13 running out of disk space. It contains a table metrics that occupies 90% of the entire database. I have deleted old records in this table. Due to this table is used ...
0
votes
0answers
20 views

Index on json list

I have an attribute (ids) defined as jsonb in Postgres that contains a list of values: CREATE TABLE test.id_data2 ( key bigint NOT NULL, ids jsonb, CONSTRAINT tag_data2_pk PRIMARY KEY (...
-1
votes
0answers
23 views

Index with mixed int and char columns is slow

I have been optimising a complex query with multiple joins; currently down to 0.003 seconds. But I have joined to a table with a mixed index (int, char), which tanks execution time down to 0.3 seconds....
0
votes
2answers
22 views

Finding “one of” entries to a composite key/index

Problem: Assuming I have a table like this: CREATE TABLE `test` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `a_id` bigint unsigned NOT NULL, `b_id` bigint unsigned NOT NULL, `c_id` bigint ...
0
votes
1answer
35 views

Optimize BETWEEN Query in SQL Server Azure

I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:- Timestamp | ComponenentID | Parameter1 | ...
0
votes
1answer
31 views

Understanding Postgres query planner behaviour on GIN index

Need your expert opinion on index usage and query planner behaviour. \d orders Partitioned table "public.orders" Column | ...
0
votes
1answer
41 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
3answers
110 views

Is it worth adding or rebuilding an index on a table already involved in an active query

I've kicked off an SQL Server query that has been going for 12 hours. Annoyingly I forgot to rebuild my indexes on one of the tables involved, I needed to do this as they are over 95% fragmented due ...
0
votes
1answer
24 views

How important is drive speed for a MySql server

I'm trying to invest in a self managed server for MySQL. My use case will involve querying a full text index of anywhere from 5m to 20m records. About 5 thousand full text searches per hour. Usually ...
0
votes
2answers
48 views

What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer) [duplicate]

I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it)...
1
vote
1answer
21 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 ...
0
votes
1answer
64 views

What should be the size of nonclustered index?

I was experimenting with nonclustered indexes, especially their sizes. My aim was to understand how the amount of index keys in Clustered index affects the final size of Nonclustered index. I ...
0
votes
1answer
55 views

Why does pre-sorted index not help me in window function?

I am trying to speed up this query. Currently it takes ~6s. It queries a TEMP TABLE providers_tam_with_offices_tmp. select *, %(max_providers_to_target_per_territory)s as max_providers, case ...
0
votes
2answers
34 views

SQL Server - Overlapping NC Indexes, Drop Question

I'm doing some index cleanup/tuning on a SQL '14 DB and ran across a 6MM row, 16 field table with a sound-looking clustered index and two nonclustered, non-unique indexes having the following ...
0
votes
1answer
29 views

Truncate and insert: Heap and build index or directly into Clustered table

In ETL is it better to drop the index before inserting millions of rows and right after create the index again, or to simply insert into the empty table with the indexe in place. I know that I can ...
0
votes
1answer
22 views

How to index “almost” unique columns?

I have a table that saves the needed information to send notifications to users. now the UID columns in this table is almost unique because mostly each user subscribes with one device. but it is ...
0
votes
1answer
26 views

Query sometimes hangs (but can be executed in less than 4s when run in parallel)

I have a Flask API that has 2 endpoints. When the user clicks a button, the front-end first calls a /calculate route, which computes an expensive query and appends the results to a table called ...
7
votes
2answers
433 views

Indexed columns in SQL Server do not appear to work for basic queries according to execution plan

Disclaimer: I'm not a DBA. I have picked up a few things from this board in the past that I'm building from. I have a table of google analytics session start times. I have an index on each column. I ...
2
votes
4answers
86 views

Adding an INNER JOIN ruins query performance due to different execution plan despite updated STATISTICS and RECOMPILE, why?

In the home-page of my multi-tenant web application, Linq-to-Entities (EF6) is used to get a paged list of Calendar Events. This data is JOINed with some other tables to produce this query, below. The ...
1
vote
1answer
50 views

Stale Statistics and View performance on SQL Server 2016

I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily. So my doubt essentially is - What could be the reason that even with ...
0
votes
1answer
33 views

why is there an index range scan on max() query?

I have a table that logs the transmission of data to another system. create table transfer_log (id number(10), fk_data number(10), first_sent_time ...
0
votes
1answer
17 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(...
0
votes
1answer
25 views

Which query performs better in MongoDB ($or or $facet)?

I have a query similar to the following; db.getCollection('list').aggregate([ { $facet: { "events":[{ $match: { 'type': 'Event' ...
-1
votes
1answer
43 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
2answers
57 views

Postgres index scan on large table taking a long time

I have a large table (~500M records, 85GB in size) on a PostgreSQL 10.10 database (AWS RDS instance). The records are created by a bunch of game simulations which insert "events" whenever ...
0
votes
1answer
22 views

Postgresql index stops being used after time, gets picked back up after re-creating

I'm confused as to what exactly is going on here. Hoping someone can provide some insight. Why did the index stop being used after time? Why does re-creating it work? Would there be a better index? At ...
-2
votes
2answers
59 views

Search with more than 5 Million recods With Posgres

Search to be implemented for currently developing a project which is being developed using Spring Boot and Postgres. The search table has at least 8 columns, all the columns are used in the search ...
0
votes
1answer
61 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
23 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 ...
0
votes
1answer
92 views

Oversize non-clustered index

I have a table with 220 rows. It has about 20 columns. One of the non-clustered indexes has a datetime as the key value and includes a unique identifier and a varchar(128). The clustered PK is a ...
0
votes
1answer
17 views

Should I drop a btree index on a column after adding a unique constraint?

On one of my existing tables I have a btree index. I am now adding a unique constraint to this column. I know behind the scenes it will add a UNIQUE btree index to this column as well. In this ...
3
votes
1answer
32 views

Does a huge key length value for a mulibyte column affect the index performance?

When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) ...
0
votes
2answers
41 views

Remove unused Indexes MariaDB

I have a big database with 50 tables and each table can contain between 100,000 to 100 million records. On these tables are there are 1000s of indexes, but many of the indexes are now not in use. How ...
0
votes
1answer
37 views

mysql creating sort index making queries slow

We have a view table the query for the view is pasted below. The problem is that whenever we try to query items view our database start making sorting index which make the over all time of our ...
1
vote
0answers
34 views

Ola IndexOptimize Deadlocking On Self But No Error Record In CommandLog Table

I have seen this a couple times. There is a job running IndexOptimize that completes successfully, but running sp_BlitzLock will show a deadlock for one specific table. The deadlock command is ...
0
votes
4answers
79 views

Is a Non-Clustered index needed when referencing a small table with a Foreign Key

I'm not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I'm questioning this for a long time. Assume there are 2 tables, one table with a lot of ...
7
votes
6answers
558 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 ...
1
vote
0answers
66 views

Table(s) indexing strategy for separable data [closed]

Scenario: The data needs to be indexed by location, all of the rows include location info and also city info. I want to index by location, but there are millions of rows. The idea is that I can create ...
0
votes
2answers
40 views

PostgreSQL B-tree, good monotonic values?

I am creating a unique ID generator for my application. I am reading everywhere that monotonic keys make life easier for the b-tree. I generate an ID by combining a time part and a random part, the ...
3
votes
2answers
138 views

SQL Server Execution Plans and Index Drops

When a SQL Server index is dropped (say it's a duplicate) what are the steps undertaken by the engine to clear and then rebuild the execution plans that reference it? In particular I'm wondering ...
0
votes
3answers
55 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
1answer
17 views

PostgreSQL - Removing Unused Index on Check Constraint column

I used the script from this blog to identify the unused index on my database. It returns an index which is on a column called result where I have a check constraint on that column. So removing this ...
3
votes
1answer
83 views

Why does BETWEEN use a btree index but “element is contained by” range operator (<@) does not? [duplicate]

I've got a table with a utc timestamptz which has a "btree" index on the utc column: CREATE TABLE foo(utc timestamptz) CREATE INDEX ix_foo_utc ON foo (utc); This table contains about 500 ...
0
votes
0answers
51 views

Index for finding attributes in large JSONB array

Based on this original question and the follow up question from 2015. Suppose I have a jsonb column data within a table tracks. This column holds an array with multiple keys e.g. Artist and Title in ...

1
2 3 4 5
57