Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,448
questions
0
votes
1
answer
28
views
Unstable plans when two first index columns have NULL values in all rows
I have a weird case of a query that takes long to execute despite having good matching index. The thing is that the values of the first 2 columns in a non-clustered index are NULL in all rows. (For ...
- 156
0
votes
1
answer
21
views
Difference between RLS and WHERE condition (e.g. in views)
Is there an functional advantage or performance penalty in using RLS on table vs. using a condition on a view of such table (while hiding the table)?
It seems to me, than rather than using RLS on a ...
- 107
0
votes
0
answers
9
views
What do CPU_User and CPU_System measure in MySQL?
When using profiling in MySQL, we can access various types of information, including CPU_User/System. However, I'm confused about how MySQL calculates these metrics.
For example, sometimes a query ...
- 627
0
votes
0
answers
45
views
Process for identifying why a query slows down during times of heavier activity
I'm still a bit of a newb when it comes to troubleshooting and diagnosing issues with slow-performing queries in SQL Server.
Basically, there is a query that runs throughout the day as users pull ...
- 21
1
vote
1
answer
112
views
Query is fast in the proper DB, slow in master
I have a SQL Server 2019 test instance running on VMware that hosts a copy of our production Dynamics CRM database. The reporting team wants to query the database, so we have an AD service account set ...
- 181
0
votes
1
answer
23
views
Inconsistent query planners between two Postgres 16.4 instances
I am trying to understand why the query planner is being so unoptimal on my DO managed Postgres 16.4. I have installed Postgres 16.4 on my Windows dev machine.
I restored the same database to both, ...
- 101
0
votes
1
answer
69
views
PostgreSQL Query with LIMIT Takes Long to Execute When Fewer Rows Are Left (Last Page of Pagination)
I'm facing a strange issue with my PostgreSQL query related to pagination. I have a simple pagination setup, but whenever the number of rows left to retrieve is less than the LIMIT specified in the ...
- 1
0
votes
1
answer
32
views
Sqlite fetching speed problem - how to understand explain and compare two queries
I need to join two tables product_features and feature_text to get this output
From a previous question i got two answers that both return the expected result for a small sample set: join so that
...
- 219
0
votes
2
answers
35
views
How to optimize multiple WHEN that are using differents static string?
I have a table like that:
Id
Note
Client
1
Long note that havemultiple linesClient: Name
null
2
Anoter note that have multiple linesCustomer: Name
null
3
Third note that have multiple linesClient :...
- 181
0
votes
1
answer
37
views
Select by Tuples in PostgreSQL
I have a table user_issues with 3 columns: id, component_id and path and I need to filter it but tuples of component_id and path.
So I used the following query in order to achieve this:
SELECT id
...
- 175
0
votes
0
answers
41
views
Indexing and optimize large query with multiple joins
I have search query with multiple table join as
SELECT
DISTINCT pp.person_profile_id,
pp.middle_name,
pp.prefix,
pp.first_name,
pp.last_name,
pp.suffix,
pp.privacy_type,
...
- 101
-3
votes
1
answer
46
views
How can I retrieve all hierarchical user data from MySQL tables?
This is a simplified version of my MySql tables.
User table
user_id
user_name
manager_user_id
1
John
Null
2
Maria
1
3
Rob
2
4
Alex
3
User Data table
id
user_id
some_user_data
1
2
abc
2
3
def
...
0
votes
1
answer
83
views
Sql Server - analyze sql script changes on a Database
Need your insights. Thank you for taking time and sharing.
Context
We have a 10 sql scripts which has more than 10000 lines of code and most of them are scripts and some code in each script are ...
0
votes
1
answer
32
views
Understanding the REF column in MySQL EXPLAIN
I have few questions about the REF column in the EXPLAIN output. MySQL Version 5.7.31 Community Server.
The table structure
| table1 | CREATE TABLE `table1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,...
- 216
0
votes
2
answers
62
views
What is the cause of this intermittent slow query when counting rows over a single indexed text column in PostgreSQL?
I have a table with 2,395,015 rows, where a TEXT column has one of three values and is never NULL. I have intermittent query performance issues when counting the number of rows where the value matches ...
- 389
1
vote
1
answer
58
views
Impact of Reordering Composite Index on PostgreSQL: Performance, Space, and Overhead Considerations
I am optimizing SQL query performance by reordering a composite index in PostgreSQL. I need to understand potential repercussions, including space usage, data saving overhead, and any other impacts of ...
- 13
1
vote
1
answer
35
views
Optimizing Query to Retrieve Names from pg_buffercache View in PostgreSQL
I had a database with large amount of tables / partition tables where I need to retrieve the database name, relation name, parent relation name and tablespace name instead of their respective OIDs ...
- 55
1
vote
2
answers
133
views
SQL Server Query View / Performance Issues
I'm dealing with performance issues with a 3rd-party view that's pulling from our production db. Troubleshooting is currently being directed at the hardware of our SQL Server box. Just to give you an ...
0
votes
1
answer
44
views
How to Index/Query JSONB Columns with Arbitrary Keys for Efficient Membership Queries in PostgreSQL?
In Postgres, how do I properly index/query jsonb columns that only contain key-value pairs where the keys are arbitrary, to speed up membership operations?
I have a table structured like this:
CREATE ...
- 111
0
votes
1
answer
112
views
Simple query taking way too long
I have a simple query, using an indexed column, on a very small data set, returning 1 row, hitting Azure SQL Database - taking over 1/2 second. Another earlier execution of this query took 38ms.
...
- 177
0
votes
0
answers
25
views
Postgresql calculate SUM value in time for all items in a table with gaps
I would like to calculate SUM value in time for all items in a table "measures", which contains measures from stations (each one defined with an identifier)
_ id_station (varchar)
_ ...
0
votes
0
answers
27
views
Working around Materialize node in PostgreSQL explain plan
I'm currently trying to optimize the following query for a school assignment:
SELECT
f.aircraft_code,
s.seat_no,
SUM(tf.amount) total_amount
FROM seats s
JOIN flights f ON s.aircraft_code =...
1
vote
1
answer
84
views
Trouble with slow stored procedure performance on 35M rows with CTE/Temp table
We recently went through a new feature release to our app that added about 25 new columns to a large table (~35 million rows), and now we're having some major query performance issues. I assume that ...
- 127
0
votes
1
answer
35
views
Manage the amount of data on tables
I can have several tables with 10+ millions rows in by year.
I would to know what's the best approach to maintain responsiveness with this amount of datas and I have two ideas.
Don't touch these ...
- 1
0
votes
0
answers
14
views
Variable time execution for INSERT/UPDATE
I do an INSERT/UPDATE in a table who has around 1+ million rows in MySQL 8.0. I don't know why I have different execution times for the same data to insert.
For example, I try to insert exactly the ...
- 1
0
votes
2
answers
90
views
PostgreSQL uses slow Index Scan instead of Bitmap Heap + Index scan with ORDER BY and specific LIMIT
Given the following table:
CREATE TABLE chat_message (
id bigint DEFAULT nextval('public.chat_message_id_seq'::regclass) NOT NULL,
"user" integer,
type smallint,
text text
);
...
- 129
-1
votes
1
answer
26
views
How do we optimize a transaction-log VIEW in MySQL that will use a LIMIT when executed?
Problem / Observation
A query that performs well outside of a view (with a limit 20), uses a suboptimal query plan when placed inside of a view if the limit 20 is not included in the view definition ...
- 9
0
votes
1
answer
75
views
How to optimize query with multiple many-to-many relationships
I have a performance issue when querying an events table that has over a million rows. Our application logic stipulates that users can only access events that they have access to via their 'groups'. ...
- 103
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
0
votes
1
answer
61
views
How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?
Context
Here is the DDL that I am intending to use to define the table for a logistics/delivery company.
CREATE TABLE scraping_details (
id INT IDENTITY(1,1) PRIMARY KEY, -- Identity insert and ...
- 73
0
votes
1
answer
57
views
Erratic Performance of CLUSTER in PostgreSQL
I work with PostgreSQL 12 and have partitioned tables that I am trying to cluster. Each partition is roughly the same size in terms of GB. However, the performance can vary significantly, ranging from ...
- 121
0
votes
1
answer
33
views
Question related to Database Design in SQL Server
I have two tables: Products and ProductVersions. The table structures are as follows:
Products Table:
ProductId
RegionId
ActiveProductVersionId
isDisabled
ApplicationId
ProductVersions Table:
...
- 527
2
votes
1
answer
96
views
SQL Server Underestimating cardinality on a Filter operator in a Left Anti-Join
I am tuning a query which is slow, I have narrowed the root of the problem to be at the very beginning of the execution plan, where SQL Server makes a bad estimate on a WHERE IS NULL filter that ...
- 2,033
7
votes
1
answer
711
views
Execution slow in stored procedure; fast when run ad hoc
I am dealing with an issue that I can't seem to solve. I was given this stored procedure that has speed issues. It takes 35 seconds or so. When run adhoc, it completes instantly.
This made me think it ...
- 73
1
vote
1
answer
116
views
Why does MySQL choose such a poor query plan for "IN (SELECT NULL WHERE FALSE)"
We have some code that goes through a SQL generator to produce parameterized IN clauses of the form WHERE id IN (?, ?, ?, ...) AND ....
If the list is empty, the generator cannot issue IN () since ...
- 155
0
votes
2
answers
34
views
Should I create a separate table for non null user_id values in my MySQL database schema for performance?
I have a MySQL database schema for storing uploads, where each configuration can be associated with a user account (user_id) or uploaded as a guest without an associated user account (null user_id). ...
0
votes
1
answer
52
views
Performance issues with time interval check
I'm running the following Postgres query where I join a consumer's usage with the service cost for that interval based on when they used the service from the service table. The query below is a ...
- 3
0
votes
2
answers
76
views
How to simplify this intersect query?
SELECT * from M
left JOIN K on M.id = k.id
left JOIN W on K.str = W.str where str = N'أخبار'
INTERSECT
SELECT * from M
left JOIN K on m.id = k.id
left JOIN W on K.str = W.str where str = N'فلسطين'
...
- 99
0
votes
1
answer
45
views
Postgres 15 column drop impact
Is the drop column operation scalable?
Previously, we faced issues while updating a large number of records because it took more space.
Is this the case with a drop column operation?
- 261
4
votes
2
answers
192
views
Index seeks and predicate push down
I have a query of the following form:
IF EXISTS (
SELECT 1
FROM (
SELECT RowID, OETID
FROM @InMemoryTableTypeTable i
UNION
...
- 165
1
vote
2
answers
133
views
Oracle SQL query slow even with indexes
I have an Oracle 19c database (19.0.0.0.0), and two tables with lots of columns, I'll mention only the relevant ones:
table1:
createtime date
contact_key char(96 byte)
table2:
contact_key char(96 ...
- 143
0
votes
0
answers
50
views
Mongo DB slow deletes due to indexes rebuild , same with TTL indexes
We are having huge collection which we can not partition ( we need to get the records by last updated time which can not be known ). To decrease the storage we have enabled TTL index based on last ...
2
votes
2
answers
61
views
How can I find a topmost row and count the rows with only one scan?
Suppose that I have a schema like the following:
-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);
-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
SELECT ...
- 123
3
votes
1
answer
50
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
1
vote
0
answers
33
views
Very slow to work with table using PostgreSQL [closed]
I have a table labelled SALES. It has indexes like this list
"sales_pkey" PRIMARY KEY, btree (id)
"sales_order_date_idx" btree (order_date)
"sales_product_id_idx&...
- 111
0
votes
1
answer
37
views
Get the statistics_stream on SQL server using SSMS
I have generated statistics for a few columns of a table using the following SQL command:
CREATE STATISTICS [STAT_NAME] on [TABLE_NAME] (COLUMN_NAME) WITH FULLSCAN
The generated statistics are ...
0
votes
0
answers
51
views
Azure SQL Database :: table size query takes forever to execute
I was working with the awesome Azure SQL Database Diagnostic Information Queries created by Glenn Berry and I realized that the query number 40 takes forever to execute:
-- Get Schema names, Table ...
- 1,619
1
vote
0
answers
78
views
Poor Postgres Query Performance with a Partitioned Table - pg_hint_plan does better
Simplified, I have a table with this layout:
CREATE TABLE IF NOT EXISTS public.scans
(
id bigint NOT NULL DEFAULT nextval('new_scans_id_seq'::regclass),
geo geometry(Point) NOT NULL,
...
- 11
0
votes
0
answers
28
views
How to tune below query
Please help suggest how to tune below postgres query.
select distinct c.aid, jsonb_array_elements (cvp.value::jsonb) as somejson
from
cont c
join compara_values cvp on c.id=cvp.cont_instance_id
join ...
- 13
0
votes
1
answer
106
views
Does long running query cause replication delay in PostgreSQL which is in 'idle in transaction' state?
In my system I found same query called more than 30 times 'idle in transaction' state. Is there any way to to identify the reason of 'idle in transaction'? Should I terminate those query manually ...
