All Questions
3,388 questions
1
vote
1
answer
27
views
An index that covers both an ARRAY column and sorting by another column in postgres?
Let's say I have a table with an array column and an integer column:
CREATE TABLE records(
score INTEGER;
features INTEGER[];
);
Now I want to make queries that extract rows that have at least ...
0
votes
2
answers
46
views
Is it good practice to index a large text column (up to 800KB) for full-text search in PostgreSQL?
I'm working on a PostgreSQL database for a blogging platform, and one of the columns in my table, blog_content, can hold text up to 800KB per row. This column stores the main content of blog posts, ...
0
votes
0
answers
29
views
Why am I getting an INDEX SKIP SCAN?
Scenario is as follows:
Table T is created as of
CREATE TABLE T
(
f_a ...
,f_b ...
,f_c ...
,ctv NUMBER(*,0) NOT NULL
,ffc DATE NOT NULL
,v NUMBER(1,0)
,uc NUMBER(1,...
- 123
2
votes
2
answers
129
views
What is the practical use case for a very large (terabyte-scale) heap?
This question is the highest rated question on this website about heaps. It asks about the use case of heaps. However, I believe that very large heaps (e.g. thousands of gigabytes) are a special case ...
- 799
1
vote
2
answers
77
views
When is a regular index scan faster than a bitmap index + heap scan in Postgres?
So as far as I understand, with a regular index scan (not index-only scan), Postgres will read the index and immediately fetch the referenced rows from the heap. For a bitmap index scan + bitmap heap ...
- 121
1
vote
1
answer
35
views
How can indices in PostgreSQL be so space efficient?
I have a PostgreSQL 15 deployment that contains a partitioned table in the order of tens of millions of records.
I've been playing around with index creation and I'm surprised by how little space a ...
0
votes
1
answer
27
views
MySQL: How to optimize a JOIN on a primary key with Indexes
I am looking to optimize a JOIN between tables that uses a small subset of columns in the second table.
CREATE TABLE `employees` (
`employee_id` bigint NOT NULL,
`manager_id` bigint NOT NULL,
`...
0
votes
1
answer
51
views
How to use sp_rename for an index with a target name that is the same in multiple tables
I am exploring the sp_rename procedure in MSSQL based on input provided in an answer to another question.
I have some 50 tables that has been created with a UID column having a random unique ...
4
votes
2
answers
288
views
How can I create a partitioned table with the addition of a unique index in SQL Server?
I am creating a partitioned table called TestArticles, specifying several filegroups according to the year of their publication (publishDate). This code (excluding the commented parts) executes ...
- 43
1
vote
1
answer
68
views
SQL Server: What benefit does a non-unique index have versus a unique index containing the primary key as the last index column? [duplicate]
We have an application server (written by us) which consumes a "model" and provides most of of the database commissioning work (creating tables, indexes, etc). When constructing indexes, it ...
- 49
0
votes
0
answers
20
views
Prefixed indexes in Oracle
I know it is not logical to have 2 indexes when one is prefix of another one, I mean if I have following indexes:
create index ix_vch_plc on tb_voucher(fk_plc);
create index ix_vch_plc2 on tb_voucher(...
- 189
0
votes
2
answers
88
views
Postgres SQL does full table scan despite indexing - why? How to improve performance in any other way?
I have a table that has a date value in column A and other columns B,C,D. I wanted to update the values of new column E based on the values of B,C,D for a given time period from A. It was slow so I ...
- 1
1
vote
2
answers
62
views
Optimize Postgres query for finding string collisions
For a system we're building, we store discount codes as strings in a Postgres table. We have a system where we support multiple workspaces that share the same database, and have a special value ('*') ...
- 131
0
votes
2
answers
50
views
How to have a covering index used for the updates?
I have a db table with"
id bigint NOT NULL AUTO_INCREMENT (primary key)
customer_ref varchar(64) NOT NULL
customer_counter int NOT NULL
description varchar(255) NOT NULL
I have also the ...
- 123
4
votes
1
answer
577
views
Does compressing indexes reduce the size of already compressed backups?
I use backup compression everywhere. Recently, I discovered that my backups were getting a little too big. I decided to fix this by compressing the indexes in the database (typically PAGE compression)....
- 799
2
votes
2
answers
148
views
How to predict whether a query plan will involve the use of an index?
I am using the latest PostgreSQL docker image to create a local DB (on an Apple M1 Pro - MacOS Sonoma 14.5 machine). I create a table table0 with a single column col0 within it, and fill it with ...
- 169
2
votes
1
answer
97
views
Query plan seems to depend on time interval between query and table setup?
I am working with PostgreSQL 17. As an example, I have a table table0 with a column col0, with randomly generated strings as GIN-indexed values. I use the following setup.sql to create such a table:
...
- 169
2
votes
1
answer
115
views
How to properly create a Spatial index and write a SARGable query that leverages that Spatial index?
I can't seem to get SQL Server to leverage a basic Spatial index on a GEOGRAPHY object for even the simplest of queries.
Here is a dbfiddle.uk repro which demonstrates creating a simple table with a ...
- 39.5k
2
votes
1
answer
123
views
What should I do so returning 200 rows is at least as fast as returning 1000 rows when performing partial match queries on an indexed column?
I have a PostgreSQL 17 table table0. It has a single column col0 containing random strings of 2 to 16 characters. I would like to perform partial match queries, hence I created a GIN index with ...
- 169
2
votes
1
answer
58
views
WHERE A=x DISTINCT ON (B), with a composite index on (A, B, C)
I have huge table with a composite index on (A, B, C).
-- psql (13.16 (Debian 13.16-0+deb11u1), server 14.12)
\d index_a_b_c
Index "public.index_a_b_c"
Column | Type ...
5
votes
4
answers
838
views
MySQL using a multi-column index even when the first column isn't being queried
I have MySQL version 8.0.37.
From what I understand about a multi-column index in this version, it will be used by MySQL ONLY if the query contains a subset of all the columns, starting from the first....
- 203
0
votes
0
answers
29
views
What is the process for creating a partial index for an email field when email field is not present or as empty field in MongoDB?
I'm using MongoDB and want to create a partial index on the email field, but I only want it to apply to documents where the email field exists (i.e., email: { $exists: true }). How can I create this ...
0
votes
1
answer
57
views
Can an index be redefined prior to a rebuild?
Suppose that my server has a job that sometimes rebuilds indexes based on factors that I don't control (e.g. fragmentation). Suppose also that I am sure that it will one day rebuild an index that I'm ...
- 799
0
votes
1
answer
71
views
Frequent deadlocks in sql server [closed]
Getting frequent deadlock in this case which i have attached
user306367
0
votes
0
answers
34
views
Wal-G an indexes
I'm migrating a PostgreSQL 15 database using wal-g.
After migration, everything seems to work as expected except for a unique constraint that's not being honored until I rebuild the index.
The ...
- 1
3
votes
1
answer
196
views
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 ...
- 799
3
votes
2
answers
470
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].[...
- 799
0
votes
0
answers
44
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
29
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
1
vote
1
answer
51
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 ...
- 45
0
votes
0
answers
48
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
98
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
51
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
23
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
70
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 ...
- 799
-1
votes
2
answers
56
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
0
votes
0
answers
33
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
77
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,062
1
vote
1
answer
85
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
69
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
96
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
46
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
117
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 ...
- 177
0
votes
0
answers
35
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
80
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
33
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 "...
- 559
0
votes
1
answer
58
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
111
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
76
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
71
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 ...
- 393