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
4
votes
1
answer
608
views
AWS Aurora Mysql seemingly not picking PRIMARY or secondary index
Relevant System Info:
Aurora Mysql 8.0.mysql_aurora.3.03
rg6.xl instances (1 writer, 2 read replicas)
Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot)
I have been ...
- 135
0
votes
1
answer
43
views
Index consolidation of two similar indexes
We have a primary table that drives most of the work within our product with many columns.
Index 1 has a key consisting of: Column A, Column B, Column C, Column D
Index 2 has a key consisting of: ...
- 3
1
vote
1
answer
1k
views
rebuild index in Oracle
when do I need rebuild an index.
we are using in data warehouse bitmap indexes. if I truncate table and there is no row and after that I insert new rows an index should be rebuilt as far I know ...
Pato
2
votes
0
answers
109
views
Why is it so fast to create an index on a table?
While optimizing my query in Postgres, I noted that creating an index is always very fast, less than 1 second on a table with millions of rows.
But if there's no index, a query would take a few ...
- 315
1
vote
1
answer
24
views
Postgresql 14: Searching partial index with type conversion
Based on solution from this post I've created the following partial index:
CREATE INDEX CONCURRENTLY IF NOT EXISTS cdbus_message_message_vessel_barcode_delete_procedure_idx ON tare_new.cdbus_message ...
3
votes
2
answers
366
views
How to gather Index usage information
Following query correctly outputs number of seeks, scans, etc. I have question on understanding some rows in the output. In some output rows of the query, the index name is shown as NULL and the ...
- 289
1
vote
1
answer
108
views
What's the fastest way to query multiple tables at once where each table represents one year of data?
I'm developing an web app based around a database with multiple decades of data with multiple schema changes over the years. Each year of data is released as a zip file containing CSVs named table1....
- 111
0
votes
1
answer
38
views
Indexing on ANY queries
I have a table that I am trying to query. Here is the create statement for the table
CREATE TABLE IF NOT EXISTS public.TableName
(
i integer NOT NULL,
t integer NOT NULL,
v ...
- 101
0
votes
2
answers
95
views
sys.indexes - type column
According to official documentation of sys.indexes, type = 2 -->Nonclustered rowstore (B-tree). But the line 4 of the following example taken from online here is showing type = 2 -->Nonclustered ...
- 289
1
vote
2
answers
50
views
How to optimize this query or these many indexes?
What am I doing wrong here?
I will post the table structure and indexes first, then the query that is slow, and many others that are very fast (instant).
How to optimize that one query to run faster ?
...
- 195
3
votes
1
answer
885
views
How to make a Postgresql function parallelly running?
I have thousands tables with same structure, and I need to add a index on each.
In order to simplify my work, I code a function like this:
CREATE OR REPLACE FUNCTION createIndexTD(
IN tbl_name ...
- 315
2
votes
1
answer
311
views
Create indexes for pagination API
I have a table event (id, user_id, category_id, source_id, start_time, is_read) with 8M records. I need to list events with paging for 6 types of APIs below (each API needs 2 DB queries)
API 1
Count ...
- 145
0
votes
1
answer
21
views
Tune indices when update multiple fields on PostgreSQL
On a PostgreSQL 14.5 database, there is the following table:
CREATE TABLE IF NOT EXISTS public."Points"
(
"Key" character varying(50) COLLATE pg_catalog."default" NOT ...
- 103
0
votes
1
answer
47
views
ASC vs DESC index when querying "smaller than"
I have a table populated very often with new entries.
Records older than 2 days get deleted every few minutes with:
DELETE FROM ip_log WHERE timestamp < NOW() - INTERVAL 2 DAY
This is the only ...
- 362
-1
votes
2
answers
107
views
Are there any differences relating to query performance between >= and <= vs. Between clause?
Are there any performance-related differences between those two queries?
SELECT * FROM `v1` WHERE `siteId`=1008 AND `date`>='2022-10-01' and `date`<='2023-02-01';
SELECT * FROM `v1` WHERE `...
- 218
0
votes
1
answer
56
views
Improving the performance of STEquals
We have a table containing all polygons (geometry/geography) of the objects in our application.
This is because most polygons need to have a 'geometry' (RDNew) and a 'geography' (WGS84) version of the ...
- 119
0
votes
1
answer
34
views
Creating a partial index on non-default values and searching all rows in that index in PSQL
If I have a table where 99% of rows with column x has a default value and want to search for a specific value in that column, will creating a partial index on x provide any improvements?
For example,
...
1
vote
0
answers
83
views
Seeking clarification and recommendations on unexpected patterns in Postgresql index usage statistics
I am trying to prune unused and rarely used indexes based on the following stats:
pg_stat_user_indexes.idx_scan: Number of index scans initiated on this index
pg_stat_user_indexes.idx_tup_read: ...
- 111
4
votes
4
answers
439
views
Is there a way to disable logging / backups for view indexes?
We have a SQL Server database that uses indexed views to allow for efficient querying of some data that changes regularly (yes I know there's a cost to maintaining the index when the data changes but ...
- 143
0
votes
4
answers
1k
views
Performance issues after upgrading from MySQL 5.7 to MySQL 8.0
Recently I upgraded MySQL on my server from newest 5.7.x to newest 8.0.x and I imported my entire database with multi-indexes that I created when I was using MySQL 5.7.x. These indexes optimize SELECT ...
0
votes
1
answer
53
views
Large table with too many indexes stalls my app
PostgreSQL version: 13.10
My app has a notification feature, where users have a list of events that happened in relation to them (much like the Facebook notifications). Each notification is a row in ...
- 101
2
votes
1
answer
579
views
How can I utilize an index with a different type or collation in a LEFT JOIN in MySQL?
I am trying to debug a slow query, and when I EXPLAIN the SQL, I get a warning:
Warning: #1739 Cannot use ref access on index '[index name]' due to type or collation conversion on field '[field name]' ...
- 173
1
vote
1
answer
116
views
Query with smaller subset to scan taking exponentially longer?
I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains.
I have a query which takes almost 17 minutes to run:
select * from public.extrinsics
where chain_id = ...
- 125
1
vote
1
answer
165
views
Secondary indexes vs Using elastic search
When does it make sense to put data in elastic search vs creating secondary indexing on Primary datastore?
Elastic search with another primary store
Pros:
Primary datastore can be optimised for read ...
- 111
0
votes
1
answer
64
views
Why don't I get Index Seek for my non-clustered index
Version: Microsoft SQL Azure (RTM) - 12.0.2000.8 (Azure Managed Instance)
I'm working with a table (ActivityAttributes) that has ~190Mn rows. I've created an Index as below.
CREATE NONCLUSTERED ...
- 1
6
votes
3
answers
3k
views
Why not use two columns instead of collation for case insensitive data?
I need to store emails (like '[email protected]') in Postgres 15, be able to search them in case-insensitive manner ('[email protected]', '[email protected]', etc are the same), and be able to retrieve the ...
- 291
9
votes
4
answers
2k
views
Does creating an index and then deleting it return the database to the pre-index creation state?
I'm testing some indexes and want to do a bit of A-B testing on which indexes perform better (primarily the order of the fields in the index and additional fields specified).
I have a backup of the ...
- 193
1
vote
1
answer
120
views
Why can't three non-unique indexes be used to enforce a unique constraint?
I have a large PostgreSQL table of 3 columns, I created a separate btree index on every single column. Then, I created a multicolumn unique constraint on the table on all the 3 columns, expecting the ...
- 11
2
votes
2
answers
525
views
Index for searching in column where the column starts with the provided value
I have a table, where one column contains a substring of an url. It's always the leftmost part of the url without scheme.
id
domain
column1
column2
1
example.com
value1
value2
2
example.org
value3
...
- 123
2
votes
1
answer
721
views
Add composite primary key on existing PostgreSQL table
I have a table on PostgresSQL with millions of rows. The table has columns a and b. I already have a unique index on a and b:
CONSTRAINT a_b UNIQUE (a, b)
I want to add a composite primary key on ...
- 123
1
vote
1
answer
402
views
Why my clustered index causing deadlock (Keylocks and Pagelocks)
We are experiencing deadlock 2-3 times a day in SQL Server. Upon analysis, we have found that the deadlock is caused by a combination of keylock and pagelock occurring simultaneously.
We are actually ...
3
votes
1
answer
199
views
Does simple key value storage on MySQL/MariaDB requires index on key?
I was exchanging with another developer regarding the performance of SELECT queries on a simple key/value table.
CREATE TABLE cache (
id BIGINT unsigned not null auto_increment,
...
- 133
2
votes
3
answers
576
views
EXPLAIN ANALYZE does not show what takes time with my GIN index scan
Context
I have a table named companies_establishments that holds ~33M rows.
I created a GIN index with trigrams, so I can make LIKE queries much faster.
CREATE INDEX companies_establishments_id_index ...
- 123
0
votes
1
answer
90
views
MariaDB: Using composite primary key and index on the same columns gives strange results
Environment information:
Windows 11
10.6.8-MariaDB
In SO https://stackoverflow.com/questions/20055644/mysql-counter-within-group
I asked how can I add a counter within a group. It works quite well ...
- 257
0
votes
1
answer
44
views
Should you be liberal with create index and add to many "identifier" columns?
After reading about performance a bit in sql I notice that it is quite often suggested to add indices for foreign keys - as often data is looked up through the foreign key. Similarly PostgreSQL seems ...
- 455
0
votes
1
answer
52
views
MariaDB: defective MyISAM and ARIA Indexes after errorless import of big data file
I have a long suffering with this issue after moving forward from MySQL 5.6 and then MariaDB 10.0.
Working now on Debian 11 (Bullseye) with MariaDB "Ver 15.1 Distrib 10.9.5-MariaDB, for debian-...
- 101
2
votes
0
answers
300
views
Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?
I'm currently using Postgres 12.
A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
- 21
0
votes
1
answer
547
views
How to avoid 100% CPU on CREATE INDEX
I'm indexing a partitioned table with about 200 million rows, each row is ~1kb in size.
I run multiple CREATE INDEX statements sequentially and its pegging the CPU at 100% for 2 hours. Is there a way ...
- 103
0
votes
0
answers
108
views
SQL Server: Spatial Indexing and Bounding Box
I have two tables each with a SHAPE Geom Column, and I'm working on getting the intersected area between every shape in the first table with the second table.
The second table has millions of rows, ...
- 101
0
votes
1
answer
42
views
How to see the false positive cases with bloom index in Postgres?
I have tried to reproduce a false positive but it is almost imposible to check for an example in my database. Thats what I did.
First create the index
CREATE EXTENSION bloom;
Then create the table ...
- 1
-1
votes
1
answer
154
views
Best indexing strategy for composite primary key with frequently queried columns and occasional additional column [closed]
I have a large table with the following composite primary key:
PRIMARY KEY(col1, col2, col3)
col1, col2, col3 are queried all the time.
Some times (maybe 10% of the time) I get queries for col1, col2,...
- 99
1
vote
1
answer
222
views
How can set the column as a primary key with serial type?
In my postgresql:
select index from sample;
index
---------
1
2
3
1
2
How can set the column index as a primary key with serial type for table sample?And the selected value should be as below:
select ...
- 284
1
vote
2
answers
467
views
MySQL is not using index on query plan with Order By and Limit
I'm trying to understand the behaviour of the MySQL query planner where an index is not used when a query includes Order By and Limit combined.
The table in question is big with around 8 millons of ...
- 11
1
vote
1
answer
169
views
Which version of Stratio Lucene Index should we use with Cassandra 4.x?
We are using Stratio Lucene Indexer for V3.11.5 and currently planning to upgrade to V4.x.
As specific Cassandra Lucene index versions are targeted to specific Apache Cassandra versions.
https://...
- 161
0
votes
0
answers
79
views
MongoDB Index Size Growth on updateMany()
When I update an indexed field, the size of the indexed field changes drastically. What's the reason behind this? (Indexed fields are 'result' and 'address.city' and they both have the same value in ...
1
vote
1
answer
97
views
InnoDB - how MySQL InnoDB pages works with underlying OS file system
I've studied and summarized how MySQL InnoDB pages works with underlying OS file system when accessing secondary storage.
Would you mind to confirm that my understanding is correct?
InnoDB store ...
- 113
1
vote
1
answer
161
views
Oracle query taking a very long time to execute
I am trying to run a couple of reconciliation queries to identify missed deletes from our change data capture tool, but I need it to run relatively fast...
My source and target tables are identical ...
- 69
0
votes
1
answer
85
views
How "trustworthy" is the cost information returned by EXPLAIN in Postgres?
I have a database with a table which is accessed with about 5 different SELECT commands. I tested each one of them with the EXPLAIN ... and EXPLAIN ANALYZE .... The database includes some data (about ...
- 125
0
votes
1
answer
348
views
Postgresql perform sequential scan instead of index scan for indexed column
I'm currently using PostgreSQL 14.0 for windows
My target table has a 900k+ rows and my query is using single column filter (status_id).
The column status_id is indexed via btree (default)
When i ...
1
vote
1
answer
127
views
Create non clustered indexes in background without awaiting the outcome in Sql Server
I have to release a new version of an application. I created two scripts one to move the schema and one to migrate the data. The script contains a long list of queries.
The application needs to stay ...
- 303