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
1answer
15 views

Is there a mechanism inside of database engines to avoid collisions of non-sequential keys? If so, how does it work?

Say that I create a table that that has a random uuid for a primary key. Is there typically a mechanism inside of the database to gracefully deal with collisions? How does it work? My mind is ...
0
votes
0answers
8 views

MongoDB Index Restore Failure Since I Upgraded from 4.2 to 4.4

Steps to reproduce After upgrading from 4.2 to 4.4, take backup using mongodump Now restore backup On some collections, I am getting following {"alternate":"non-ignorable", "...
1
vote
1answer
41 views

PostgreSQL Query Optimization - Joining with a lookup table does not use index

I am attempting to optimize indexes and querying for a postgres database, and have run into some issues around a lookup table I have used to support joins for a many:many relationship. Here is the ...
5
votes
1answer
155 views

Index Definition Order and ORDER BY Clause

So i was doing my morning blog reading and stumbled upon this fun exercise: https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/ Here is the query in question from the ...
-1
votes
1answer
35 views

How to correct number of rows sampled (index statistics) in a large table?

I am looking at index statistics of a large table. The number of rows sampled are 300. Where as the total rows in the table are 345100. I want to fix such that a substantial amount of rows are sampled,...
-1
votes
0answers
29 views

How does sql server decide when to stop after starting to read an index?

Let's say the sqls server is reading an index. It may be clustered or non clustered index. Say the index has values from 1 to 999999. And assume sql server decides to read the index. When the where ...
-1
votes
0answers
17 views

How to design a database for search filters?

We have a normalized relational database with lots of tables. We plan on adding search filters that can be combined together. We don't know which filters are going to be used the most at this time, ...
0
votes
1answer
19 views

Get number of deleted pages in PostgreSQL table/index?

How can I get the number of deleted pages in a table or index? My purpose in asking is to modify an index bloat query that otherwise doesn't take into account which pages are deleted (and therefore ...
0
votes
1answer
17 views

Amazon RDS index creation on large table over 300 M records

I need to create a new index on large table in RDS MySQL. This index is needed to reduce the scan time on this table. Below is the configuration: Table size: 400 GB Records: 400 Million column - ...
0
votes
0answers
7 views

A strange index that is always marked pg_index.indisvalid=false . Why indexes with ON ONLY option are always rebuilt in invalid state?

I'm looking in database (postresql 11.10) and see an index on a partitioned table that marked as invalid: SELECT indrelid::regclass, indexrelid::regclass, indisvalid, ddlx_create_index(...
0
votes
1answer
103 views

Why is read performance using a covering index better than using a primary key?

I have this table with just Id and Value columns, and this is joined to to get the value in a common query. The Id is the (clustered) primary key and the value column is read in the select. If I add a ...
2
votes
1answer
50 views

PostgreSQL relative update breaks unique constraint

I have unique index on column for UPSERT. When I am trying to update column using v = v + 1 expression my unique index breaks. SQL CREATE TABLE test(v bigint, data jsonb DEFAULT '{}'::jsonb); INSERT ...
2
votes
1answer
38 views

Why is my Postgres 9.6 Create Index Concurrently INVALID?

Am trying to create an index concurrently on production. However when doing so the index when viewing via \d says INVALID. Why is this the case? Never seen it happen before. Have tried re-creating but ...
-3
votes
1answer
30 views

Must an index cover all columns to be used in a merge? [closed]

For testing purposes, I'm interested in speeding up the following query: SELECT a.*, b.id FROM "table" a JOIN "table" b ON a.id = b.id where "table" has an index on id. ...
1
vote
0answers
61 views

How to estimate the nonclustered index size before its creation?

I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong. Scenario 1 Create table mytable1 with clustered index and ...
1
vote
1answer
35 views

What is index_method in PostgreSQL?

In the Postgres docs, index_method is used. Are Index Methods and Index Types the same thing? Is it possible to create your own index method or are we restricted to the predefined ones ? I just want ...
4
votes
1answer
54 views

Optimize query matching first N items of an array

I'm working with a Postgres DB full of chess game data right now, where each game is a row in a 'records' table. The players' moves and the (optional) computer evaluations of those moves each have ...
2
votes
1answer
86 views

How can I improve query performance with filter on datetime column using an indexed view?

I have this table: CREATE TABLE transactions ( id NUMERIC(20, 0) NOT NULL PRIMARY KEY, amount NUMERIC(18, 2) ...
3
votes
1answer
163 views

How to tell if an index has OPTIMIZE_FOR_SEQUENTIAL_KEY turned on?

SQL Server 2019 brings the OPTIMIZE_FOR_SEQUENTIAL_KEY option that, as far as I know you specify on index creation or alter using the WITH keyword. But how do you check if the option is already turned ...
0
votes
0answers
37 views

SQL Server index on uniqueidentifier, how is the data ordered

If an index defines the order of the data and a uniqueidentifier can't be ordered, then how does an index on a unqiueidentifier work. How is the data ordered?
0
votes
1answer
18 views

Struggling to create an index for my PSQL Query

I'm running a query on a dataset that's got 30 million rows in and will only continue to grow, the table is customer_actions (Table size is 2416 MB) create table customer_actions ( id ...
3
votes
1answer
39 views

How to get index scan for OR'ed time-range predicates?

I have table events with fields: id user_id time_start time_end ... And have b-tree index on (time_start, time_end). SELECT user_id FROM events WHERE ((time_start <= '2021-08-24T15:30:00+00:00' ...
0
votes
1answer
33 views

Is COW (copy-on-write) based atomic commit widely used in mainstream databases?

The database is not a flat structure like a file system, and generally has three to four levels of index levels. If you simply use COW, you will have obvious write amplification every time you modify ...
1
vote
1answer
40 views

Massive slowdown after doing an ALTER to change index from int to bigint, with Postgres

I have a table like this: create table trades ( instrument varchar(20) not null, ts timestamp not null, price double precision not null, quantity double ...
-1
votes
0answers
21 views

How to index objects inside array in JSONB field?

I want to index the JSONB field with GIN indexing. Inside this field, I have an array of objects. Precisely, this is how it looks (shortened second object with three dots): [ { "tags&...
0
votes
0answers
6 views

Are FullText catalog indexes managed by Azure SQL?

I know normal indexes are maintained (reindexed) automatically on azure SQL. (without the need of having to create a jschedule job to run it periodically) However, non of the documents are clear to me ...
0
votes
1answer
28 views

Azure SQL Read-Only table Indexing

I'm trying to work out the best indexing strategy for a slightly weird pattern I'm forced to use with Azure SQL. Tables are dropped, re-created & loaded with new data daily from ADF Tables are ...
0
votes
2answers
23 views

Indexing on a fixed-length string (10 characters - phone numbers) for a large number of entries (performance)?

I understand that a lot of similar questions have already been asked but I could not find a definitive answer for my question. Briefly, I have to authenticate users using a REST API, with their phone ...
1
vote
0answers
11 views

Postgres - Cascading Deletes and Partial Indexes

I am trying to optimize deletes from a table that results in lots of cascading deletes. Naturally, part of the process is ensuring that there is an index in place for each foreign key involved in the ...
0
votes
2answers
59 views

Why does GROUP BY on table A cause "using temporary; using filesort" on table B? [closed]

Given the following contrived example code: SELECT A.author_name , COUNT(T.title) AS num_titles FROM authors A JOIN titles T ON T.authorFK = A.authorPK GROUP BY ...
1
vote
1answer
33 views

Postgresql multi-field index on jsonb data does full tablescan when field is not found

I have a table with millions of records. I need to query for the last-added record by timestamp, for a given field. Pretty simple stuff, trivial to do with SQL: CREATE TABLE records ( id integer,...
2
votes
1answer
35 views

PostgreSQL similarity operator - how to optimize / index?

I'm a newbie to both SQL and PostgreSQL. I'm trying to figure out how to get this type of query to utilize an index for the trigram operation. Using PostgreSQL 12.7 on x86_64-pc-linux-gnu The basic ...
0
votes
3answers
97 views

adding the right index to a table in a MySQL Database

We are having a big problem of high CPU usage of MySQL process in our database server, after investigation we have found that a big part of slow queries are related to a specific table and a big ...
0
votes
1answer
38 views

Is there an index that would support comparison of 2-tuple values?

I want to implement cursor-based pagination for a large data set. With OFFSET based pagination, when the user wants page N, you just OFFSET N * page_size. The query ends up like: SELECT * FROM books ...
0
votes
0answers
17 views

Intersection / overlap query of interval in dynamoDB

I want to check if two intervals intersect one another. Easy, right? Just plug this guy in and we can move on with our lives? intersects = itemStart < queryEnd && queryStart < itemEnd ...
2
votes
1answer
46 views

Are reportedly "bloated" index sizes a problem?

I create a table with 1 million records, then I delete those records. (Common with some sort of processing list.) CREATE TABLE example (id int PRIMARY KEY); INSERT INTO example SELECT generate_series(...
1
vote
1answer
188 views

Why is Index causing bad estimation and execution plan

SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) - ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0) FROM [Credit] M WITH(NOLOCK) INNER JOIN [Transaction] T4 WITH(...
0
votes
2answers
40 views

Index Drop for Partial Overlap?

I'm working with a 50mil row table with the following: Clustered Index keyed on [Col1] Nonclustered Index keyed on [Col1], [Col2] (no includes) It seems like the right call here would be to drop #2 ...
0
votes
0answers
15 views

How to interpret results of mongo explain and improve geo spatial query?

I have a collection in MongoDB where the documents have a structure like this: { "vechicleDetails": { "location": { "type": "Point", ...
1
vote
1answer
42 views

Several Postgres partitioning questions (hierarchical partitioning, HASH, PK order)

I'm pretty new to Postgres and to RDBMS partitioning. I have a few related questions about the performance benefits of partitioning. Background: I am trying to fix slow batch queries on a 4 column ...
0
votes
2answers
36 views

Sort index doesn't work for MySQL

I have a table with more than a million records. I get the data by using LIMIT, but also ORDER BY. The latter causes a huge performance problem. My query ends with this: ... ORDER BY `record`.`...
1
vote
0answers
48 views

Postgres failing to consistently use best partial indexes when multiple are present

In Postgres 10.17, I have two queries with different WHERE clauses and two partial indexes (idx_s_id and idx_w_id) optimized for each one respectively that should allow index only scans for both ...
1
vote
0answers
35 views

How to interpret pg_stat_user_indexes and pg_statio_user_indexes results?

I have this query to analyze index usage in Postgres: SELECT idx.* , idxio.idx_blks_read , idxio.idx_blks_hit FROM pg_stat_user_indexes idx JOIN pg_statio_user_indexes idxio USING (...
2
votes
1answer
29 views

Finding timestamp when index was created or reindexed

Is there any way to find when an index was last created or reindexed in PostgreSQL 12-13? The docs show a few standard views like pg_stat_user_indexes that track usage statistics for indexes, but I ...
2
votes
3answers
98 views

How to find progress of database reindexing on PostgreSQL?

When running: REINDEX DATABASE CONCURRENTLY mydb; which could take several hours, or even days, depending on the size of the database, is there anyway to get a rough estimate of its progress? I've ...
0
votes
1answer
32 views

Help needed to raise performance Postgres SELECT

I have a table in Postgres 9.6: CREATE TABLE public.lml_data ( timestamp_from timestamp without time zone, timestamp_to timestamp without time zone NOT NULL, statcode character varying(16) ...
1
vote
0answers
21 views

Creating the right index/es for querying views in SQL(ite3)

I have a python script that works by first creating some tables in a SQLite3 DB from lots of text files, then creating some views by joining the tables, and finally querying one of the views and ...
0
votes
1answer
15 views

Letting user select Unique Identifier of dynamic data?

I'm creating an app that lets companies view analytics about their employees. The manager of the company will upload all their employees' data and the app will run analytics on it. The problem is that ...
0
votes
0answers
28 views

Slow query in SSD when enable_bitmapscan=on

Table INFO and index INFO: CREATE TABLE PART ( P_PARTKEY SERIAL, P_NAME VARCHAR(55), P_MFGR CHAR(25), P_BRAND CHAR(10), P_TYPE VARCHAR(25),...
0
votes
0answers
36 views

Slow index scan on postgres12

I have a table like that should enable index only data to be used (Postgres-12) CREATE TABLE sensor_values_2021q2 ( ts TIMESTAMPTZ(35, 6) NOT NULL, value ...

1
2 3 4 5
59