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,346
questions
0
votes
1
answer
27
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
46
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?
...
- 323
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 ...
0
votes
0
answers
46
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
51
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 ...
- 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 ...
- 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....
- 101
1
vote
1
answer
64
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
17
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
575
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
25
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
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 ...
- 73
0
votes
2
answers
32
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
466
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