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
2
votes
1answer
96 views

Mysql GROUP BY is really slow on a simple view

I have these tables. CREATE TABLE `movements` ( `movementId` mediumint(8) UNSIGNED NOT NULL, `movementType` tinyint(3) UNSIGNED NOT NULL, `deleted` tinyint(1) UNSIGNED NOT NULL ) ENGINE=InnoDB ...
0
votes
0answers
23 views

This is a question about hash index. I'm not sure about the correct answer

I think the answer of the first question is 2^(n-na) and the second question's answer is p*2^(n-na) + (1-p)*2^na. is it correct? But what about the third question?
-1
votes
2answers
43 views

Sql Server Standard 2014: Reindex job locked by other transactions

On a customer of ours we have Microsoft SQL Server 2014 - 12.0.4100.1 (X64) installed. It's a heavy transactions schema (especially during nights) and we have a scheduled REINDEX maintenance job that ...
1
vote
1answer
20 views

Mysql wrong use of index when a date interval is big

I have a query that have this statement: SELECT DISTINCT C.id_produto FROM movdiario C INNER JOIN PRODUTO P ON C.ID_PRODUTO = P.CODITEM INNER JOIN estoques B ON B.id_produto=C.id_produto ...
1
vote
2answers
19 views

Postgres doesn't use expression index for group by

I'm doing a simple group by on a single column with a matching index and it works fine: create index on contacts (home_city); select home_city, count(*) from contacts group by home_city limit 50; ...
1
vote
1answer
25 views

How to troubleshoot poor trigram (gin) index performance?

I have a compound LIKE in a query that would have seemed to have benefited from a gin index however on two practically identical servers I see vastly different performance, with the worst case being ...
1
vote
1answer
22 views

Impact of partition scan on large and small data

I have a table SampleTable which has a clustered index on Date column, and is also partitioned on Date column. However the Date column is of type DateTime. While querying from this table i.e. ...
0
votes
2answers
15 views

slow index creation with gist and gist__int_ops

I am seeing performance issues with frequently updated / inserts table, and one of my hypothesis is the gin index I have on int array. The gin index was added since we have query of "X exists in ...
3
votes
1answer
82 views

Save CPU cycles on SQL Server Query Plans (Better without Stats than with on wide tables)

Assume that a query in question has a very wide set of columns. The query looks like the following: set statistics io,time on; Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5 From FirstWideTable a ...
4
votes
1answer
231 views

SQL Server chooses unselective index

I was testing SQL Server indexes and found very strange behavior. Here is my code: DROP TABLE IF EXISTS dbo._Test DROP TABLE IF EXISTS dbo._Newtest GO CREATE TABLE _Test( ID INT NOT NULL, ...
0
votes
0answers
21 views

SARGABLE predicate must be in DNF. Why?

This famous paper about finding Access Path in Relational Database Management System by Selinger et al. says the following about criteria tree and predicates (at the end of 3rd section). A sargable ...
3
votes
1answer
24 views

Why does ORDER BY NULLS LAST affect the query plan on a primary key?

Using PostgreSQL 11, I have the following table with around 450 million rows: postgres=> \d+ sales Table "public.sales" ...
0
votes
1answer
26 views

PostgreSQL: Table and index on different tablespace

In PostgreSQL i have the freedom of choosing to put index (of a table or materialized view) on tablespace other than the one i chose for the table (or materialized view) itself. Is there any ...
1
vote
2answers
46 views

Index on a partitioned key

we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the ...
1
vote
1answer
24 views

Understanding composite BTREE + GIN_TRGM_OPS index prioritization & odd lower() behavior

hoping someone can try to help me decrypt some index behavior. I'm working on enabling some simple contains type lookups on various user-data columns (~varchar < 255) and trying to understand the ...
0
votes
2answers
52 views

Can indexes be deactivated for particular queries in DB2?

I'm trying to evaluate the impact of a new index on various queries that run against a DB2 11.1 database. It would be convenient to be able to force the optimizer to ignore the new index on a query-...
5
votes
1answer
48 views

What accounts for a large single index size (other than bloat?)

I've been doing some digging into the size of our production PostgreSQL 9.6 database and found some results I thought were surprising. We have a table (let's call it foos) with about 10 million ...
1
vote
1answer
88 views

How to use index for simple time range join?

I have a large (~ 100 million rows) timeseries table t_16 in Postgres 11.5 where the primary key is a field abs_date_time of type timestamp. This is a follow up of this question: Why does a CTE ...
0
votes
0answers
46 views

Problem with calculating an approximate space required for online index rebuild

I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes ...
4
votes
4answers
211 views

Leveraging an index on another column with the same ordering guarantee

I have a massive table that is only indexed by the auto-increment column, which is also populated with the current timestamp (which is not indexed). CREATE TABLE MyTable ( ID BIGINT NOT NULL ...
0
votes
1answer
49 views

Do I still need to disable or drop column store index before insert and/or update on big tables?

As the title says, I thought that from MSSQL 2016 I don't really have to drop or disable ColumnStore indexes before ETL and rebuild it after. Any consensus?
0
votes
1answer
36 views

MySQL Create Index Semantics: Clustered vs. Nonclustered?

I'm new to databases and specifically MySQL, so pardon me if this is a simple question. I'm trying to make sense of what happens with MySQL when I call CREATE INDEX. I know that primary keys are ...
0
votes
0answers
30 views

How can I get MariaDB to use indexes in a RANK() query?

Consider the following simple RANK() query. select project_id, group_id, rank() over (partition by group_id order by stars desc) as star_rank from project_metrics; I've added indexes to the ...
1
vote
1answer
60 views

SQL Server indexes and record deletion

I've inherited a large production database in which one of the tables has 72 million rows and is inserted/updated thousands of times during a day. These rows have built up over 3 years and they are ...
-1
votes
1answer
25 views

How can PostgreSQL use multi-column index for column-specific lookups?

If I'm reading this article correctly, one can create an index on t(col1, col2) and use it to speed up lookups for col1. Storage and CPU-wise, I assume that having such a combined index is more ...
0
votes
1answer
55 views

Why is this datetime index not being used?

I have a table like this: CREATE TABLE TestTable ( [TestTableID] [int] IDENTITY(1,1) NOT NULL, [IntField1] [int] NOT NULL, [IntField2] [int] NOT NULL, [IntField3] [int] NOT NULL, [...
1
vote
1answer
32 views

When SELECTing an expression that is an expression based index does the expression get processed? [closed]

I am asking this mainly for postgres expression based indexes but would be interested to know for other SQL implementations that have expression based indexes. Example: CREATE INDEX people_names ON ...
0
votes
1answer
34 views

Locating all records relevant for a specific date

I have a MySQL InnoDB table with about 1GB of records and growing. each record in this table has, among other fields two date fields : AddedDateRecord - The date the record was added (datetime). ...
2
votes
1answer
22 views

Postgres not using multicolumn index for queries on leftmost columns?

I'm working in Postgres 9.5.15 and have a table called project: id | integer | | not null | nextval('project_id_seq'::regclass) user_id ...
-1
votes
1answer
34 views

data compression does not show when scripting index on ssms

when I right click on SSMS and choose to script the create index: I get this script, without the data_compression: USE [APCore] GO /****** Object: Index [i_agencyId] Script Date: 07/11/2019 12:...
1
vote
3answers
75 views

Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle)

I have 2 queries SELECT * FROM sys.dba_indexes WHERE Table_Name = 'XDB$RESOURCE' and SELECT * FROM sys.dba_tables WHERE Table_Name = 'XDB$RESOURCE' Are there any reasons why a table wouldn'...
0
votes
2answers
37 views

How to optimize inner join query on one large table and one small table

I have table A with 15M records and table B with 5k records. I need to perform an inner join on both but the query time is considerably high. explain analyze SELECT distinct(a.student_id), b....
0
votes
1answer
28 views

Why can't I create a index using column definition in MySQL?

Consider the following create table syntax CREATE TABLE test1 (f1 int primary key, f2 int, unique key(f2)); I can also write the above as follows CREATE TABLE test1 (f1 int primary key, f2 int ...
3
votes
1answer
52 views

DBCC PAGE equivalent in Postgres

In SQL Server you could check specific index information using the DBCC PAGE command. Is there anything similar in postgres? And if there isn't, how would you get the same info then? This is simply ...
3
votes
1answer
40 views

SQL query with Like operator with Leading Wildcards

I have a query as follows: select top (30000) * from table1 where licenseplate like '%as43bv%' I have an index on licenseplate but the query will not hit the index due to leading wildcard. Since ...
3
votes
1answer
34 views

Should a partial index with WHERE NOT NULL be used if queries use a JDBC prepared statement?

Suppose we create a partial index in Postgres that avoids useless NULL data: CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL; Should I write the SELECT query as: SELECT * FROM my WHERE ...
0
votes
1answer
26 views

Postgres: Can I associate index with FK constraint?

Postgres FK constraint and index are independent. Recently I was looked for unused indexes and found trick to eliminate indexes needed for constraint enforcing by: WHERE NOT EXISTS (SELECT 1 ...
3
votes
1answer
45 views

Does the Query Optimizer Prefer to Query on Constants before Columns?

I think I have found the answer for this already but I am hoping to get some additional perspective. Assume we are JOINing two tables together on a shared column and each table then has a different ...
3
votes
1answer
88 views

Why is my DB size increasing when rebuildling an index using sort in tempDB in SQL Server?

I am running Ola Hallengren's SQL Maintenance scripts using the following command: EXECUTE [dbo].[IndexOptimize] @Databases = 'MyDatabase' , @FragmentationLow = NULL , @FragmentationMedium = '...
9
votes
2answers
492 views

Xml index, slow count of rows

I am not getting index seek in the following scenario. Instead of inserting <Number>xxx</Number> in xml column like in this post Why is the secondary selective index not used when the ...
3
votes
0answers
36 views

MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN

Question Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in ...
1
vote
1answer
19 views

How safe are PostgreSQL CONCURRENTLY option

As I know CONCURRENTLY let DATABASE create an index without any lock though It could incur more CPU usage. I am planning to create an index in production DATABASE with the option. Before applying ...
4
votes
1answer
32 views

Prevent drop column with index on it in PostgreSQL

In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in ...
0
votes
1answer
29 views

PosgreSQL query slow when sorting on indexed timestamp colum

I've the following issue: the table below: CREATE TABLE public.current_read ( id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass), cube_id bigint NOT NULL, end_time ...
0
votes
0answers
31 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
1answer
30 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
85 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 ...