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
1
vote
1answer
35 views

Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?

I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index CREATE INDEX tab_x_y ON tab(x) ...
1
vote
4answers
102 views

PostgreSQL (big data): search by tags and order by timestamp

We need to add a search feature on a large table (200M+ rows): item_id | tags | created_at | ... ------------------------------------------------------------------- ...
0
votes
1answer
36 views

Clustered index and Non clustered index [duplicate]

This might be a silly question but i really want to know the internals.This is one of small table just to test as below I have create a table as above and 'name' column is primary key and clustered ...
0
votes
1answer
69 views

Non-clustered index covering a clustered index

I have a table that has the following indexes: IXC clustered index that includes a single column : A asc. IX1 that includes two columns : A asc, B asc. In this case ICX seems like a duplicate index ...
1
vote
2answers
105 views

Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)

I have the following table and indices defined: CREATE TABLE IF NOT EXISTS data ( id serial, job_id bigint NOT NULL, payload jsonb NOT NULL, tags jsonb NULL ); CREATE UNIQUE INDEX IF ...
1
vote
1answer
41 views

Rebuild materialized view from massive base tables

We had a materialized view over two bae tables, which was set to refresh on demand, and we had an hourly job which fast refreshed the mview after a linux script inserted new records to the base tables....
0
votes
0answers
63 views

Create expression index for XML query

Schema create table soa_rawdata_lp ( date_created timestamp with time zone default now(), filename varchar(128), xmldata xml ); Each xmldata entry is about 4.2MB size. Currently ...
0
votes
1answer
88 views

Most efficient way to query a date range in Postgresql

I have a table with a timestamp with tz column. I want to perform a count / group by query for rows within a certain date range: select count(1), url from app_pageview where viewed_at between '2019-...
0
votes
1answer
175 views

PostgreSQL not using varchar index when ordering by primary key

My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it ...
0
votes
1answer
30 views

How to avoid index_merge in a query?

I have multiple queries using index_merge but for put an example, I show you the following query EXPLAIN select count(*) From Clientes Where id_campana='1849' and id_empresa='55' and (...
0
votes
1answer
89 views

Can I modify a column's data type against a live table in Postgres?

I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields. I want to change these columns back to varchar. Can I do this against a live ...
0
votes
1answer
19 views

Multiple query to columns indexed in MySql

I am trying to make a table with server-side page, so it can also apply filters for each of the columns in my table. This is its structure: +----------------+ | products | |----------------| | ...
1
vote
2answers
105 views

How to compare write speed before and after adding index?

I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on ...
0
votes
3answers
64 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
1
vote
0answers
48 views

Update is not taking index for query

In the logs I found slow queries, but when running them manually it is fast or at least taking indexes into account. The logs tell me the query part is not using an index, which is available. Running ...
0
votes
0answers
55 views

Will MariaDB treat ut8/utf8mb3 and utf8mb4 collations as compatible?

Background on relevant character sets MySQL and MariaDB support the deprecated utf8mb3 character set and collations. (utf8 is currently an alias for utf8mb3.) This supports all Unicode code points on ...
0
votes
1answer
76 views

Offline index rebuild - does it also create a whole new copy of the index?

As I know, while online index is rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index. ...
0
votes
0answers
21 views

Indexing an attribute of the elements of an array of another array in a jsonb field

I have the following document that is stored in a jsonb field in a postgres 11 DB. { "bundles": [ { "id": "9c9c1b73-b058-3f22-a48b-725ce39eb861", "items": [ ...
1
vote
1answer
105 views

MongoDB: Creating index very slow

I'm currently creating an index on a mongo database and so far it has taken about 52 hours. I've worked with large(r) Mongo databases before but creating an index has never taken this long. My ...
0
votes
1answer
146 views

msdb system table fragmentation

I have a Index Maintenance job for msdb database to rebuild those fragmented index in msdb database. But I still found some backup related table in msdb database (e.g. backupmediafamily, backupset, ...
0
votes
1answer
79 views

Very slow query despite index being used

I have this table, created to hold tons of modbus measurements that comes from my devices: CREATE TABLE public.tv_smartdevicemeasurement_modbus ( measurement_id serial NOT NULL, insert_time ...
0
votes
1answer
63 views

Index / Check DB Script question

I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.
3
votes
1answer
122 views

Do I need to rebuild heap table after rebuilding a non-clustered index?

Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe ...
2
votes
1answer
146 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
2answers
55 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
30 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
39 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
49 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
27 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
39 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
96 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
247 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
30 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 ...
6
votes
1answer
162 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
93 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
49 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
179 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
92 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
65 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
215 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
55 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
220 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
350 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
110 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 ...
1
vote
0answers
45 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
64 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
30 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
3answers
224 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
33 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
35 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). ...

1
3 4
5
6 7
55