Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
1,964
questions
0
votes
0
answers
50
views
UPDATE vs SELECT returns different Execution Plan
In my DB table, where ID is the PK, if I use this:
EXPLAIN SELECT * FROM table WHERE ID = 3;
The type and the ref columns of the explain output table are both equal to const.
Now, when I use this:
...
- 163
1
vote
1
answer
125
views
Optimisation select with large IN and millions of records in result
I work with a table which consists of 1.5B records.
DB: RDS, PostgreSQL 12.4, 16GB RAM, 4vCPU
Schema:
CREATE TABLE public.trip (
id bigint NOT NULL,
cell_to character varying NOT NULL,
...
- 13
1
vote
2
answers
651
views
CPU High usage crashing our server
The server where my database is running is suffering from CPU spikes. We're having trouble identifying what is causing these CPU spikes, and consequently how to mitigate them.
I've tried adding some ...
0
votes
1
answer
123
views
Query data via username - pair - best performance
RDBMS: MariaDB 10.3
Let's say you have a table which stores specific information (info_1, info_2, etc.) according to a given pair of usernames; like so:
+----------+----------+----------+----------+---...
- 163
-1
votes
2
answers
2k
views
MySQL 8 - optimize all tables + indexes
I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have ...
- 113
-1
votes
1
answer
74
views
Long Running Query Optimization
I need to optimize this query as this query is taking minutes of time in execution.
Below is the query. i'm giving date parameters and shift id is optional.
as query is returning same in and out from ...
1
vote
2
answers
229
views
How to index range condition and ordering by a different colum?
I use PostgreSQL and i have a table like this:
create table transactions (
uuid uuid,
user_id integer,
created_at timestamp,
amount numeric
);
create index idx_transactions_user_id_created_at ...
- 111
6
votes
1
answer
326
views
Demystification of SQL Server optimization process [duplicate]
We would like to see all variants of query plan considered during a query optimization by a SQL Server optimizer. SQL Server offers quite detailed insight using querytraceon options. For example ...
- 253
4
votes
2
answers
5k
views
How to optimize Mysql database of 250 million rows for bulk inserts and selects
I am a student, who have been given the task of designing a database for sensor data. My university currently has a large database which is being filled with these data, but a lot of what is being ...
- 43
10
votes
2
answers
4k
views
Building an execution plan takes too long on SQL Server
I have an SQL Server 2014 instance (12.0.2000.8) and a quite complex SELECT statement with about 20 joins. This query works fine with the same data set on PostgreSQL, Oracle and other databases, and ...
- 421
0
votes
1
answer
131
views
How long does query optimisation take relative to the execution time?
Could anyone please point me to a benchmark comparing the time spent by the query optimiser relative to the overall query execution time (any relational DBMS will do, but PostgreSQL would be ...
- 1
1
vote
1
answer
993
views
Optimizing concurrent read performance
I wrote a program to test concurrent reads to a single database table. I'm expecting high amounts of read (no deletes, inserts, or updates) traffic in the future, using ad hoc queries, which is why I ...
- 149
0
votes
2
answers
2k
views
Postgres: Performance: Slow index scan
I have a really big query, that queries data from various tables. I have tried various approaches to optimize the query.
Made possible LEFT JOIN as LEFT LATERAL JOIN, so that the lateral join queries ...
- 131
1
vote
2
answers
165
views
Detection of contradictions
I found some old notes of mine from 2011 and I also re-read the excellent article 10 Cool SQL Optimisations That do not Depend on the Cost Model by @lukas-eder. So I thought I would take my notes for ...
0
votes
2
answers
193
views
Mysql query time massively affected by limit
I have this mysql query, where I am getting 250ms query time when the limit is 500.
If the limit is 1000, it's 1,5 seconds
if the limit is 1300 , it's 2,5 seconds
select hotel_code,
...
2
votes
1
answer
368
views
Optimizing cloud server (php and mysql)
I am running a vBuletin forum as a hobby and I was using a shared hosting service until a couple of days ago. Because the amount of visitors grew and the forum seemed to be slower and slower every day,...
- 31
1
vote
0
answers
192
views
Anomalous performance of mass INSERT in Oracle
Can you please help me diagnose, explain, and possibly solve the
problem of unstable peformance of mass insertions in Oracle?
I need periodically to load large amounts of data into an Oracle
database. ...
- 248
18
votes
3
answers
7k
views
Colleague says to never use an OR statement in SQL, is this true?
My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't ...
- 321
1
vote
2
answers
238
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 `...
- 537
0
votes
2
answers
53
views
Optimize duplicated but negated subquery
I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
(b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
- 101
0
votes
1
answer
2k
views
check if each value of array are equal
I try to get d.id only if all rows with same id have same pday:
with dif_zero as (d.id,
date_part('day', p.docdate)::text pday
from sh_billing.dogovor d
join sh_billing.pays p on p.dogovor_id = d.id ...
0
votes
1
answer
706
views
Decrease Data IO operation on query to lower the DTU cost on Azure
We have a FunctionApp in Azure that triggers on a timer and does some DB operations every 5 minutes. These operation are taking a very long time to complete. When I run the same query on my computer ...
- 199
0
votes
1
answer
80
views
Statistics not collected on Insert-Select
Based on the information here, I expected the statistics to be automatically collected during an Insert-Select operation on a new table in Oracle. However, I don't see any table-level statistics ...
- 101
3
votes
1
answer
839
views
Query optimization - unread messages in all channels of a conversation
I have a slow query which I'm trying to tune. Tables in DB (mssql):
Users, ChatGroups, ChatMessages, ChatGroupChannels, ChatMessageSeenFast
The query:
WITH messages_ranked AS( SELECT p.*, ROW_NUMBER(...
- 185
0
votes
1
answer
98
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 ...
- 105
1
vote
1
answer
151
views
PostgreSQL Sorting an output of a fulltext search query takes very long time
I have a table with a few millions of rows where I perform a SELECT with a complex fulltext search query in the WHERE clause (over a fulltext search index column) and with ORDER BY id DESC at the end. ...
- 111
2
votes
2
answers
995
views
SQL Server Index Scan when using 'OR' operator
We implemented a Google-style search where SQL queries are run after a debounce is triggered from the front-end. (We know SQL is probably the wrong technology for this, but I'm knee-deep in startup-...
- 275
1
vote
1
answer
1k
views
How to run my query on a one-to-many relation to run efficiently
I have two tables in one-to-many rel, I want to run a query efficiently returning data from the base table and some aggregates from the joined subtable. Adding in some indices, I still did not manage ...
- 163
1
vote
2
answers
687
views
Postgres NOT NULL optimization
I am trying to optimize this SQL query:
select topics.id from "topics"
left join "articles_topics" on "topics"."id" = "articles_topics"."...
- 129
10
votes
1
answer
3k
views
Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?
I am trying to work out ways to improve the performance on a very slow query. It has more obvious problems but one thing I noticed is that one of the conditions in the WHERE clause is 'AND t.data IS ...
- 341
1
vote
1
answer
1k
views
Query takes long time to execute while there is an index configured
We have a table with 190M records. there is an index on a fairly unique column but still, if I run a query on the table selecting top 10 with only the indexed column in the where and select clause it ...
- 553
11
votes
2
answers
561
views
Inner join elimination inhibited by prior outer join
Synopsis: Inner joins that can be logically eliminated are instead retained if there is an non-eliminated outer join earlier in the logical tree. Why?
Examples run in AdventureWorks2008R2 and later. ...
- 869
3
votes
1
answer
921
views
Postgres Query Optimization, high cost functions
Currently I'm working with a postgres table that looks like this (postgres12)
create table if not exists asset (
id text,
symbol text not null,
name text not null
primary key (id)
);
create ...
- 133
3
votes
1
answer
589
views
What is search space when it comes to query optimization?
I am quite new to learning about databases and I have come across the term "search space" in the context of query optimization. I am a little unsure of what the term means and why it is ...
- 41
3
votes
1
answer
271
views
What exactly does PostgreSQL (or other databases) do internally when you "prepare" a query rather than just running it directly?
When I face a huge number of repeated INSERTs in a loop, I tend to first create a "prepare skeleton query" prior to the loop, and in the loop, I simply "execute" this prepared ...
user214427
3
votes
1
answer
79
views
Query optimisation
I want to optimize 2 queries from 2 tables(orders, actions) with relation one to many
Now I make one select from orders:
SELECT "id"
FROM "orders"
WHERE "orders"."...
- 142
7
votes
3
answers
711
views
Consistent query performance over time
We're running an intensive application load (thousands of operations/second) against a SQL Server database with quite a bit of data. Some tables have billions of rows, several of these have lots of ...
- 173
0
votes
2
answers
89
views
How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?
I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys ...
- 25
0
votes
1
answer
66
views
Have I misunderstood the amount of "automatic optimization" that PostgreSQL does without me having to be "clever" when designing my tables? [duplicate]
Suppose I have a table such as this, for logging all web requests made to my website:
CREATE TABLE weblog
(
id bigserial,
moment timestamp with time zone NOT NULL DEFAULT now(),
...
user213808
1
vote
2
answers
617
views
Creating an Index based on Execution Plan
I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the ...
- 205
5
votes
1
answer
133
views
Logical trees: difference between Before Project Normalization and Join Collapsed
The Before Project Normalization tree follows the Join Collapsed tree.
Using a sample set of ~250 queries - and Traceflags 8605, 8606, 8607, 3604 - I have not detected any difference between these two ...
- 869
1
vote
0
answers
93
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 ...
- 131
2
votes
0
answers
548
views
Correlated subquery with common table expressions in SQLite
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
- 131
0
votes
1
answer
101
views
Which SQL table statistics are used after they are updated when NORECOMPUTE is turned on?
Background:
I have a large table whose values are significantly rewritten with a switch ~once a week, but the table's primary key values remain relatively constant. After the switch, statistics are ...
Matt Owen
0
votes
1
answer
72
views
How to benchmark Switchable Optimizations in MySQL
I want to benchmark the performance of Switchable Optimizations in MySQL, i.e., comparing the query latency and the result of EXPLAIN with/without using these optimization switches.
I have already ...
- 233
0
votes
1
answer
113
views
Avoid duplicate WHERE clause on both sides of a LEFT JOIN, without changing semantics or impairing query optimization
I have a table recording the results of a web crawl. (Table
definition at the end of this question.) The relevant part of the data
stored in it looks like this:
experiment_id | url_id | ...
- 251
2
votes
2
answers
240
views
How to create index to improve performance of an aggregate function that creates a table in oracle
I am creating an Oracle ORDS API using APEX_JSON. I recently started using bind variables instead of string concatenation using ||. I am trying to use an in clause in my where condition.
The problems ...
- 23
0
votes
0
answers
21
views
High-performance query of two tables with count from second table? [duplicate]
I am searching for a high-performance query using two tables to output all locations (name) from country (adm0 = 'DE') in the geodb_locations table, sorted in descending order by the number of users ...
- 41
1
vote
1
answer
89
views
Optimizer logical trees: Order of the different tree types?
Based on observing output from Traceflags 8605, 8606 and 8607 (and comments from i-one), I have this as the order in which the logical trees are generated.
Converted
Input
Simplified
Join-Collapsed
...
- 869
1
vote
1
answer
534
views
How to improve performance merging multiple table via cursor
I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query
BEGIN
DECLARE done INT ...
- 111