New answers tagged index
0
votes
Large table with too many indexes stalls my app
Eitan Blumin has recently released Drop All Redundant Indexes In Every Database All At Once
It's a 300 lines of code script and you have to port it from SQL Server to PostgreSQL.
There is one less ...
- 1,353
0
votes
Performance issues after upgrading from MySQL 5.7 to MySQL 8.0
You need to provide more information, such as table and index definition, the slow query and the execution plans on 5.7 and 8.0, statistics of tables and columns referenced in predicates.
- 26
0
votes
How can I utilize an index with a different type or collation in a LEFT JOIN in MySQL?
In MySQL 5.7, you can create a generated column and add an index to it. The only difference between this an MySQL 8.0 is that 8.0 allows you to create an index on an expression without adding the ...
- 12.2k
0
votes
MariaDB: Using composite primary key and index on the same columns gives strange results
MySQL is deprecating the assigning of @variables in SELECTs.
MariaDB will probably follow suit at some point.
See http://mysql.rjweb.org/doc.php/myisam2innodb#index_issue_2_column_pk
for workarounds.
- 73.8k
1
vote
Accepted
Query with smaller subset to scan taking exponentially longer?
The slow plan uses an index on just (block_number), and filters 11 million rows. That's expensive nonsense, and should not occur while a more fitting index on (chain_id, block_number) exists. Unless ...
- 163k
1
vote
Secondary indexes vs Using elastic search
When does it make sense to put data in elastic search vs creating secondary indexing on Primary datastore?
These things are like comparing apples to oranges.
Use Elasticsearch when your primary use ...
- 30.6k
0
votes
Are 'Functional Indexes' Supported in MS SQL Server?
You could emulate functional indexes via computed columns:
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/indexes-on-computed-columns
There are few restrictions:
All functions ...
- 457
0
votes
Filter by list of values pairs
adjust you query for:
select count(*) from optprice
left join prod on optprice.prod = prod.id
where price between 10 and 500 limit 20;
and create a index optprice.price
I believe that performance ...
- 1
1
vote
Why don't I get Index Seek for my non-clustered index
Seeks only happen on Key fields [Activity_Id], Include (covering) columns are used to minimise Key/Rid lookups.
This index will give you a Seek (Range Seek because it's non-unique):
CREATE ...
- 1,088
6
votes
Why not use two columns instead of collation for case insensitive data?
I did some testing with all approaches plus the case-sensitive one as a baseline by inserting 1,000,000 entries to each table with random strings of random case of equal length, and here are the ...
- 291
4
votes
Does creating an index and then deleting it return the database to the pre-index creation state?
There are some small pitfalls that you will have to pay attention to, because it depends on the indexes you are creating and/or dropping.
Along Come Clustered Indexes
There are tips out there that ...
- 16.3k
7
votes
Does creating an index and then deleting it return the database to the pre-index creation state?
Alternative
For this sort of testing, I like to use Database Snapshots.
In a testing environment, it can be useful when repeatedly running a test protocol for the database to contain identical data ...
- 78.8k
11
votes
Why not use two columns instead of collation for case insensitive data?
Third option:
CREATE TABLE test
(
original_email TEXT NOT NULL
);
CREATE UNIQUE INDEX test_email_uniq on test(lower(original_email));
INSERT INTO test (original_email)
VALUES ('IVAN@email.com');
...
- 2,394
7
votes
Why not use two columns instead of collation for case insensitive data?
The downsides of using a generated column are the wasted space and the processing time it takes to calculate the computed column whenever you modify the row. On the other hand, the index would be ...
- 40.7k
9
votes
Accepted
Does creating an index and then deleting it return the database to the pre-index creation state?
Longer
Index changes (creates, rebuilds, drops, etc) on a table do invalidate any relevant plans in the plan cache and cause a new plan to be recompiled the next time the related query is executed.
It ...
- 30.6k
5
votes
Does creating an index and then deleting it return the database to the pre-index creation state?
short
Dropping the index is sufficient for resetting the table to its prior state. If you disable the index, statistics created for it may still be used for cardinality estimation.
- 36.8k
1
vote
Why can't three non-unique indexes be used to enforce a unique constraint?
A multi-column unique constraint can only be enforced by keeping track of every combination of values, as that is what a multi-column constraint represents.
I created a separate btree index on every ...
- 8,940
3
votes
Index for searching in column where the column starts with the provided value
It seems like all possible substrings of your input are delimited with the character /. To illustrate my point:
SELECT string_agg(part, '/') OVER (ORDER BY ord) AS prefix
FROM string_to_table('...
- 163k
2
votes
Accepted
Index for searching in column where the column starts with the provided value
Q: SELECT id FROM table WHERE 'example.org/sub/sub/sub/test.html' LIKE (domain || '%') which gives what I want, but it always does a seq
scan of the table, even though that I have an index on the ...
- 29.2k
1
vote
Accepted
Add composite primary key on existing PostgreSQL table
Will there be any downtime?
While CREATE INDEX only blocks concurrent writes and still allows reads, you need ALTER TABLE to add the PRIMARY KEY constraint, and that takes an ACCESS EXCLUSIVE lock on ...
- 163k
2
votes
Accepted
Why my clustered index causing deadlock (Keylocks and Pagelocks)
most likely
You have a read query deadlocking with a write query, which means one or both query plans have one or more key lookups in them.
<owner-list>
<owner id="...
- 36.8k
5
votes
Accepted
Does simple key value storage on MySQL/MariaDB requires index on key?
Without an index, you will get a full table scan every time. Glad you indexed the table.
I would recommend you make a compound index for every better performance as the table grows:
ALTER TABLE cache ...
- 178k
2
votes
EXPLAIN ANALYZE does not show what takes time with my GIN index scan
If the wildcard is always at the end of the pattern like in your example, then you can use just a btree index scan to handle it efficiently. But you will need to build the index using ...
- 35.6k
3
votes
EXPLAIN ANALYZE does not show what takes time with my GIN index scan
The trigram index had to search through lots of data, probably because the trigrams 502, 022, 221 and so on occur frequently. Part of the problem might be a long GIN pending list. See if VACUUM on the ...
- 40.7k
2
votes
Accepted
EXPLAIN ANALYZE does not show what takes time with my GIN index scan
Your question is a duplicate of this one, but perhaps it's worth an explanation specific to Bitmap Index Scan.
The numbers in the explain output: actual time=157.625..157.626, as indicated in the ...
- 23.9k
0
votes
Should you be liberal with create index and add to many "identifier" columns?
As this key will be the main entry point of lookups, there should be a index on that column, right?
Correct, and it ought to be unique as well.
... administrative functionality on users. This is ...
- 7,601
1
vote
Accepted
How to avoid 100% CPU on CREATE INDEX
To reduce the CPU consumption of CREATE INDEX, the most effective way is to disable parallel query by setting max_parallel_maintenance_workers = 0. Then CREATE INDEX will only use a single CPU core.
...
- 40.7k
0
votes
MariaDB: defective MyISAM and ARIA Indexes after errorless import of big data file
Will you be having a mixture of MyISAM and InnoDB? I see a big buffer_pool (for InnoDB), but no setting for key_buffer_size, which is needed for MyISAM.
How much RAM do you have?
Advice on memory ...
- 73.8k
1
vote
ERROR: data type text[] has no default operator class for access method "gist"
Another option would be to create the index converting the column to ts_vector, this way you will not need to install smlar.
create index on bar using gist(array_to_tsvector(f));
When using the index,...
- 111
1
vote
How to see the false positive cases with bloom index in Postgres?
You will never get false positive results, since they are removed by the Index Recheck during the Bitmap Heap Scan phase. If you want to know how many false positives the Bitmap Index Scan phase ...
- 40.7k
1
vote
Best indexing strategy for composite primary key with frequently queried columns and occasional additional column
No, it generally makes no sense to do so.
The primary key is itself unique, so a lookup on col1, col2, col3 already gets a unique row.
In MySQL/InnoDB, a primary key is clustered, so col4 is already ...
- 8,940
Top 50 recent answers are included