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,364
questions
3
votes
1
answer
174
views
+50
Should tiny dimension tables be considered for row or page compression on servers with ample CPU room?
An old Microsoft paper says to consider using ROW compression by default if you have lots of CPU room (emphasis mine).
If row compression results in space savings and the system can accommodate a 10 ...
- 645
3
votes
2
answers
437
views
Is there any benefit to declaring an index that contains a primary key as unique?
Suppose that I have a table with many columns that I don't care about, but two that I do: Primary and Secondary. There is a clustered primary key on Primary.
CREATE TABLE [dbo].[...
- 645
0
votes
0
answers
31
views
Are multi column indexes for JSONB columns better?
we are using PostgreSQL and we have a table we are using JSONB due to the unstructured nature of the data. We are integrating with a ton of different CRMs, so a part of the records data is pretty ...
0
votes
0
answers
24
views
Why isn't the datetime index used?
I've a big log table with about ~60m records for the past 2-3 years. The structure is like this:
CREATE TABLE `dmsserver_status_log` (
`id` bigint(20) NOT NULL,
`customer_id` int(11) NOT NULL,
`...
- 131
0
votes
1
answer
44
views
Huge index size on table
I have a large index on a specific table, and I wonder if it's better to drop and recreate it or use the REINDEX command. The table's total size is 322GB, with an index size of 166GB and an actual ...
- 35
0
votes
0
answers
39
views
Index and Statistics Maintenance Scheduling Questions
I'm working on setting up a maintenance plans for some SQL Server instances. I'm trying to find a good/safe staring point to start the maintenance plan on and I can adjust and make appropriate changes ...
- 1
0
votes
1
answer
54
views
postgres drop/create unique index concurrently
I have trouble understanding two limitations for postgres when it comes to create indexes and dropping them in concurrently mode:
the first about CREATE INDEX CONCURRENTLY:
Regular index builds ...
0
votes
1
answer
29
views
How to Reorder Primary Key Constraint on a Large TimescaleDB Table Without Downtime?
I am trying to reorder the primary key constraint on sampleTable to improve query performance.
Here are the details:
Database: PostgreSQL (12.18), TimescaleDB (1.7.5)
Table Name: sampleTable
DDL:
...
- 13
0
votes
1
answer
20
views
MongoDB index not used depending on query formulation
We see in one of our services that certain queries always result in a COLLSCAN, instead of the appropriate index being used. The data has the form
{_id: DocumentID, "field": {"child&...
- 5
-2
votes
1
answer
67
views
Does REORGANIZE compact pages and respect fill factor?
I've always been told that REORGANIZE does nothing to fix poor page fullness because all that it does is reorder pages on disk. To investigate this, I read the documentation. However, I've found it ...
- 645
-1
votes
2
answers
49
views
Single Primary Key vs. Composite Primary Key to store user isolated data
Let's say I want to store user provided data in a table called post.
users data are isolated by user_id column
select sum(likes_count) where user_id = $ should be fast
select * from post where id = $ ...
- 3
1
vote
0
answers
23
views
autovacuum, vacuum, analyize reset table index usage counters?
I am trying to get rid of unused indexes. The index usage is very much depends on the output of pg_stat_user_indexes.idx_scan (correct me if i am wrong). Just wondering what are the impacts of ...
toanwa
1
vote
1
answer
61
views
Does inserting new data into a sorted clustered columnstore index in SQL Server disrupt the order?
I'm working with clustered columnstore indexes in SQL Server and have a question about maintaining data order.
I understand that by default, clustered columnstore indexes don't sort data. I've seen a ...
- 2,042
1
vote
1
answer
76
views
When is it safe to drop redundant or duplicated indexes on AWS RDS MySQL?
To identify redundant and duplicate indexes while ignoring primary and foreign keys and checking their cardinality. What is the best practice for dropping indexes on AWS RDS MySQL databases in the AWS ...
1
vote
1
answer
61
views
Impact of Reordering Composite Index on PostgreSQL: Performance, Space, and Overhead Considerations
I am optimizing SQL query performance by reordering a composite index in PostgreSQL. I need to understand potential repercussions, including space usage, data saving overhead, and any other impacts of ...
- 13
1
vote
1
answer
63
views
MySQL bug -- MySql is choosing incorrect indexes
This is the last update to this very LONG post.
Problem Summary:
In a database containing a field for datetime and a field for milliseconds (and indexes for both), MySQL picks an index of milliseconds ...
- 111
0
votes
1
answer
33
views
Index use after unwind in aggregate
We have a collection in our MongoDB where due to a changed use-case a so-far small query is now really slow. The aggregate in question is somewhat like this:
db.collection.aggregate([
{
$match: {...
- 5
2
votes
3
answers
93
views
Best way to test index changes outside of production
We have a massive MS SQL Server production environment with thousands of tables in dire need of index tuning & pruning. Any suggestions on the best ways to test proposed changes outside of ...
- 175
0
votes
0
answers
21
views
How to Create a Case-Insensitive Search Index for Multiple Fields in MongoDB Atlas Search
I'm working with a MongoDB collection named skus and need help creating a search index and query for case-insensitive search across multiple fields with priority. Here's a sample document from the ...
0
votes
1
answer
56
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%'...
- 115
0
votes
1
answer
30
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 "...
- 539
0
votes
1
answer
54
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?
...
- 333
2
votes
2
answers
103
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 ...
0
votes
0
answers
62
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 ...
- 581
1
vote
1
answer
61
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 ...
- 341
1
vote
1
answer
90
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 ...
- 163
0
votes
1
answer
46
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....
- 101
1
vote
1
answer
81
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 ...
- 111
0
votes
1
answer
34
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 ...
9
votes
1
answer
742
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 ...
elahren
0
votes
1
answer
29
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 ...
- 101
0
votes
1
answer
62
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 ...
- 73
0
votes
2
answers
56
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 ...
- 121
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 (...
- 111
3
votes
3
answers
482
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 ...
- 141
1
vote
1
answer
99
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,
`...
- 131
0
votes
1
answer
22
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 ...
- 103
0
votes
0
answers
20
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 ...
- 101
0
votes
3
answers
72
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 ...
- 121
1
vote
1
answer
84
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 ...
- 203
0
votes
1
answer
56
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 ...
0
votes
1
answer
67
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 ...
- 1
2
votes
1
answer
116
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 ...
- 21
0
votes
1
answer
32
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 ...
- 3
0
votes
0
answers
53
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 ...
0
votes
1
answer
25
views
SASI support in Cassandra 3.11
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 ...
0
votes
1
answer
45
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, ...
- 265
0
votes
1
answer
106
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.)...
- 133
0
votes
1
answer
62
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 ...
- 429
0
votes
0
answers
41
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 ...
- 1