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
1 vote
0 answers
15 views

Why is Ignore Duplicate Keys not allowed on a Unique Key?

If I understand this answer correctly the difference between an Index with Is Unqiue on and a Unique Key is mainly cosmetic. In the MSSQL interface when I switch to a Unique Key the Ignore Duplicate ...
Dirk Boer's user avatar
  • 365
1 vote
1 answer
12 views

Can't see table partitions after adding PK

I'm on Azure SQL, adding monthly partitioning to some historical archive tables (creating a 2nd table with partitioning scheme/function and dumping the rows from the original into it). I was using a ...
dave111's user avatar
  • 11
0 votes
0 answers
24 views

GIN index not used on query with multiple ILIKE clauses

I am trying to optimize a text search that involves two large tables, persons and emails, and currently uses ILIKE operators. I have those two indexes: CREATE INDEX idx_persons_full_name ON persons ...
Franck's user avatar
  • 13
0 votes
1 answer
39 views

Should you include the primary key in an index, if the column is used in a condition?

Let's say I have this table: create table "Sample"( "id" bigserial primary key, "source" varchar not null, "category" int not null, "t1&...
birgersp's user avatar
  • 163
0 votes
0 answers
20 views

Index field with all same value. Is this bad?

I have the following index create unique index open_issues_unique on issue (status, type, reference_id) where status = 'OPEN'; This means the value of the column status will always be OPEN. Does this ...
Herr Derb's user avatar
  • 113
3 votes
1 answer
181 views

Finding all the index corruptions in the entire Postgres DB

There is DB with unknown amount of corrupted unique indexes. Currently I discover them one by one when I try REINDEX DATABASE CONCURRENTLY <db_name>, handle the specific duplication violation (...
KaliTheGreat's user avatar
0 votes
0 answers
19 views

Timeseries data. Should we include both columns in the index if we have multiple timestamp columns(started_on, ended_on)? [closed]

Table: car_charging_location (id,vehicle_number, location_id, started_on, ended_on) Should we include both columns in the index if we have multiple timestamp columns(location_id, started_on, ended_on)?...
Rakesh Gupta's user avatar
0 votes
1 answer
36 views

Performance difference between multiple tables and indexed single table

I want to create a relational database for financial data and struggling to figure out what the best approach to this is. 1st option: single table for all records with an index 2nd option: one table ...
DamiToma's user avatar
  • 141
4 votes
1 answer
203 views

Unique index on computed column when contributing columns are already unique

I have a table like this: CREATE TABLE dbo.DiscountBarcodeList ( ID int NOT NULL IDENTITY CONSTRAINT PK_DiscountBarcodeList PRIMARY KEY CLUSTERED , Discount int NOT NULL CONSTRAINT ...
Andriy M's user avatar
  • 22.9k
1 vote
0 answers
69 views

Why is PostgreSQL performing a sequential scan, except when my CTE is materialized?

The issue My application is encountering performance issues on a query that, to the best of my understanding, should be performant. In this post, I have simplified the query and schema while still ...
sgielen's user avatar
  • 111
0 votes
1 answer
10 views

PostgreSQL Tables Without Unique Constrtaint On Sequence

I use PostgreSQL. Is there a query to get a list of all tables that do not have the unique constraint on the sequence column? If it helps forming a query I use "reference" for my sequence ...
Ron Piggott's user avatar
0 votes
1 answer
24 views

Is it possible to use both GIN index and pgvector index for querying in postgres?

I have a table like below in postgres: create table posts ( id bigserial, tags text[], content text, content_embedding vector(512) ); create index on posts using GIN(tags); -- from ...
Nguyen Nguyen's user avatar
0 votes
2 answers
92 views

Does rebuilding/reorganize an index in SQL Server free up space in the DB file due to compacting data pages?

If I have an index with a default fill factor of 100%, and modifications result in a large amount of internal fragmentation (page splits, free space, etc.) so that many pages end up around 60% full ...
Marcus's user avatar
  • 59
0 votes
2 answers
66 views

Query is much slower with indexes

For a select query on a table with a few million rows, I notice that the performance is much slower with indexing. I have replicated the problem on an example table with fewer columns: CREATE TABLE ...
ahron's user avatar
  • 793
0 votes
1 answer
43 views

Is it a red flag to see data typecast in query plan?

In a query plan I see the type of a row being cast. There's an index that should be used, and isn't. Is the typecast a red flag? The table has had an ANALYZE and has 110 million rows. Of these, 10% ...
Joe's user avatar
  • 1,635
0 votes
1 answer
38 views

Should I consider GIN index for several columns?

In our application we have a field that can be used to match with several columns in our database. So if the user searches for somestring we will return all the rows WHERE column1 = 'somestring' OR ...
zefciu's user avatar
  • 101
-1 votes
1 answer
51 views

What would be a good strategy for a 5tb database and index maintenance?

We use ollas index maintenance with some changes here and there done by our team. we usually have an issue with a huge database where its Alter index locks some processes. Its normally a reorganize , ...
Racer SQL's user avatar
  • 7,392
1 vote
2 answers
213 views

How can I tell if a full-text index is being used?

It's possible to tell how much a rowstore index is used, but what about full-text? I could find nothing in any GUI or DMV. Even dbatools doesn't support it. I've inherited a server that has a few full-...
J. Mini's user avatar
  • 135
0 votes
1 answer
39 views

Is there ever a reason to create a non-clustered index on a column that is already the clustered primary key?

Suppose that I have a table. Its primary key is clustered and consists of exactly one column. Is there ever a reason to also make a non-clustered index on that same column with no other columns in any ...
J. Mini's user avatar
  • 135
2 votes
1 answer
81 views

Is automatically renaming indexes when renaming columns relevant for database health/maintenance?

I am currently building out a few features for a project where customers are able to change up their database schema via an UI and we handle all the nitty-gritty details on running the required ...
Firat Oezcan's user avatar
0 votes
1 answer
33 views

Help inconsistent index result on generated column after server restart

I have a table that has a date column and I need a data that will be group by week. And I found this Youtube video that used an index generated column Faster database indexes (straight from the docs) ...
zer09's user avatar
  • 453
0 votes
1 answer
88 views

Is there any advantage in using IdentityColum+AnotherColumn vs just IdentityColum as the Clustered Index?

The database where I work has Table A with column A_id (an autoincrement field, which in practice is always unique, never null), and column Date (not unique), and other columns. For some reason, the ...
Juan Perez's user avatar
0 votes
1 answer
101 views

Extraneous Unique Index in Oracle DB DDL

I exported the DDL for the EMPLOYEE table in an Oracle DB. What is this index? What columns is this index for? CREATE UNIQUE INDEX "MY_USER"."SYS_IL0000126968C00005$$" ON "...
Nathan's user avatar
  • 113
0 votes
1 answer
99 views

EAV or JSONB column for dynamic data that needs good indexing

I have some data in a Postgres database that does not fit into a static schema. There is a table "content" and each piece of content has a user-defined type. Each type can have different ...
Fabian's user avatar
  • 103
0 votes
0 answers
31 views

Why does this index make one query faster but another one slower

I am optimizing my database I found an index that makes one query go from 400ms to 2ms but it also makes another query go from 1000ms to 4000ms. It seems that the planner is choosing a suboptimal path ...
Erik's user avatar
  • 1
2 votes
1 answer
165 views

DB migration - Converting Unique constraint to Primary key

I have a a table which has unique index: CREATE UNIQUE INDEX IF NOT EXISTS tb_sha256_key_idx ON blob_props (sha256, key); I want to change it into primary key during db migration and there are 2 ways:...
KaliTheGreat's user avatar
1 vote
1 answer
94 views

MySQL Multi-Valued Indexes

In the following SQL, Multi-Valued Indexes are generated for the json array. ALTER TABLE table_name ADD INDEX index_name((CAST(json->'$.field' AS json_type ARRAY))). ALTER TABLE table_name ADD ...
feilong gao's user avatar
1 vote
1 answer
60 views

Why does creating an index on MySQL(InnoDB) and then crashing deliberately in between not result in a rollback?

I created this table USERS having `10 million records mysql> desc users; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-...
catsarethebest's user avatar
0 votes
1 answer
177 views

Is there a way to monitor indexes in Oracle 19c

Is there a way to monitor indexes in Oracle 19c? I have turned index monitoring on ALTER INDEX my_index_i MONITORING USAGE; Using the queries below to get a rough idea when an index was used: SELECT ...
datascinalyst's user avatar
2 votes
1 answer
86 views

Why is my composite index on 3 int columns smaller than the PK index on 1 int column?

I have a table called data_table. Below are schema, indexes, and their sizes. Table "public.data_table" Column | Type | ...
Ezhar's user avatar
  • 21
2 votes
2 answers
93 views

Indexing Entire JSON Column vs Indexing Specific JSON Field in PostgreSQL

I have a PostgreSQL table named item_log with a log column of type jsonb that contains various attributes. One of these attributes is delete_log. I'm querying this table based on the created_at field ...
sujeet's user avatar
  • 235
1 vote
1 answer
104 views

aurora mysql index creation challenges on large tables

I have a table of yearly data [size 500GB = ~1 billion rows] in aurora mysql 3.02.2. requirement is to migrate this table to another aurora mysql [PROD] using DMS. we initiated the DMS task with data ...
JollyRoger's user avatar
1 vote
1 answer
83 views

How do I find missing sub-partitions of an index to make main index complete?

I have a table with many partitions that I wanted to add a new index to. Following the instructions from Postgres I created the new index on the main table using ON ONLY and then proceeded to create ...
Kenny Loveall's user avatar
1 vote
1 answer
57 views

What indexes should I put on this table, given this illustration of its contents and the query I will be running on it?

I am working with a table (which I control) that is being populated by another application (which I don't control), each time a record of a particular type in an external system is created or updated. ...
utterly confuzzled's user avatar
2 votes
2 answers
685 views

Why are these columns specified in the include clause of an index rather than as key columns

For the below query, I'm trying to figure out why the index suggested by SQL Server on the sysjobhistory table, which is also the index that results in a seek, was created on the Job_Id column with ...
Marcus's user avatar
  • 59
1 vote
1 answer
126 views

Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?

I've used Ola Hallengren's Maintenance Solution for a while, but I've never used the @DatabasesInParallel parameter. I am considering using this on the Index and Statistics Maintenance jobs where I ...
Wayne Cochran's user avatar
0 votes
2 answers
72 views

Index optimization for generic UI sorting and filtering [duplicate]

We have a table that may contain 1M-5M or more rows. It has an assortment of string, numeric and date columns. The UI presents a table view, with "infinite scroll" paging, and various ...
e.dan's user avatar
  • 101
1 vote
1 answer
66 views

potgresql uses only one worker for index creation

To speed up the CREATE INDEX operation I increased the work_mem and set max_parallel_maintenance_workers to 8 and it worked as expected when I was creating a B-tree index on the table. I saw 7 ...
lian's user avatar
  • 113
1 vote
3 answers
56 views

MySQL query optimisation

This is the MySQL table DDL: CREATE TABLE `users` ( `username` varchar(250) NOT NULL, `jid` varchar(250) NOT NULL, `nick` text NOT NULL, `subscription` char(1) NOT NULL, `ask` char(1) NOT ...
datascinalyst's user avatar
0 votes
1 answer
39 views

Postgresql - optimizing multicolumn search with "prefix" search pattern as one of the filters

I'm now working on the project that automates "contracts" creation and signing. Recently I received a requirements to implement search functionality for "contracts", so I struggle ...
EPe's user avatar
  • 1
2 votes
1 answer
86 views

Should I use this partitioning/indexing strategy for a table that grows by about a billion rows per year?

I'm a software engineer with about 10 years of experience building Laravel web applications for small businesses. I'm pretty comfortable with database design, but it's not my greatest strength. One of ...
Ken Schnetz's user avatar
0 votes
2 answers
106 views

Does the logical query processing change when indexes are involved?

I highly recommend this book. This book explains in a great way the behavior of a sql query, but my doubt is, How is a sql query processed when there are indexes, that is, would this behavior change? ...
jwa's user avatar
  • 1
0 votes
2 answers
177 views

What is the difference between CREATE INDEX and CREATE INDEX CONCURRENTLY in PostgreSQL?

I would like to know what the difference is between CREATE INDEX and CREATE INDEX CONCURRENTLY (if any) after the creation of the index has built. I have been having a discussion with a colleague ...
Aaron Gonzalez's user avatar
0 votes
0 answers
46 views

Complex day-to-day difference of historical data in SQL server

Setting: I have a table (let's call it "HistoricalData") with 10 attribute columns, a date (in string format) and a value. The table has a unique clustered index on (date, idcol1, idcol2,.....
101001000100001's user avatar
1 vote
1 answer
56 views

MySQL - Slow Performing Subquery when DISTINCT Is Used

I have this query that runs very slowly when DISTINCT is used; SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
Marisa's user avatar
  • 23
0 votes
0 answers
25 views

Verify a postgresql index can be dropped securely

Indexes use resources because they have to be updated when data changes. How to check if an index can be securely dropped (ie. not used at all)? This request seems to return the unused indexes, ...
Slim's user avatar
  • 237
1 vote
2 answers
72 views

Should a frequently-growing log file be stored as a table or a blob in the database?

I'm an undergrad and haven't worked with any actually database yet so my apologies if I misunderstand/misuse any terms. My question comes from an assignment of mine, but this is my attempt at learning ...
Cydennn's user avatar
  • 13
1 vote
0 answers
49 views

Postgres 15 doesn't use index under RLS

Recently, we upgraded the AWS RDS instance from Postgres 12.14 to 15.4 and noticed extremely high disk consumption on the following query execution: select (exists (select 1 as "one" from &...
Alexander Ok.'s user avatar
3 votes
3 answers
258 views

Do you need to explicitly include the primary key in a composite index?

I recently read that when creating a composite index in MariaDB that the primary key is appended to the end of the index "under the covers". I access my data many times in order by these ...
scott8035's user avatar
  • 131
3 votes
2 answers
111 views

MySQL chooses a slow execution plan for selecting rows by range condition with ORDER BY, LIMIT and OFFSET clauses

I have a table "client_data" with 2 indexes: CLIENT_USER_CREATED (CLIENT, USER, CREATED) CLIENT_CREATED (CLIENT, CREATED) The primary key is (ID, CLIENT, CREATED) The table is partitioned ...
Vladimir Yanchenko's user avatar

1
2 3 4 5
67