The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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.

Filter by
Sorted by
Tagged with
0
votes
0answers
28 views

FTS is slower on tsvector than text

I tested the performance of searches on two columns : text, tsvector. Somehow it appears that tsvector is slower than text. Also, there's some incosistency with planning time and execution time. It ...
1
vote
0answers
68 views
+50

indexing reference to multiple tables based on type and cascading them

I have a rather simple "social network" scenario , with Posts (which can include Media, like photos), and likes & comments. Post can have Likes and Comments by different users. Post can have ...
1
vote
1answer
25 views

composite index on bigints

I have a User Relations table, which basically stores all the one-directional relations of users. For example , User A follows user B ; User X blocks User Y User ids: the id of users are bigints. It'...
0
votes
1answer
12 views

How to optimize count(*) using only foreign key index on PostgreSQL

I have a web app that extensively uses data tables that connect to the database to fetch data. The problem though, it always needs first to count the total results before it could paginate the data by ...
1
vote
1answer
30 views

Best Indexes For Group By + Join + Where Condition

I have a database that includes the tables shown below: CREATE TABLE metadata ( keyword_id bigint NOT NULL, campaign_id bigint NOT NULL, org_id bigint NOT NULL, keyword character ...
0
votes
0answers
25 views

When does setting the SORT_IN_TEMPDB option actually improve performance? [duplicate]

My question is related to index rebuilding, mainly the SORT_IN_TEMPDB option. BOL states that: Even if other users are using the database and accessing separate disk addresses, the overall ...
1
vote
1answer
81 views

Do I need to update an index include columns every time I add a new column

I work on a team where we have millions of records of data, but not 100s of millions anything crazy. We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
1
vote
1answer
123 views

SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week

I have a very simple query INSERT INTO #tmptbl SELECT TOP 50 CommentID --this is primary key FROM Comments WITH(NOLOCK) WHERE UserID=@UserID ORDER BY CommentID DESC I have a simple index on the ...
0
votes
1answer
30 views

Multiple vs Single COUNT on same col + Multiple vs Single col index

I have two very simple questions 1. Multiple vs Single COUNT on same col I need to perform CASE on an aggregate column like this: SELECT tbl_students.school, COUNT(tbl_students.id) as students, ( ...
0
votes
0answers
59 views

ORACLE MULTIPLE TABLE INDEXES ON GROUP BY

I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1. ...
1
vote
2answers
33 views

Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. ...
2
votes
1answer
61 views

Understanding when to remove Order By or Sort operator from Query/plan

While i was reading and per understanding one needs to avoid unwanted ORDER BY in SQL queries if matching index to support query has key columns sorted the same way. For below DB test schema-- ...
1
vote
1answer
26 views

Should I add my index after the table is populated or before (MySQL. Aurora)

I know this has been asked in the past, but sometimes things change over time and I wanted to double check. I have a table with about 9 Billion rows. Should I add the index before inserting the data ...
2
votes
1answer
78 views

Rebuilt index quickly becomes fragmented - Fragmentation going down to 0, back to 99% in 10 minutes [closed]

I have some index with very high fragmentation, 85-100% very soon after an index rebuild has brought it to 0%. The only clue I have is that it may not work if the data file is fragmented on disk and ...
0
votes
1answer
53 views

How to improve performance for table JOINS involving millions of rows

I am dealing with a performance issue for a very simple structure involving simple queries. Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary ...
0
votes
0answers
15 views

How should I add index for this query

I have a stored procedure as follows, how should I add index for it?CREATE DEFINER=`root`@`%` PROCEDURE `SelectUsage4`( IN p_ids MEDIUMTEXT , IN p_locationIDs MEDIUMTEXT , IN ...
0
votes
0answers
41 views

IndexMaintenance fails on error

We are using the Index Maintenance of Ola Hallengren (https://ola.hallengren.com) solution for a couple of years now. While setting up some new SQL servers we are seeing some strange behaviour in the ...
1
vote
1answer
39 views

Improve query performance of subquery with MAX()

I am looking to migrate our support database from MS SQL Server to Postgres and most of it has gone OK. However, some of the queries seem to be slower on PG than on the SQL Server. We have a ticket ...
0
votes
3answers
49 views

How to free used space from disabled index?

I've come across a need to free used space from a large number of disabled indexes. Is it possible to make SQL Server release the used space from these indexes without a drop/create for each index? ...
-1
votes
1answer
23 views

trigram index for integer field

I am trying to optimize query with the clause: WHERE LOWER(CAST(\"my_table\".\"id\" as TEXT) LIKE '%456%' I decided to create a trigram index for it. CREATE INDEX index_name ON my_table USING gin (...
0
votes
0answers
40 views

Duplicated values found in unique indexed column

I have a database table with an unique index idx_1(USER_ID, COURSE_ID), however I still found some entries with the same USER_ID and COURSE_ID (both not null). And the other columns of the duplicated ...
-3
votes
1answer
33 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
0
votes
2answers
66 views

Help error message inside dynamic T-SQL

I have a requirement to come up with reporting for indexes for our whole production environment: I found the below script online and modified it according to my requirement and trying to execute it ...
1
vote
2answers
39 views

MYSQL: How to optimize a date sorted table with a triple primary key index

I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began ...
1
vote
0answers
57 views

oracle 11g select distinct issue

I have a big table (61mil rows) that has an indexed not-null column ID. I want to select the distinct values of that column. I expected the select distinct id from bigtable ; to run fast, making ...
0
votes
1answer
20 views

show extended index equivalent for MySQL 5.7

If you look at this documentation for show index in version 8.0, it says SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] The ...
0
votes
2answers
49 views

Why is Primary Key not present at the end of Unique Key in MySQL?

I have a table with following definition. CREATE TABLE `test` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, `e` int(11) DEFAULT NULL, ...
3
votes
1answer
84 views

Why is a Postgres 11 hash index so large?

Postgres 11.4 on RDS and 11.5 at home. I'm looking at hash indexes more closely today because I'm having problems with a citext index being ignored. And I find that I don't understand why a hash ...
4
votes
3answers
78 views

Expression index on a citext column ignored, why?

Running on RDS with about 32M rows. PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit Also testing locally on macOS with about 8M rows. ...
0
votes
1answer
10 views

Changing POSTGRES column datatype - should I drop indexes and then re-create?

I have a number of postgres tables with a column of type INT which I need to change to BIGINT. Each partition of the table contains about 100 million rows, my intention is to detach the individual ...
0
votes
2answers
73 views

Proper way to add new column in database

What is the proper approach when we add a new column into an existing table? For example, I already have columns like, Foo1, Foo2, Bar1, Bar2. Now I want to add a new column called Foo3. What is ...
2
votes
0answers
38 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
2
votes
1answer
68 views

Range Extraction Algorithm for Muti-Part aka Composite Indexes in MySQL

I went through Range Extraction Documentation for MySQL Indexes. They have explained for single column indices. What is the procedure to reduce complex multi column (at least two) criteria? What I am ...
0
votes
1answer
64 views

SQL Server Non Clustered Index Compression

I'm struggling to find out the answer, we currently use compression on all our data warehouse tables, which has improved performance and also greatly reduced the storage space used. I've only ...
1
vote
1answer
34 views

How to make Postgres Multi-Column and Multi-Table Search More Efficient

I have a Shipment table with some basic data about a shipment and a ShipmentItem table which contains additional attributes about that shipment with a foreignKey on the Shipment table's primaryKey. ...
2
votes
1answer
42 views

Adding indexes to production MySQL database without downtime?

I am looking to improve the performance of queries on a large MySQL database that is running in production by adding proper indexes. We have tried to add the indexes on the live database, but of ...
2
votes
1answer
25 views

Creating a view (or table) from JSON data with two different shapes

I'm pulling in JSON data from two different sources that have roughly the same information, but in different shapes. Let's say I'm pulling in data about music albums, and one of the API responses ...
0
votes
1answer
57 views

sqlite - very slow performance of WHERE EXISTS without index on EXISTS

I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause. For example: UPDATE tbl1 SET col1 = "Y" WHERE EXISTS (SELECT ...
0
votes
0answers
21 views

Matching Information in specific example

I am new to database building and have been fighting with a certain query problem for a couple of days now. All I need really is how this kind of Problem is called, but I would appreciate a longer ...
2
votes
1answer
103 views

indexes are not created on localdb

When using localdb and executing this, when browsing the database in sql server object explorer (in visual studio), the index is not created. I have to manually create it to make it appears. Why ? ...
1
vote
1answer
49 views

Why Index-Only-Scan's COST can be higher than Seq-Scan

sql> create table index_test (col1 integer, col2 integer default 0); sql> do $$ begin for i in 1..100000 loop insert into index_test (col1) values(i); end loop; end; $$; sql> ...
2
votes
3answers
26 views

Postgresql Operator Class for index support of custom operator

I'm trying to create an index that will support queries that use my custom operator. This is on Postgresql 10.4 The custom operator I followed the tips in this SO answer to create an operator that ...
1
vote
1answer
31 views

How to get same value in a unique column but differentiated by other column values?

I have a table like this ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID --------------------------------------------------- 1 | VITAMIN A | 1 | 1 2 | PARACETAMOL | ...
4
votes
2answers
237 views

GIN index ignored when query has many (more than 18) OR statements

I have an issue where the execution is not using a GIN index when going over 18 OR statements. This is my index: CREATE INDEX emailjobs_externalreferences_gin ON vsko_mailer_api_prod.emailjobs USING ...
2
votes
0answers
24 views

Querying a High Cardinality Field

I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
0
votes
1answer
24 views

Does innodb guarantee unique data in unique indexes even with UNIQUE_CHECKS=0?

We have a suspicion that sometimes there could be non-unique data even with a UNIQUE index. Question is, can we rely on InnoDB guaranteeing that there won't ever be non-unique data, even in the ...
5
votes
1answer
47 views

Bad execution plan when having partial index and big In-clause in Postgres

Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements. Given the following ...
4
votes
1answer
68 views

How to recreate an Index with the same settings?

I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same. I do not have the ...
7
votes
2answers
362 views

Force SQL Server to use fragmented indexes?

I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index ...
2
votes
1answer
39 views

Is searching faster if indexed values are the same?

Currently I have a collection events with an attribute createdAt of type date (for example 2019/09/09 10:32:12.123). Because my users search a lot by date, I added an index on this attribute. There ...