Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
642
questions with no upvoted or accepted answers
8
votes
1
answer
164
views
SQL Server: how Row Goal affects the same index operation?
I am tuning some SQL performance in the SQL Server 2019, and I ran into issues with one query that involves the issue with the row goals. The simplified query is:
select TOP 1 ISNULL(RUNID,-1)
from ...
- 191
8
votes
1
answer
175
views
Why does only one version of these procedures get blocked under moderate load?
The table:
CREATE TABLE [dbo].[Session] (
[SessionId] UNIQUEIDENTIFIER NOT NULL,
[CID] INT NULL,
[DEST] VARCHAR (50) NOT NULL,
[...
- 316
8
votes
0
answers
2k
views
How to measure the memory usage of a Mongodb aggregation?
I recently started running into the Sort exceeded memory limit of... exception on an aggregation pipeline in MongoDB. I got around this by setting AllowDiskUse=True for the aggregation. However, I ...
5
votes
0
answers
483
views
Postgresql row-level security generates different query plan from manually inserted WHERE clause
(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
- 163
5
votes
1
answer
8k
views
Materialized Views and Indexes
Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
- 61
4
votes
0
answers
1k
views
PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice
This is the story if a nightmare... I have this table:
Table "subscriptions"
Column | Type | Collation | Nullable | ...
- 623
4
votes
1
answer
2k
views
Slow postgres query that uses quick sort instead of hash-aggregate
I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic.
I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given ...
- 1,094
4
votes
1
answer
2k
views
Why does postgres choose to sort instead of scanning the index?
I have the following table:
# \d service
Table "public.service"
Column | Type | Modifiers
-------------+----------+-----------
customer_id | integer | not null
date | date ...
user109810
4
votes
1
answer
146
views
help required with indexing and query optimzation
After browsing most of the posts and after running out for options I am posting here.
I have a table with 76,222,954 (76M) rows. It collects web site traffic based on country.
Here is my table ...
- 41
4
votes
1
answer
811
views
Optimizing intersect query in MySQL
I'm facing a performances problem in our MariaDB server.
We have a table linking users to pages, a user belongs to 1+ pages.
I have a page that have users and I want to get the 10 best pages based ...
- 141
4
votes
2
answers
641
views
poor and/or unreliable InnoDB performance
I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped.
I have a database containing four InnoDB tables. The largest ...
- 273
3
votes
1
answer
52
views
fetching data from 2 10M+ rows tables
I need to run the following query on my production database, using 2 tables that contain more than 10M rows each :
SELECT rm.parent_id as processed_media_id
FROM render_medias rm
JOIN processed_medias ...
- 131
3
votes
1
answer
1k
views
Why does my query take a long time to run, and why does changing the order of the joins make it quicker?
I have a query in sql server that takes close to 3 minutes to run. Interestingly enough, when I change the order of one of the joins the performance improves dramatically and it takes only about 20 ...
- 31
3
votes
0
answers
281
views
Slower delete performance in MySQL8 compared to MySQL5.7
I'm looking for some insights into why deletes from a fairly simple table seem to be much slower in MySQL8 than in MySQL5.7
Apologies in advance if this is a little fluffy at this stage and I've ...
3
votes
0
answers
304
views
Execution Plan changes between good and bad query plans (Azure SQL Server)
I've done my best to follow Why is my query suddenly slower than it was yesterday?
The ShowPlanXML parses fine with SQL Sentry Plan Explorer but not via brentozar.com/pastetheplan (not sure why) so I ...
- 175
3
votes
0
answers
73
views
Is there any way to optimize this query (generated by Drupal)?
I have an older system built on drupal, and there is a particular (generated) query that takes a long time - never completing in some cases.
The query:
SELECT
node.nid,
node.created,
node....
- 1,222
3
votes
1
answer
402
views
How to optimize simple table with groups and timestamps (SUM over 15M rows with GROUP BY)
I think I have quite simple question, but I couldn't find the answer anywhere.
I'm using MySQL and I have a simple table:
id
timestamp
groupId
costA
costB
costC
...
1
2022-02-01 19:45
1
5,13
3,20
30,...
- 31
3
votes
0
answers
713
views
MariaDB - Query to find unused indexes
I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this ...
- 69
3
votes
0
answers
925
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 (...
- 131
3
votes
1
answer
2k
views
What are the methods to paginate a table 100 rows at a time?
Let's say we have a table with a large number of records. Now to show all the data using pagination in a basic framework using a MySQL query, we can use limits to get a subset of rows:
SELECT * FROM ...
- 131
3
votes
0
answers
643
views
How to optimize this recursive postgresql query?
I've been searching a lot on this topic but I'm stuck at optimizing a query.
I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
- 131
3
votes
0
answers
2k
views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
- 256
3
votes
0
answers
88
views
What is a cost of search for records in a table with an equality predicate on a non key attribute A and a static hash organization?
To speed up the search for records in a table with an equality predicate on a non key attribute A and selectivity factor fs, is preferable:
A sequential organization on a key attribute ?
A static ...
3
votes
1
answer
2k
views
SELECT Query is slow when run remotely in MySQL
When I connect MySQL from remote server and run below query then the results are very slow(Sometime even faced lost connection Issue). However when I run the same SQL query on the local machine then ...
- 31
3
votes
0
answers
106
views
Understanding Database Query Cost Optimzation
I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in ...
- 39
3
votes
0
answers
124
views
Performance differs on same query against two (almost) identical tables?
I'm running these two queries in SQL Server 2012:
SELECT tpua.Field1,
CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2,
CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
- 585
3
votes
0
answers
83
views
Tagged questions
I have similar to StackExchange network that utilises tagged questions.
In my web application Users also can find questions by tags. The query below returns a set of tagged questions:
SELECT ...
- 151
3
votes
0
answers
1k
views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url column, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
- 274
3
votes
1
answer
242
views
Why is it so fast to create an index on a table?
While optimizing my query in Postgres, I noted that creating an index is always very fast, less than 1 second on a table with millions of rows.
But if there's no index, a query would take a few ...
- 357
3
votes
4
answers
289
views
Optimizing slow query
For a few days now, i'm trying to optimize this query.
But still I haven't found the right solution in how to fix this.
The contents table contains about 20 million records. As the connect table ...
- 31
3
votes
1
answer
5k
views
How can we improve this PIVOT operation? ( SQL Server 2008 R2 )
We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query.
select top 100 *
from (
select codCliente
,...
- 7,436
3
votes
1
answer
308
views
query optimization of a left join
I have the following query written into the slow log (mysql-slow.log).
I don't have much idea how to optimize this query. I already have indexes for:
mode,
f.user_id... etc
but doesn't seem to be ...
- 143
3
votes
1
answer
336
views
GROUP BY very slow when returning many rows
I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than O(...
- 345
3
votes
2
answers
278
views
Changing database compatibility from legacy CE to 120
Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)
From DEV point of ...
- 2,190
3
votes
1
answer
1k
views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
- 465
3
votes
1
answer
136
views
How can I improve my query performance?
I'm building a statistic application based on Django & PostgreSQL with focus on performance and maintainability.
The problem is, that data is distributed among multiple relatively large tables.
...
- 475
3
votes
2
answers
145
views
Optmizing ORDER BY
I'm new to MySQL and have written the following query (see below).
PROBLEM:
The query returns accurate data but takes about 40 seconds to execute.
However, When I remove the ORDER BY part, it takes ...
- 131
2
votes
1
answer
40
views
Oracle optimizer does not use column group information from multiple tables and misses correlation
I have a problem where the execution of a query is too slow in oracle 12c standard edition.
I figured out, the optimizer estimates wrong cardinalities because it misses the correlation between two ...
- 21
2
votes
0
answers
95
views
Query not using foreign key indexes
I have the following schema for my database:
CREATE TABLE companies (
id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE employments (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
...
2
votes
1
answer
178
views
random_page_cost and memoize plan relation
Postgres 15.2. I have the following simple query:
SELECT mark.last_modified FROM mark
INNER JOIN element ON mark.element_id = element.id
INNER JOIN model ON element.model_id = model.id
WHERE ...
- 135
2
votes
1
answer
121
views
MySQL showing randomly poor performance with nested loop antijoin
I have a very simple query which is randomly performing poorly in production. I have the EXPLAIN ANALYZE outputs for both the good case and the bad case.
MySQL version is 8.0.31.
This is the query:
...
- 121
2
votes
0
answers
22
views
set options for replicating slow queries in SSMS
I am investigating a slow ad-hoc query(about 45 seconds) from our application. I am not able to replicate the slowness in ssms however to investigate indexing, reads, etc. I've attempted to use the ...
- 257
2
votes
1
answer
177
views
PostgreSQL imbalanced index query optimization
I am having performance issues with my query because the index is heavily imbalanced. I have a column family_id on a table. The table has about 150 million records. This family_id column identifies ...
- 121
2
votes
0
answers
173
views
deadlock on too many requests and DoS occurred, Why?
I ran into a strange monitoring DBA error "Deadlock" when I try to Pentest my web app. (Send lots of request to DB then Deadlock Occurred.)
Why did this occur?
I add the Deadlock XML Too:
...
- 121
2
votes
1
answer
965
views
Why would INSERT, REPLACE or UPDATE statements sometimes take over 1 second to execute?
I have a very good server (great CPU, huge RAM, NVMe) with a huge InnoDB buffer pool that's not full yet.
The website is very write-intensive, but it runs fast enough (SELECT queries well optimized, ...
- 839
2
votes
0
answers
374
views
Service Broker performance issues after upgrading from 2012 to 2017 - tons of LCK_M_IX
Last night we upgraded a SQL Server 2012 server to SQL Server 2017 + CU 29. This is one of a matched set - we send the same message to both and each processes it. No, this is not AlwaysOn; we're ...
- 433
2
votes
0
answers
214
views
Why does SQL Server 2019 stored proc get Adaptive Join on Development Server, but not on Production Server?
I have a stored proc with one select statement that gets a plan w/ Adaptive Joins on my development server, but not on my production server. What could explain that? I'm not much interested in ...
2
votes
2
answers
703
views
Slow query for multiple data at same timestamp
Hi everyone,
I'm currently facing a problem to query efficiently my database to retrieve all rows at same timestamp.
I will explain,
I have two table of data using TimescaleDb Hypertable with ...
- 31
2
votes
2
answers
3k
views
Optimizing a recursive CTE or replacing it with a temporary table
I have a recursive query like this:
with recursive PCte(id) as
(
select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in
('123', '124','125', 'n')
union all
...
2
votes
0
answers
211
views
How to determine the Tipping Point of which Physical Join Operator gets used to process a query?
I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
- 38.6k