Skip to main content
The 2024 Developer Survey results are live! See the results

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
1 answer
36 views

Trigram index is slow for common terms

I have a PostgreSQL database with bunch of string columns in a table with a trigram index on them using pg_trgm. They are queried like this SELECT [...] FROM mytable WHERE mycolumn ILIKE '%searchterm%'...
Fabian's user avatar
  • 115
0 votes
1 answer
28 views

Performance Impact of Partial Index on INSERTs Not Involving the Partial Match

Given: postgres=# create table testing(a int primary key, b int not null); CREATE TABLE postgres=# create index on testing (b) where b = 0; CREATE INDEX postgres=# \d testing Table "...
Kevin Meredith's user avatar
0 votes
1 answer
48 views

How to create a table with only indexes and no actual data?

Question: Is is possible to create a table in PostgreSQL for the sole purpose of creating specialized indexes (used for advanced search use-cases), without storing duplicate data in such tables? ...
Saurabh Nanda's user avatar
2 votes
2 answers
93 views

Automatic index creation - too many

When we have the auto tuning set to ON for index creation, the Azure SQL Hyperscale db has created about 10 indexes for some of our large tables. In analyzing the indexes, some of them differ only in ...
Anand Sowmithiran's user avatar
0 votes
0 answers
47 views

Not able to drop non-clustered index

I was executing a query to create a non clustered index in one of the table and that has around 43M of rows. It runs more than 1 hour, then I felt something is wrong and have cancelled the query ...
Tufan Chand's user avatar
1 vote
1 answer
53 views

How a B-Tree index on a columnar table would look like?

Greenplum database supports B-tree index on append-optimized columnar tables which allows UPDATE operations as well . Even though it's not a recommended practice to have index on such tables(probably ...
goodfella's user avatar
  • 168
1 vote
1 answer
86 views

What is the impact on an index when I am widening a key varchar column

One of the central tables in my database has a varchar(25) column named EmployeeID. This column is used within a compound index and is likely the most used index in our system. We got an enhancement ...
Aheho's user avatar
  • 163
0 votes
1 answer
38 views

Postgres - Improving multi-column GIN text search performance

I've got a schema for customer records a bit like: account_id - UUID name - text I currently have a GIN index that looks like: ... USING GIN (account_id, name gin_trgm_ops) We have a mix of accounts....
ColinHowe's user avatar
  • 101
1 vote
1 answer
65 views

Creating indexes before bulk-loading data is weirdly much faster than vice versa

EDIT: An important thing that I missed is the fact that I parallelize COPY operations by N number of cores, by splitting the data file into N equal parts, and issuing N requests through separate ...
Rafael Sofi-zada's user avatar
0 votes
1 answer
20 views

MongoDB createIndex compound unique index on array field

I'm trying to create a unique index in MongoDB that enforces uniqueness based on the existence and non-undefined nature of a field (barcodes) in my skus collection. Here's the index creation query I'm ...
Brainfuck's user avatar
9 votes
1 answer
587 views

Does Ordering of Columns in INCLUDE Statement of Index Matter?

I was reading through the comments of this answer on another question, and it got me wondering if the ordering of columns in the INCLUDE clause of an index (on SQL Server specifically but also any ...
user avatar
0 votes
1 answer
26 views

When is re-indexing triggered after multiple updates

If I have a table in a PostgreSQL database and I want to perform a few thousand of the following statements: UPDATE table_name SET indexed_col='some', other_col='values' WHERE pk='row-slug'; UPDATE ...
kaan_atakan's user avatar
0 votes
1 answer
59 views

How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?

Context Here is the DDL that I am intending to use to define the table for a logistics/delivery company. CREATE TABLE scraping_details ( id INT IDENTITY(1,1) PRIMARY KEY, -- Identity insert and ...
Della's user avatar
  • 73
0 votes
2 answers
35 views

Postgres query planner suddenly changing his mind

My Postgres table has a range column containing timestamps with time zone. I have created an index on the lower bound of the range, like so: CREATE INDEX bdg_sys_period_start_idx ON building USING ...
Istopopoki's user avatar
0 votes
0 answers
49 views

Indexing strategy for a very read-heavy application with most business logic managed by the database

We're working on migrating from a NoSQL database to Postgres. Our entire business logic (complex network calculations) is handled by the database – we have approx. 150 tables, 300+ dynamic views (...
Rafael Sofi-zada's user avatar
3 votes
3 answers
467 views

Enforce non-unique combination of columns, where combination is required

I have the following (and more non-related) columns in my MSSQL database table: Id, ImageUrl and ImageId. The combination of ImageUrl and ImageId can occur multiple times. ImageUrl belongs to ImageId ...
Max's user avatar
  • 141
1 vote
1 answer
86 views

Why MariaDB 11 isn't using DateTime index?

I've a big table (MariaDB 11.2) with many fields, but I'll resume the table like this: CREATE TABLE `GT_CalendariProfessional` ( `CalendariProfessionalID` BIGINT(20) NOT NULL AUTO_INCREMENT, `...
Dimas's user avatar
  • 131
0 votes
1 answer
21 views

MySQL - Proper key length

I was looking at a table definition query from WordPress and noticed something strange. First of all, the query was this: CREATE TABLE $wpdb->comments ( comment_ID bigint(20) unsigned NOT NULL ...
Faye D.'s user avatar
  • 103
0 votes
0 answers
19 views

Monitor delay for updating index on deletion

I'm aware than adding / removing data to a table that has one or more index can slow down the operation. However, I'm wondering how I could monitor that (on postgresql). When I delete data in one ...
poussma's user avatar
  • 101
0 votes
3 answers
50 views

Why is the Postgres query planner choosing the wrong strategy?

I have a Postgres/Postgis table with ~50M rows. I am making requests similar to this: select * from building b where ST_Intersects(b.point, some_fixed_geometry) order by id limit 20 offset X; The ...
Istopopoki's user avatar
1 vote
1 answer
41 views

composite index on (A,B) + single index on (A): postgres is using single when it "should" use composite?

There are several good posts, e.g: Should I use a composite or single-column index? showing that if you have a composite index on columns (A,B), and a single index on (A), postgres will use the ...
John Little's user avatar
0 votes
1 answer
43 views

How to Create a Case-Insensitive Unique Compound Index in MongoDB?

How to Create a Case-Insensitive Unique Compound Index in MongoDB? Question Body: I'm trying to create a compound unique text index in MongoDB with the following fields: tenant_id: 1 seller_id: 1 ...
Brainfuck's user avatar
0 votes
1 answer
30 views

SQL Server management Studio does not show Index recommendation

I have installed SQL Server management studio version 20 (and also tried with 19 and 18) and surprisingly unable to see the Missing Index Hints while explaining a SQL Query. Whenever i receive a SQL ...
Parth's user avatar
  • 1
2 votes
1 answer
79 views

Should I delete index before bulk-loading data with COPY?

My pet project has one table which is totally rewritten once a day. This project uses a PostgreSQL database. I decided to use COPY to insert data from 4 CSV files (about 3 million rows). At the same ...
Rash-77's user avatar
  • 21
0 votes
1 answer
27 views

If SSTable sorted, why do we need Sparse Index?

As far as I know, when we create clustered index it restructures the table in B-Tree form and uses binary search when we try to find particular row. SSTable is also sorted by key. Why do we need ...
Shamil's user avatar
  • 3
0 votes
0 answers
33 views

Mongo DB slow deletes due to indexes rebuild , same with TTL indexes

We are having huge collection which we can not partition ( we need to get the records by last updated time which can not be known ). To decrease the storage we have enabled TTL index based on last ...
Yulian Oifa's user avatar
0 votes
0 answers
14 views

SASI indexes on cassandra 3.11.4

we're running a 3.11.4 cassandra cluster with 3 nodes. The application team wants to create a SASI index on a clustering column, which has a high cardinality. The thing is that inside cassandra.yaml a ...
Pablo Olmos's user avatar
0 votes
1 answer
42 views

Is expected that indexes on binary columns perform slower than those on bigint columns?

Running MariaDB 10.8.8 I've had to implement geo-targeting on my site. For this each night I pull the free IP-to-country dataset from ipinfo.io. I have a PHP script that then creates two tables, ...
Codemonkey's user avatar
0 votes
1 answer
74 views

What is harm in marking a function as LEAKPROOF in PostgreSQL when row level security is in effect in a single tenant setup?

I am trying to understand the implications in marking a PostgreSQL function as LEAKPROOF. Let us say we have a (single tenant) web application setup, which is Browser -> Server (Django, Rails, etc.)...
rationaltiger24's user avatar
0 votes
1 answer
50 views

When generating combinations using a CTE, is there a way to use an index to group the elements of each combination?

I have a project that involves combining a variety of things into their possible combinations. This includes permutations, combinations, with and without repetition, and some more exotic ways of ...
alx9r's user avatar
  • 431
0 votes
0 answers
35 views

Create an index at oracle sql server on tablespace

I want to create an index of type context on a tablespace different from the one the table is. Also I want to pass parameters. The data type of ATTACH_BIN is BLOB. CREATE INDEX ATT_INDEX ON ...
Nick's user avatar
  • 1
0 votes
0 answers
12 views

MongoDB - optimizing compound index by using executionStats

I am visiting the MongoDB learn DBA course. In one of the labs, it was shown how to optimize the compound index by examining the explain plan. The lab used the sample training dataset. Following query ...
ray's user avatar
  • 126
0 votes
1 answer
56 views

What is the difference between CURRENT_TIMESTAMP::DATE and CURRENT_DATE in PostgreSQL? [closed]

I created an immutable function as below: CREATE OR REPLACE FUNCTIONGetCurrentDate() RETURNS DATE AS $p$ BEGIN RETURN CURRENT_TIMESTAMP::DATE; END; $p$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER; ...
Jitendra Loyal's user avatar
0 votes
1 answer
60 views

PL/SQL dynamic Index loop based on list variable

Currently, I am indexing column by column with the following code snippet: DECLARE already_exists EXCEPTION; columns_indexed EXCEPTION; PRAGMA EXCEPTION_INIT ( already_exists, -955 ); ...
Matt Miles's user avatar
-1 votes
1 answer
112 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,...
Stackoverflowuser's user avatar
0 votes
3 answers
82 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 ...
laurent's user avatar
  • 191
2 votes
0 answers
30 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 ...
Florat's user avatar
  • 21
0 votes
1 answer
162 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, ...
Abdul Jabbar's user avatar
2 votes
1 answer
66 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....
Tomas F.'s user avatar
  • 123
0 votes
1 answer
143 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-...
John Bachir's user avatar
2 votes
2 answers
68 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:...
Seperman's user avatar
  • 123
1 vote
5 answers
244 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 ...
goodfella's user avatar
  • 168
2 votes
2 answers
296 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 ...
olleicua's user avatar
  • 123
2 votes
1 answer
108 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 ...
Ivaylo's user avatar
  • 133
0 votes
1 answer
58 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 ...
i486's user avatar
  • 245
0 votes
1 answer
61 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 ...
Karl Forner's user avatar
1 vote
1 answer
56 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(...
Giacomo M's user avatar
  • 163
0 votes
1 answer
83 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
  • 375
1 vote
1 answer
104 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
  • 13
0 votes
0 answers
75 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
  • 33

1
2 3 4 5
67