Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
710
questions
0
votes
0answers
21 views
Postgres Jsonb array and join
I made a difficult query (for me) that allow me to get the element of a array in a jsonb element with condition on the bottom of the jsonb like this:
Select distinct c.*
from table,
...
2
votes
1answer
92 views
Indexing Referenced Table Columns
I would like to do a search across columns of joined tables. What would the best index configuration be for the query below?
Table A
------------
Id
ValueA
-- Table A has many more columns
Table B
---...
0
votes
0answers
23 views
Triggering Bitmap heap scan on big table. Can this be avoided somehow? [closed]
Context
Brief context, I've got 2 "geographic" tables, T1 with 16m rows (measurements) and T2 with ~2k rows (states and cities).
I'm creating a geometry-less cache_areas table with the pre-...
0
votes
1answer
16 views
@TimeLimit setting in Ola-Hallengren's IndexOptimize Running Much Longer Than The Value Set
We start our indexing at 10:00 PM every evening and we want to allow it run until 4:30 AM, the next day. Therefore, we set the @timelimit variable / parameter to 23400 seconds. The issue is that the ...
0
votes
0answers
18 views
Calendar Event table - best practice setup for range queries and individual retrieval
This seems like a generic problem that should have been solved already, but I can't find anything about this. In general this question is - given a table where data is read by a date range, what is ...
2
votes
1answer
30 views
Searching INET[] for CIDR match, efficiently using indexes (without UNNEST)
This question stems from having recently discovered that the following are very, very different in terms of performance
DROP TABLE IF EXISTS dns_lookup;
CREATE TABLE IF NOT EXISTS dns_lookup (
...
1
vote
2answers
65 views
Why is PostgreSQL Not Using My Index?
I have the following query:
SELECT "account_transactions"."subsidiary_id"
FROM "account_transactions"
WHERE (transaction_type = 'loan_payment'
AND amount > 0
...
0
votes
1answer
97 views
Slow query, indexes are in place. Help!
Here is the query:
EXPLAIN
SELECT
f.transaction_id, a.first_name
FROM
gateway_fees_stripe f
INNER JOIN payments_credit_card a
ON a.`transaction_id` = f.transaction_id
WHERE f.organization_id IN (262,...
2
votes
2answers
49 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&...
1
vote
1answer
35 views
What is most efficient way to store one or two chars?
In Postgres table with ca million rows I have a column bookingtype (character varying(20)), which default value is null and besides that it may have 7 distinct values (1-2 chars). Current situation:
...
1
vote
1answer
80 views
Eliminate filter operator before columnstore index scan operator
I have a large fact table with millions of rows called MyLargeFactTable,
and its a clustered column store table.
There is a composite primary key constraint on it there as well
(customer_id,...
0
votes
0answers
14 views
Analyzing performance of an insert int postgres
I have a statement which is inserting a bunch of rows (doesn't really matter what or where for this question) into a Postgres DB and it isn't as fast as I'd like. I can run an explain query to see ...
0
votes
1answer
15 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
25 views
Why does my Query use a Bitmap Heap Scan
my simple query is not really fast and I don't know why my query is using Bitmap Heap Scan.
explain analyze verbose
SELECT connections, epoch_time
FROM connections
WHERE host_name = 'xyz.z'
ORDER ...
1
vote
1answer
54 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
2answers
55 views
Multiple Indexes and Tuning
I'm utilising Azure SQL Database 2019.
Let's say I have columns A-F and the following indexes.
Primary Key (clustered): A
Index 1: B (ASC),C (ASC)
Index 2: B (ASC)
If I wanted to run run the ...
0
votes
0answers
15 views
SELECT statement optimization MySQL
I am looking for a way how to make my SELECT query even faster than it is now, because I have a feeling it should be possible to make it faster.
Here is the query
SELECT r.id_customer, ROUND(AVG(tp....
0
votes
1answer
35 views
Why does adding an index on a column containing a char version of a UUID decrease performance?
I have a table with 3.5 million rows containing UUIDs in their ascii representation (so 36 chars) and an integer amount.
I need to SUM all amounts grouping by UUID, so the query is very simple:
SELECT ...
-2
votes
1answer
49 views
Indexes not used for join
I join listings_tail table and materialised view cals_reviewed_wide_scaled on listing_id column of type integer.
The table has around 1,5 billion rows and 2 mln unique listing ids. The MV has been ...
-1
votes
2answers
32 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
37 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 ...
3
votes
0answers
55 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 (...
1
vote
0answers
23 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
29 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
vote
2answers
99 views
Why is index not being used? Can I replicate plan?
I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment.
Specifically, in the UAT environment the ...
0
votes
1answer
50 views
Receiving “Query processor could not produce a query plan because of the hints defined in this query” when using FORCESEEK but can use FORCESCAN?
I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total).
One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that ...
4
votes
1answer
157 views
Why is a GiST index used for filtering on non-leading column?
I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query....
0
votes
1answer
25 views
Unable to create partial indexes using bloom
I'm new to bloom indexes and currently my table has 3 partial indexes for the same column in the WHERE clause and with different column combinations for the same table. I was trying to replace them ...
1
vote
1answer
103 views
Index or query tuning for complex predicates
I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates:
JOIN on a date from one table being in range - between two date ...
2
votes
1answer
34 views
Indexing a date column in MySQL doesn't work if a date function is used
I have a table with the following schema
table_name
id int
task_id int
completed_date datetime (INDEX IX_TBL_NAME_COMPLETED_DATE)
I run EXPLAIN on this query
EXPLAIN
SELECT
*
FROM ...
0
votes
1answer
16 views
Create partial index with single value
I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-...
0
votes
0answers
37 views
MySql Group By Query Taking More 5 seconds On An Indexed Table
I have a simple analytics table which has a little over 2 Million rows and currently growing at a pace of 20k rows per day. Please check the table
CREATE TABLE IF NOT EXISTS `affiliate_analytics` (
`...
1
vote
1answer
27 views
Optimising query: not sure why LOWER() index isn't used
I've got comments that have 1..N comment_notifications.
Each notification basically contains a username of the user notified.
Each notification can be resolved_at when the user is done with it.
This ...
0
votes
1answer
39 views
Use both b-tree and BRIN indexes in PostgreSQL
I have rather large tables (> 1000 M rows) where I have to do quick lookups. They are typically indexed with composite indexes to enable index-only scans like this CREATE INDEX table (study, ...
1
vote
2answers
30 views
MYSQL Query Index Check
I have a table with the following structure;
CREATE TABLE `operations_copy` (
`id` bigint(22) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`zoneId` int(11) unsigned DEFAULT NULL,
`...
0
votes
2answers
44 views
Postgres 10.6: Why is a particular partition's index not being used?
I'm truly stumped as to what's causing this issue and hoping for some guidance.
Firstly, this is in our STAGE environment and we partition this table based on the createdTime every month. I have ...
3
votes
2answers
59 views
Why is creating and dropping a random index fixing this performance problem?
I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/...
1
vote
2answers
33 views
postgres includes listed column in sort key and thus fails to use index
This is a query that occurs reasonably frequently:
SELECT DISTINCT id, published_year, first_author
FROM publication
ORDER BY published_year DESC NULLS LAST
LIMIT 10;
There is an index that could ...
0
votes
0answers
57 views
SQL Server Full-Text Search Order by another Column Performance
I have two tables, Entity and EntityDetails.
Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime).
EntityDetails table has columns EntityId, Details1, Details2.
Where ...
0
votes
1answer
73 views
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.
Do you think that indexes going to reduce query run time?
Any ideas how ...
0
votes
1answer
97 views
Optimise primary key and index settings for multiple link tables
I have this tables stated as below.
orders is the main table that will store all the details such as the customerID, merchantID, which riderID it was assign. The order details like total, subTotal, ...
0
votes
1answer
49 views
Indexing a GROUP BY Select with Multiple Inequalities
I have a pair of linked issues I need help with:
Issue #1: How to Index a Table to Optimize for a "Group By" SELECT
I have a table of stock price information with the following Create statement:
...
0
votes
1answer
22 views
When using blitzindex should are the reads the number of times the index was used to read or is that the op stats?
Im using blitz index to identify some indexing issues. Im concerned that I am reading the output incorrectly. In my output, im seeing reads, compared to writes in the usage column, and then im ...
0
votes
1answer
24 views
query doesnt use column dedicated index after a certain amount of rows
I have the following table:
create schema test;
CREATE TABLE test.foo (
info_datetime timestamp NOT NULL,
asset_id int4 NOT NULL,
price float8 NULL,
CONSTRAINT minute_prices_pkey ...
1
vote
0answers
42 views
Which order should the columns in a composite key as a foreign key be specified?
Looking at the code from this answer which uses a composite key to control which child tables can contain the parent key, I noticed he specifies the primary key of the foreign table first, then the ...
0
votes
1answer
71 views
Speeding up “duplicate” checks
We have an events table that is reasonably large (~35 million rows)
The original table is like this,
CREATE TABLE [dbo].[Events](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[...
1
vote
2answers
61 views
Very large table makes the application performance too slow
We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.
I found one table referenced in this app with 1,332,730,...
3
votes
1answer
141 views
How can I improve the performance of this query on a table with millions of trigrams?
In an effort to make searching across many string fields in certain tables faster, I've been attempting to use trigrams.
I've created a separate table to hold them, and a query to search against them ...
0
votes
0answers
18 views
Would a set of non-clustered index work better than a clustered index for my large table?
I'm trying to create an index over a table with potentially 100 million rows. Right now I have the following columns:
name="date", type ="DATE"
name="account_id", type ="INT64"
name="group_id", type =...
0
votes
1answer
30 views
Does the order for clustered index have the same constraints as non-clustered index on search?
I have a table filled with a bunch of data, and I want to use the following clustered index:
[account_id, group_id, run_id, page_id, date]
For a non-clustered index, if I were to to use WHERE ...
