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,272
questions
1
vote
1
answer
87
views
Index on Column with Very Few Distinct Values
I have to query a table of stock prices that includes a column 'split_coefficient' where split_coeffieint is almost always 1.0, but very occasionally is something other than that: 2.0, 0.5, etc.
I ...
- 111
1
vote
1
answer
113
views
Is there any configuration not to use index merge in mariadb, if a cardinality of an index is below than that?
There is a table have 2 indexes, called state and CATEGORY, which has a very low cardinality respectively 4 and 24 within 7,110,590 rows in the table.
When running query of select statements that ...
- 111
1
vote
1
answer
290
views
What is the way to know the number of rows a Postgres INDEX currently manages?
In Postgres, I created an index which looks like this:
CREATE INDEX foo_idx ON foo (other_id)
WHERE a = 1 AND b = 2 AND c = 3 AND other_id IS NOT NULL;
Now I'd like to know how many of the 150,000 ...
- 125
11
votes
1
answer
1k
views
If you disable a non-clustered index, are the statistics still used?
tl;dr - As the title states - If I disable a non-clustered index on a table, are the linked statistics for that index still used?
I'm aware there are many questions about "dropping vs disabling ...
- 213
3
votes
2
answers
2k
views
indexing multiple columns in a table in MySQL
I am greatly confused about creating an index for a table.
Assuming that a table users have 5 columns, id (primary key), name, email, creation date, and update date.
id | name | email | creation_date |...
- 129
2
votes
1
answer
631
views
Creating indexes in an amazon aws aurora mysql does have a cost?
I am looking for informations without finding them.
If I create indexes in a aws aurora mysql instance, do I have to pay?
The only things I found is that you pay for all I/O operations (and for ...
- 131
1
vote
1
answer
108
views
Query for MAX/MIN of non-clustered index field for partitioned table
We have a table partitioned on a two-year based hash function from the system date at the insertion time (let's call it PartitionKey). It also has a non-clustered index on a single field (Key). We ...
- 123
4
votes
1
answer
252
views
Why does SQL Server use INDEX SPOOL when there is already a relevant index during FK check?
I've got two tables in SQL SERVER 2019: Condition table with 2 FK on Reference table. Condition table has appropriate indexes on FK columns. The schema screenshot and SQL script to generate it are ...
- 49
1
vote
1
answer
45
views
How to solve this problem for hierarchical data structure?
I have this table called Hierarchies. This is its actual definition:
CREATE TABLE `Hierarchies` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`Guid` uuid NOT NULL DEFAULT uuid(),
`Key` varchar(400) ...
- 1,417
2
votes
3
answers
459
views
How to query most recent pair of each unique data-pair?
There's a table wherein there currency exchange rates are.
fx_rates
---
id
buy_curr_code
sell_curr_code
rate
inserted_at
A table gets updated with unknown frequency: sometimes daily, sometimes ...
- 35
0
votes
1
answer
707
views
How to index a Postgres Full-Text Search across relations
I'm in the process of adding indices to support full-text searching in our PostgreSQL (12) database. I noticed that everything works well if all searched columns are in the same table, but that is not ...
- 103
2
votes
1
answer
345
views
How can I force a system function to use an index in SQL Server?
Good morning team,
I’m really struggling to improve the performance of this function: sys.fn_cdc_map_time_to_lsn. It is a system function which is used by CDC, you can read a full description of what ...
- 41
0
votes
1
answer
176
views
How to structure an index to support counting distinct values of one column and grouping by several others?
I have a single reporting table of sales data with about 4 million rows of data:
CREATE TABLE reporting_sales (
customer_id bigint(20) DEFAULT NULL,
effective_date date DEFAULT NULL,
...
- 103
0
votes
1
answer
130
views
can date datatype varchar use index as date datatype datetime index?
I have an existing table that have a column date but varchar datatype and also a date datetime datatype
example:
tbl_name: trx1
columns: trx_id, activity, name, date(datetime)
index: date_of_birth
...
- 25
0
votes
1
answer
38
views
How can I sort thousands of Replies on a Post, following an algorithm that's not just based on columns of the "Replies" table?
On my website, Replies to Post are sorted based on an algorithm that depends on multiple factors: Reactions, Reply Quality, User Reputation, Reports, Replies to Reply, etc... (these are just a few ...
- 809
9
votes
3
answers
3k
views
In MySQL/MariaDB, do Indexes' performance degrade as they become larger and larger?
I'm currently exploring the use of PARTITION, for a specific use case I have.
I use InnoDB, file per table. MariaDB 10.8.
I was reading Rick's PARTITION Maintenance in MySQL webpage.
I'd like to ...
- 809
3
votes
1
answer
127
views
Create Index for specific value in mysql column
I have a column in my table with TinyInt type, which stores millions of numbers ranging from 0 to 50.
However a great proportion of them have the exact value of 50.
Now I need to set an index to ...
2
votes
1
answer
42
views
Does a multicolumn unique index support my query?
Using PostgreSQL-13, I need advice about key index. I have a table named "employee" and I manage my DB using DBeaver Community Edition (latest).
Fieldname Type
---------- ----
pid ...
- 21
0
votes
1
answer
28
views
How does the table in dolphindb implement the reindex function like pandas?
In Pandas, reordering existing data can be done by resetting the index operation. If the reset index label does not exist in the original Dataframe or series, the element values corresponding to the ...
1
vote
2
answers
704
views
Very slow DROP INDEX, ADD PRIMARY KEY index
This is probably related to another question I had, but not sure.
So my table url_meta is 25 GB including data and indexes, 70 million records.
Data 18.3 GiB
Index 7.3 GiB
Overhead 5.0 ...
- 195
1
vote
1
answer
250
views
Find integer in array is very slow
PostgreSQL Version PostgreSQL 15.0, compiled by Visual C++ build 1914, 64-bit
This is my "victim" table.
create table if not exists "KeywordsStats" (
id bigserial primary key, &...
- 11
0
votes
2
answers
285
views
PostgreSQL Multicolumn GIN Index with Ordering
I have a large table (~20M records) in a Postgres DB with a "tags" column of ARRAY type, and a "date_inserted" column. I want to be able to query the latest inserted records for a ...
- 1
1
vote
1
answer
269
views
effectiveness of creating index for ORDER BY column when used with many WHERE clauses
Say I am trying to build a pagination for a simple e-commerce app where user can search, filter, and sort items, and the result is displayed in an infinite scroll UI. I'm planning to use the cursor ...
- 11
1
vote
1
answer
191
views
Using btree index with LIKE operator but not equal (=)
In summary, Postgres prefers doing the equivalent of a [datetime index seqscan]+[filter (using the heavy function)] instead of using the index when using = to find what I like. When simply replacing = ...
- 113
1
vote
4
answers
252
views
Why would deleting a redundant index cause an application to perform unexpectedly?
I have inherited an application (and associated MS SQL database) that was installed by a software vendor many years ago. We do not have any kind of vendor support at this point. Besides using the ...
- 215
1
vote
0
answers
91
views
Curious results from pg_stat_user_indexes
PostgreSQL 12.8, PostGIS 3.1.4
Running select * from pg_stat_user_indexes, there are some rows that have 0 for idx_scan, and a >0 result for idx_tup_read.
The indexes that match this condition are ...
- 223
5
votes
1
answer
356
views
How to optimize PostgreSQL OR query on two indexed columns
I have a large partitioned table that stores monetary transactions between accounts.
CREATE TABLE "transactions" (
"from" BYTEA NOT NULL -- sender account
,"to&...
- 127
0
votes
1
answer
33
views
Minimizing MySQL full text query times on news database
I have a MySQL Database of 250,000 news articles. With time, it's size will continue to increase by at least 250,000 articles per year.
I'm doing full text searches on this database to find articles ...
5
votes
1
answer
560
views
PostgreSQL - Index efficiently on REGEX_REPLACE()
I have a query which is designed to loop and search addresses for duplicates, the query uses REGEX_REPLACE. I am trying to index on the regex as on doing an explain and its doing a sequential scan on ...
- 429
3
votes
0
answers
324
views
Full-text search index across columns and tables
I am building a document database and I would like to utilize the full-text search capabilities of PostgreSQL. The documents do not all follow a clean schema, so I decided to store some values in ...
- 81
2
votes
1
answer
164
views
Skip (or speedup) postgres unique index creation if I know beforehand that it's unique
I have a large Postgres table (dbeaver says it has about 3e9 rows and almost 4Tb of disk space)
I need an UPSERT, but ON CONFLICT section fields don't contain PK and I can't know it without performing ...
2
votes
1
answer
90
views
Performance, RAM improvement with partitions
I have a table like:
create table table_a
(
id_a mediumint unsigned not null,
id_b tinyint unsigned not null,
id_c char(10) not null,
value tinyint ...
- 45
2
votes
1
answer
359
views
MySQL InnoDB foreign key requirements (index/unique/primary)
MySQL 5.6 Doc has set certain conditions and restrictions on foreign keys:
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must ...
- 143
6
votes
2
answers
509
views
Make two columns unique both ways
I'm creating a database which stores football matches.
Each round, any given football club may only play once (home or away).
For example, after the insert of
INSERT INTO Match (Round, Home, Away) ...
- 63
0
votes
0
answers
386
views
Performance improvement of query with Index scan never executed : postgresql
Can the below query be improved with new Indexes.Few Index scans are "never executed"
explain analyze select ASSOC.ID,ASSOC.APPLICATIONCONTEXT,ASSOC.ASSOCIATIONTYPE,ASSOC....
- 161
3
votes
1
answer
1k
views
PostgreSQL attaching range partition is slow
I have a range partitioned table where I would like to be able to swap out partitions over the same range quickly (that is, re-create tables and swap out existing partitioned tables for them). However,...
- 127
0
votes
0
answers
30
views
Index added but is not applied to queries executed by app
TL;DR
Queries are too slow on one of tables, Added index but no change
Description:
There is a channels table with 1 million records. Displaying it's content in the app takes up to 10s! I checked db ...
- 183
2
votes
2
answers
197
views
PostgreSQL BTREE descending but insert ascending
I have a (partitioned) table in a PostgreSQL that stores events. It has the following properties:
rows are rarely deleteed
rows are frequently inserted (~ 100-200 per second, every second on average)
...
- 127
6
votes
3
answers
2k
views
In PostgreSQL, can data be lost by adding an index to a table which is being updated?
I'm not sure how to answer this from the documentation, and am unable to find an answer through searching.
I'm wondering: if there's an existing table which is being updated (having data added to it), ...
- 296
1
vote
3
answers
364
views
What is best practice on creating an index?
what is the best practice on creating an index?
example 1:
createdd_at_indx (created_at)
updated_at_indx (updated_at)
OR
example 2:
date_indx (created_at, updated_at)
- 25
0
votes
2
answers
94
views
MySQL YEARWEEK function is not using index
Given are two tables:
create table table_a
(
id_k mediumint unsigned not null,
id_m tinyint unsigned not null,
id_a char(10) not null,
v tinyint unsigned not ...
- 45
2
votes
1
answer
776
views
Multiple `DROP INDEX CONCURRENTLY` running on the same table in Postgres
I have a Postgres table with many indexes some of which I need to remove as they are unused. To do this without blocking reads and writes to the table I want to use DROP INDEX CONCURRENTLY. I have a ...
0
votes
0
answers
58
views
how to find who disabled the index? The question is not about UNUSABLE index
I have an Index which gets disabled always. Whenever I enable it, then it will goto disable state. Not sure what is going on.
0
votes
1
answer
760
views
Why can't postgres index a text casted enum
I'm trying to add a default value to an enum column so a unique constraint will only allow one null like so:
CREATE UNIQUE INDEX "some_index" on some_table (coalesce(some_enum_column::text, '...
- 109
0
votes
2
answers
362
views
Query planner mis-estimating row counts, uses sub-optimal join strategy
Running Postgres 13.8 on Ubuntu 20, I have a complicated-looking, yet relatively straightforward query. For some reason, the optimizer is wrong about the expected row count of the joins and is using a ...
- 103
0
votes
2
answers
90
views
Why don't Postgresql use a index for a query?
I have a huge table as following:
CREATE TABLE ticks8888 (
tick_time timestamp(6) with time zone NOT NULL,
trade_day date NOT NULL,
other_col FLOAT8 NULL,
CONSTRAINT ticks8888_pkey PRIMARY ...
- 315
0
votes
0
answers
53
views
How to get executing result of a 'DROP' in a custom function in Postgresql?
I have thousands of tables with same structure in a Postgresql database, and each of them has a index with same suffix e.g. '_brok_name'.
I need to drop all those indexes and re-create.
To prevent ...
- 315
0
votes
1
answer
38
views
how to build a multi-column index for sorting, where one of the columns may be null
I'm using postgresql 11, and am looking for a way to optimize the following query, to retrieve the orders by "most recently handled", i.e. most recently completed or created first, where ...
- 101
0
votes
2
answers
2k
views
How to speed up single-column date range query in Postgres?
How would you speed up a Postgres query that's trying to filter on a date column between a start and end date?
I'm running a query like:
SELECT * FROM record WHERE tag_id IN (1,2,3) AND person_id = 1 ...
- 1,314
-1
votes
1
answer
33
views
Will UPDATE benefit from INDEX that contains all the UPDATE params?
I am using SQL Server 2014 and I have a table with a clustered index and two non clustered ones on certain columns.
I have a certain update that is being done quite regularly and updates a numeric(10,...
- 521