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
2answers
42 views

Does MySQL use its index metadata to optimize COUNT(*) queries?

Let's consider a user_actions table with a standard indexed primary key user_action_id. I want to know the number of user actions up to a certain key (say 100 millions): SELECT COUNT(*) AS `...
2
votes
1answer
36 views

Why when I use variables on SQL the Database Engine avoid the indexes?

I'm improving our SSIS process passing the variables through a declare and I found this: Here you have the plan execution using variables brentozar.com/pastetheplan/?id=rkSxs7VPv for the following ...
0
votes
1answer
34 views

Understanding Index w/ Cast

Given: postgres=# \d b Table "public.b" Column | Type | Collation | Nullable | Default --------+------+-----------+----------+--------- a | uuid | | not ...
0
votes
1answer
62 views

Are multiple column index used in single column where clause

I have a table with a defined non clustered index on 3 columns. CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable] ( [Col1] ASC, [Col2] ASC, [Col3] ASC ) Will it be used if i do a ...
1
vote
2answers
67 views

Why is my database still fragmented after I reorganized and rebuilt it, and updated the statistics in SQL Server?

I created a Maintenace Plan, first i reorganize the indexes, then rebuild, and final update the statistics every Saturday. I ran this script for verification: SELECT S.name as 'Schema', T....
1
vote
1answer
47 views

How to share a unique value between tables efficiently [postgres]

We are modeling the concept of a global "reference code" for some of the records in different tables in our system (e.g., ABC123). A "reference code" needs to be unique, easy to ...
0
votes
0answers
17 views

Analysis of Conditions with no results and high offset number - PostgreSQL JSONB [duplicate]

I am asked to record down the analysis on PostgreSQL JSONB query having Conditions with high offset number and query that provide no results considering the below. select * from sub_test df, ...
4
votes
2answers
223 views

Speeding up index scan backwards query

My app is making the following psql query, and it is running extremely slow: SELECT COUNT(*) FROM ( SELECT 1 AS one FROM "large_table" WHERE "large_table"."user_id&...
0
votes
2answers
43 views

Full text index for a column that stores json which contains guid - Is this a good idea, space-wise?

I'm considering adding a full text index for a column whose values are json strings. These jsons usually contain Guid (or UUID) values. The index goal is to allow fast search over both "normal&...
0
votes
0answers
18 views

PostgreSQL JSONB query on comparison operation with Index

Considering the below query, select * from sub_test df, jsonb_array_elements(json_data) jba where (jba->>'name')::text = 'SubYear' and (jba->>'value')::numeric > '...
0
votes
1answer
33 views

Optimize SUM with time segment WHERE

Suppose I have a table A with columns id:int, kpi:int, created_at:datetime. And suppose I would need to fire the following query: SELECT SUM(kpi) FROM A WHERE (created_at > X AND created_at < Y) ...
0
votes
0answers
27 views

PostgreSQL JSONB Execution Plan with high Offset

How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance? Considering the below "Test Query", help me to clarify the ...
0
votes
0answers
32 views

azure index recommendation

I have 142 SQL databases on Azure. There are using Standard Elastic Pool (100 eDTUs). Azure Portal recommended an index for one my database. It says disk space needed is 1045.19 MB. Azure portal ...
0
votes
1answer
32 views

index used when query process on oracle database

I a new one using and learn about oracle database. currently i have sql query like this select attend_code from attadanstatusdetail where attend_id = 'attendidcode001' and company_id = 168 currently ...
-1
votes
1answer
19 views

indexing impact

If I have a table with 4 columns(a,b,c,d) I create the following index for this table : CREATE INDEX index_t ON table(a,b,c); Will the PostgreSQL optimizer consider using the index in the following ...
0
votes
2answers
79 views

Postgres create Index

im running out with ideas for creating postgres index for a better performance. This example below is a selection of a big table with many entries for a diary to person relation. usually, i create an ...
2
votes
1answer
82 views

VARCHAR missing index not showing up in missing index dmv or query plan suggestions

I have a table with 307200 records, with f_column being VARCHAR(30). No missing index are shown in the missing indexes dmv or suggested in the execution plan. SELECT f_column, f_column2 FROM t_table ...
1
vote
0answers
34 views

Update and insert performance with partial indexes

I have different queries for fetching data from a large table (about 100-200M rows). I've created partial indexes for my table with different predicates to fit the query because I know each query. For ...
0
votes
1answer
47 views

Postgres query slow despite index being used

I have the following tables: The main lead table with close to 500M rows: create table lead ( id integer, client_id integer, insert_date integer (a ...
1
vote
0answers
27 views

Extreme slow query for a simple indexed selection - Why? [duplicate]

i have a table in my database that is described as follows: -- Table: public.devicemeasurement_mqtt -- DROP TABLE public.devicemeasurement_mqtt; CREATE TABLE public.devicemeasurement_mqtt ( ...
0
votes
1answer
17 views

MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly

I have a table in Mysql with userid, status and other columns. I keep updating the status of the row as and when the job is complete. Periodically these rows are deleted from the table. JobTable (...
0
votes
1answer
19 views

Why is MySQL using this index? Can I force it to use another?

I have a query, and when I call EXPLAIN for it, it appears that my db is using an unhelpful index and ignoring a useful index. EXPLAIN SELECT id, full_name, agent_id, brand_id FROM `mytable` WHERE ( ...
0
votes
2answers
125 views

Why Whole Table Scan (Clustered Index)

I have a big table with 315M rows. with these columns & Index : [key_] (int & IDENTITY(1,1)), [time_] (datetime), [value_] (float), [quality_] (int) PK & Clustered-Index: key_ , Non-...
1
vote
1answer
65 views

Clustered and columnstore indexes

I just started a new job where we have many tables from ETL processes. I was told not to put any columnstore or clustered indexes on any tables since the server does not have the resources (CPU). So ...
0
votes
1answer
147 views

Why is this postgres query ignoring the index in the subquery?

I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index: sophia=> explain analyze select count(distinct(source_doc)) from public....
0
votes
2answers
708 views

Slow planning time on PostgreSQL 12.4

I have a database with Openstreetmap location data, and in particular there is a polygon table with around 500 million rows. I created indexes on all of my queries consisting of using gist(way) where ...
2
votes
1answer
32 views

How does mysql optimiser chooses a composite Index

I have a table with the following structure. -id (bigint|pk) -funnel_id (bigint|fk with funnels table) -affiliate_id (bigint|fk with affiliates table) -created_timestamp (bigint) Observations ...
1
vote
4answers
53 views

Load index to cache is not using memory available in the key_buffer_size variable

Good afternoon folks, I have a problem when trying to load the indexes from the MyISAM tables into MySQL memory, even though there is space available in the key_buffer_size variable, it stops loading ...
0
votes
1answer
28 views

Covering Index Not Being Used Effectively

I have a unique composite index on three fields (hash, is_customer, user_id) in a table a. And queries that go like this: select `a`.`id`, `hash`, `is_customer`, `user_id` from `a` where `hash` = ...
0
votes
1answer
23 views

Does Sql Server maintain the free space defined by fillfactor? [duplicate]

I'm new to concept of what fillfactor is and why it is used. So far, reading this article, I've learned that by setting the fillfactor configuration of an index, we specify how much space on each page ...
0
votes
1answer
225 views

Create GIN index in a schema with the pg_trgm extension

I'm migrating entities from the public schema into a new one, let's call it myschema. Originally, the pg_trgm extension was installed and the index was created in a schema migration with: CREATE ...
4
votes
2answers
325 views

SQL Server not using nonclustered index

I have table which contains about 470mln of rows. I would like to select data based on date. I have two indices created on this table. One is clustered one and other is non clustered on date column (...
0
votes
2answers
855 views

Is the foreign key automatically indexed in MySQL?

Do we need to create an index for a foreign key while creating the table in InnoDB of MySQL?
0
votes
1answer
90 views

MySQL - Force index degraded performance over time

Last year we migrated data from our old product environment into a new environment with our new product. Since the new product was still fresh, we found that some of the new queries were very slow ...
0
votes
1answer
112 views

Index fillfactor and index pages in PostgreSQL

The postgres documentation says index fillfactor affect on "index pages" and says "for heavily updated tables a smaller fillfactor is better". I'm confused that its about update an ...
2
votes
1answer
28 views

composit index and possible keys specific query

i have query like this select date_format(referencedate, '%m/%d/%Y') as PostingDate, reference_code, "" as DocNo, Account_Number, detail_desc as Description, case ...
0
votes
1answer
17 views

Query dramatically slower with less rows from WHERE to evaluate

What is the query SELECT SQL_NO_CACHE id, laptime, driver_id, driverName FROM ( SELECT race_result_details.id id, driver_id, driver.name as driverName, laptime FROM race_result_details LEFT ...
0
votes
0answers
44 views

PostgreSQL: Bitmap Heap Scan very slow for SELECT using time zone

We have PostgreSQL 11.1 on x86_64-pc-linux-gnu (Red Hat 4.8.5-28), 64-bit. We have a table with quite a lot of data (but not extra huge, e.g. weather data for some locations for one month). There is a ...
0
votes
1answer
30 views

Index on Multiple Dimensions [duplicate]

I've recently started reading the book Designing Data-Intensive Applications. This book has revealed that I have been using indexes incorrectly, in that I didn't realize that even if I have an index ...
1
vote
2answers
81 views

How do I enable full text search on MySql 8?

I'm using MySql 8. I would like to speed up searches on my table of the form select * FROM dirctory_coop where name like '%mystr%'; and someone mentioned using Full text searches -- https://dev....
-2
votes
1answer
421 views

How do we select fragmented indexes from postgresql?

Need to select indexes which are fragmented for a certain level. Can someone help me with the query for that?
0
votes
1answer
24 views

Is there a better way to exclude all “not actively created by a human” indexes than what I'm doing?

My goal is to list the indexes which I, a human, have actually added into my database. I don't want to see ones that were created by PG when creating PKs and whatnot. I didn't even know it did that, ...
1
vote
1answer
37 views

How can I enforce that my composite key columns do not equal one another in MySQL?

I have a pivot table in which there is a composite key made up of two columns that reference the same table. The columns are requester_id and addressee_id and both of them refer to the id field on a ...
3
votes
0answers
107 views

Why is PostgreSQL not using composite Index on JSONB Fields?

Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example: SELECT id from table where (j1->>'f1')::int = 1 AND (...
5
votes
1answer
188 views

Do I need to ANALYZE table after DROP INDEX?

In our postgres db we historically have many similar indexes (e.g. is_deleted boolean) that have quite bad selectivity value. Worse than that, sometimes query planner breaks and postgres starts to use ...
0
votes
1answer
19 views

String index for a list of values using IN($1,$2): will a btree index work?

We have a query that needs to compare a value to a list of values in a where clause. The strings are known and discrete. Does IN use the = operator to compare values? Would a b-tree index work for ...
1
vote
0answers
29 views

Why does adding an index increase the execution time in SQLite?

I'll just show you an example. Here candidates is a table of 1000000 candidates from 1000 teams and their individual scores. We want a list of all teams and whether the total score of all candidates ...
0
votes
1answer
64 views

Why is this (postgres) composite gin(scalar, scalar, array gin__int_ops) index not using the array criteria?

I have a table create table r_prot_search( openprot_release_id integer, annotation_group_sig text, dataset_ids integer[] ); With a composite index: create index my_index on r_prot_search ...
-1
votes
1answer
108 views

Oracle index analyze stats - managed by whom?

Some questions about managing indexes. We saw in some cases where indexes weren't analyzed in weeks, even though data was inserted and queried from them - using conditions that use these indexes. The ...
2
votes
1answer
630 views

Does using inequality (<>) or NOT in where clause lead to SQL ignoring the index?

I found this article that talks about avoiding using <> operator in where clause because optimizer ignores the index. https://www.mssqltips.com/sqlservertutorial/3203/avoid-using-not-equal-in-...

1 2 3
4
5
57