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.
3,317
questions
-2
votes
0
answers
24
views
When is a multi-column index generally used by a RDBMS?
Say we got a table with columns: A, B, C, D, E
I am confused as to what specifically the rules are as to when an index is used by the RDBMS in the general sense.
What I think the answer is:
For some ...
-1
votes
1
answer
80
views
Identify which index's rebuild operation is causing the log file to grow
We have a database, in full recovery model, which goes under index rebuild maintenance once a week. There is an index which is causing the log file to grow. I have log file growth event alerts enabled,...
0
votes
3
answers
73
views
What index can be created to optimise this query?
I've got the below SQL query that runs extremely slowly. As for this query, this is due to the "ORDER BY" statement, since Postgres is scanning the changes table by "counter" which ...
2
votes
0
answers
16
views
Postgres uses wrong index when using collation
I have a table with 1000 records. I created two indexes with different collation.
create INDEX test_en on webshop.address (tenant_id, city collate "en-x-icu");
create INDEX test_de on ...
0
votes
1
answer
48
views
Postgres doesn't use functional index for LIKE queries [closed]
I have a query with a LIKE operator:
SELECT "task_v1_taskv1"."id",
"serviced_object_v1_servicedobjectv1"."id"
FROM "task_v1_taskv1"
...
0
votes
1
answer
37
views
How Whatsapp Search/Index Encrypted Chats specially IndexedDB, Searchable Encryption
Our team has developed a Hybrid app which is based on IndexedDB to support cross platforms. This app now has a chat feature and we want to store chats for offline readability. But as a rule of thumb, ...
2
votes
1
answer
58
views
Pageinspect function bt_metap says 'index is not a btree index'
I wish to examine contents of a btree index.
However each of pageinspect's btree functions says "channel_status_pkey_index" is not a btree index
Table looks like this:
CREATE TABLE public....
0
votes
1
answer
52
views
Ideal multi column index ordering for a low-cardinality match and a high-cardinality range
In postgres, I am doing a query on 2 columns
string type with only 3 possible values
timestamp the_timestamp
the query is
select id from my_table where type = 'a-value' and the_timestamp < '2024-...
2
votes
2
answers
61
views
When an index is created from a function on a column, we get the function name instead of the column name in pg_attribute in Postgres. How to fix it?
I have come up with the following query to get the table name, index_type, operator_classes, and uniqueness of indexes based on the answers mostly from @Erwin Brandstetter:
SELECT i.indrelid::regclass:...
0
votes
5
answers
133
views
Why do Databases use HEAP to store table data while other structure like B-tree exists?
Database Engines like MS SQL Server , PostgreSQL etc uses HEAP to store table data while using B-tree to store index data. Time complexity for most operations in B-tree is O(log n) while for HEAP it ...
-2
votes
0
answers
17
views
How can I calculate the block access required in Secondary multi level indexing [closed]
Table structure
Given below are four relational schemas taken from the same University database.
Student(Std Id, S_name, Address, Gender, Dept_Id, GPA)
Department(Dept Id, D_name, Location)
Enrol(...
2
votes
2
answers
259
views
Optimize query performance for a filter on col_a > col_b
I have a large table in a PostgreSQL database (~100 columns, millions of rows). My query has no joins but it does have several where clauses. All of the where clauses are covered by appropriate ...
2
votes
1
answer
70
views
Composite index with a range column in the middle
My understanding is that when designing a composite index a column, which will be used for range tests should be placed at the end of this index, because the usage of the columns in composite indexes ...
0
votes
1
answer
50
views
Index usage for NULL values
If there is index for a column, will it speed up search if condition is ... WHERE column IS NULL? In fact it is composite index on 2 columns and second column can be NULL (WHERE col1 = X AND col2 IS ...
0
votes
1
answer
48
views
trivial query very slow on primary key on a PostgreSQL DB (instantaneous on MySQL)
I have a table data:
with ~ 400 million rows
with data.id as int4, not null, and set as primary key
it's an aws RDS server, with ~128G of RAM
there are no row with id > 1e9
This query:
select ...
1
vote
1
answer
53
views
Which indexes create to speed up a query with where
I have a query but I do not know which index create to speed-up it.
This is the query:
select
`id_my_table`
from
`my_table`
where
now() between created_data
and ifnull(finish_data, now(...
0
votes
1
answer
41
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 ...
1
vote
1
answer
33
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 ...
0
votes
0
answers
43
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
...
0
votes
1
answer
44
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&...
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 ...
3
votes
1
answer
205
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 (...
0
votes
1
answer
58
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 ...
4
votes
1
answer
212
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 ...
1
vote
0
answers
85
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 ...
0
votes
1
answer
11
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 ...
0
votes
1
answer
35
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 ...
0
votes
2
answers
113
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 ...
0
votes
2
answers
69
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 ...
0
votes
1
answer
46
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% ...
0
votes
1
answer
49
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 ...
-1
votes
1
answer
59
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 , ...
1
vote
2
answers
258
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-...
0
votes
1
answer
42
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 ...
2
votes
1
answer
131
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 ...
0
votes
1
answer
34
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) ...
0
votes
1
answer
90
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 ...
0
votes
1
answer
103
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 "...
0
votes
1
answer
154
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 ...
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 ...
2
votes
1
answer
206
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:...
1
vote
1
answer
106
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 ...
1
vote
1
answer
61
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 |
+--------+-...
0
votes
1
answer
417
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 ...
2
votes
1
answer
90
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 | ...
2
votes
2
answers
190
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 ...
1
vote
1
answer
129
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 ...
1
vote
1
answer
133
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 ...
1
vote
1
answer
58
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. ...
2
votes
2
answers
691
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 ...