Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
6 votes
1 answer
585 views

How to reduce query size with many repeated UNION subqueries?

I use Postgres 13 and have a table defined with the following DDL: CREATE TABLE item_codes ( code bytea NOT NULL, item_id bytea NOT NULL, time ...
Vitalii Vitrenko's user avatar
2 votes
2 answers
142 views

Straightforward inner join is slow despite using indices and ref/eq_ref types

I have a couple moderately-sized MySQL 8.0.30 InnoDB tables: models - 1,000,000 rows activations - 10,000,000 rows A fairly simple INNER JOIN takes about 500ms, which seems like a lot (is it?). This ...
mgiuffrida's user avatar
3 votes
1 answer
732 views

How To Optimize a Query That Aggregates Then Duplicates a Large Number of Records in a Single Table

In the following post, J.D. brings up that I have a poorly performing query. I am running this query on SQL Server 2019 Standard Edition (query plan was generated in Development Edition) Let's take a ...
David Rogers's user avatar
0 votes
0 answers
70 views

PostgreSQL query times out when selecting a smaller amount of data (different query plan)

PostgreSQL 15.2. We have a table like this (simplified): create table behavior.event_59 ( event_id integer not null primary key, second_offset integer not null, -- ... more columns ) ...
EM0's user avatar
  • 190
0 votes
0 answers
127 views

Redshift / Postgresql query multiple tables faster than Natural Full Join

I have been given a requirement to query 8(+) different Redshift tables and return some of the fields where email = provided email address. Current implementation: (select product_name, email, ...
AylaWinters's user avatar
1 vote
1 answer
77 views

keeping order rows saved to calculate rank and percentile

we have a large table, around 12M records, everyday at midnight, we run a query to sort all rows by a certain criteria and save the order in a column. the reason we do that is because we have a ...
Hassan Khallouf's user avatar
1 vote
2 answers
88 views

MySQL with subquery becomes slow

I have 3 MySQL tables: products, orders and users. I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple ...
screaming SiLENCE's user avatar
0 votes
0 answers
65 views

How to get SQLs per network consumption?

On Db2/Linux_x86 version 11.5.8.0 we have several Db2 instances. With iptraf-ng command sysadmin has noticed that one of this Db2 instance has 20-times more output network traffic then others which is ...
folow's user avatar
  • 325
0 votes
2 answers
1k views

MySQL select query slow despite having indexes

Have this rather simple SELECT on just one table: SELECT id AS price_id FROM prices WHERE product_id IS NOT NULL AND product_id > 1 AND search_name_updated IS NULL LIMIT 1000 For some reason, the ...
Mads's user avatar
  • 15
1 vote
1 answer
188 views

Returning array of JSON objects from related fields in PostgreSQL

I have the following schema and some data inserted into the DB. Schema (PostgreSQL v14) CREATE TABLE users( id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE, password TEXT NOT NULL, ...
Sirjon's user avatar
  • 65
1 vote
1 answer
156 views

Oracle query taking a very long time to execute

I am trying to run a couple of reconciliation queries to identify missed deletes from our change data capture tool, but I need it to run relatively fast... My source and target tables are identical ...
Lauren_G's user avatar
1 vote
0 answers
168 views

Optimizing UPDATE multiple tables with specific commit size

I'm trying to optimize my procedure for updating multiple tables with huge amount of data. (millions of rows). The commit size is set to 50.000 to fit memory capacity. Here is what I have now. The ...
in3des's user avatar
  • 11
0 votes
1 answer
328 views

Postgresql perform sequential scan instead of index scan for indexed column

I'm currently using PostgreSQL 14.0 for windows My target table has a 900k+ rows and my query is using single column filter (status_id). The column status_id is indexed via btree (default) When i ...
starting dba jr's user avatar
3 votes
1 answer
123 views

Improving query response speed when joining a non-spatial table with a spatial table on a non-spatial column

I have a table of frequently updating events that affect certain geometries. The geometry areas are fixed, but there may be an event affecting multiple areas at once, so there's a many-to-many ...
wxdan's user avatar
  • 33
0 votes
1 answer
783 views

Bad inner join performance on one-to-many relationship

I have a question regarding the postgres planner that I can't wrap my head around. I am trying to get the planner to look up a project ID by its unique key before running the other side of the query ...
mreaglejr's user avatar
0 votes
2 answers
534 views

MariaDB - Mysterious Delay at Each Query

I have 2 tables on MariaDB (10.5.16-MariaDB-log) where I query table A, and then loop over the roughly 4000 results and for each one do a query on table B. (This is inherited code and this will be ...
Deefo22's user avatar
  • 11
0 votes
1 answer
146 views

getting rows and adding a column to each with the timestamp of the next row, with postgres

I have the following object: CREATE TABLE IF NOT EXISTS {schemaName}.{tableName} ( id VARCHAR NOT NULL, ticker VARCHAR(16) NOT NULL, interval ...
Thomas's user avatar
  • 273
0 votes
0 answers
163 views

Materialized view slow with Timescale

I have a table created like this: -- enable timescale plugin CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; -- create schema CREATE SCHEMA IF NOT EXISTS {schemaName}; -- create table CREATE ...
Thomas's user avatar
  • 273
1 vote
1 answer
719 views

Optimize querying 100 million records- Postgres - indexes

Currently our queries are not performant. Table: (Item) 100 million rows. id: uuid user_id: bigInt item_type_id: uuid status: varchar start: date end: date Index on user_id, item_type_id Query: ...
trenatoid's user avatar
0 votes
0 answers
75 views

Hierarchical query to select `children` from `children`

I have the following table mytable where id is a unique identifier at a given r (resolution) and children is an array of unique id's of r + 1. id r children[] 0 2 {0,2467972595799031808,...
Michael's user avatar
  • 275
0 votes
1 answer
486 views

Why query takes ages when I filter by an indexed boolean column?

I have a query that filters on a boolean column that has index. But, the query takes ages to finish. When I don't use this filter, the query returns very quickly. Here are the explain plans. The first ...
p.matsinopoulos's user avatar
1 vote
1 answer
75 views

MySQL: Materializing table function take so long

Thanks for checking my question I encountered a strange issue. the same query in same db return different actual time here is explain analyze statement explain analyze SELECT COUNT(1) AS `cnt` FROM `...
YOSEPH NOH's user avatar
0 votes
0 answers
77 views

Which is faster between regex and string length function, in searching string of different length?

I have a table named table1 having 2 columns named as user_id and col1. The table contains 5 million rows. This column col1 contains 3 types of values: first ending with char 'x' and string length 13; ...
abhishek's user avatar
0 votes
0 answers
115 views

Find the I/O cost of the following question?

Estimate the I/O cost of retrieving records from Parts table that contains price less than or equal 1000 rupees. Assume that 15% of tuples satisfy the selection criteria. There are 3300 pages in the ...
Azr.em's user avatar
  • 1
0 votes
1 answer
100 views

Different performance of SQL Server instances on same server

I have 4 development SQL Server 2016 instances on a virtual server (128 GB, 12 virtual processors). Each instance is configured the same (16 GB memory, automatic processor and I/O affinity masks). ...
pvallero's user avatar
3 votes
1 answer
272 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 ...
A Y B's user avatar
  • 31
0 votes
1 answer
70 views

making query more efficient when there is a millions of row

My table consists of the following columns: job_id, event_type, timestamp, client_type, role_type, Every job_id has record of which event_type (example login, edit, delete) , what timestamp, which ...
orrie881's user avatar
2 votes
3 answers
457 views

How to query most recent pair of each unique data-pair?

There's a table wherein there currency exchange rates are. fx_rates --- id buy_curr_code sell_curr_code rate inserted_at A table gets updated with unknown frequency: sometimes daily, sometimes ...
Camila326's user avatar
0 votes
0 answers
133 views

Optimising a recursive SQL query that processes several million records in BQ

I need help optimizing a recursive SQL query in BQ. I have hierarchical data stored in a table as parent-child relationships, i.e will be stored as parent_item_id child_item_id 1 1 1 2 1 3 3 4 ...
Roman Dryndik's user avatar
0 votes
1 answer
80 views

Is it slower to pass a composite type representing a table to a function then the individual arguments?

Let's say you want to create a function CREATE FUNCTION f(a,b,c,x,y,z) FROM t; Is it any slower to restructure the function such that this works, CREATE FUNCTION f(t) FROM t;
Evan Carroll's user avatar
  • 62.1k
0 votes
1 answer
868 views

Postgres return multiple columns from subquery

I have found older posts but I'm curious if there is any better solution for my case. I have working code but it is really slow. I tried to return two columns in subquery but I have an error (the ...
Drtil Tomáš's user avatar
-1 votes
1 answer
787 views

Most efficient way of search for only spaces strings

I'm consuming data from a view. The view is large (millions of rows). I need to know if a specific varchar column rows are composed completely of spaces (" ") or if it has words on it. Right ...
imatiasmb's user avatar
2 votes
0 answers
17 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 ...
DBA Greg14's user avatar
0 votes
0 answers
47 views

Optimize SQL Query

Im having trouble with the performance of this query, especificallly with the case statements. How would yo optimize it? SELECT a.t, a.c, a.id, a.id_r, a.s, a.n, a.n_s, a.c, a.co, a....
KodaCode's user avatar
0 votes
3 answers
115 views

Poorly Performing Query with Massive Misestimates

I have been looking at this statement for the last day but I've been unable to work out the best way to improve it. So, I thought I would reach out to the wider community for some advise. SELECT [FLD6]...
Tom's user avatar
  • 191
0 votes
0 answers
169 views

Postgres/Aurora query response time unexplained

I have AWS Postgres Aurora RDS version 11 running with db.r6g.12xlarge instances in a 3-node cluster with 1 writer, 2 readers. It should be noted though that this also occurs on our on-premises Ubuntu/...
Diane's user avatar
  • 141
0 votes
0 answers
202 views

Why is the query time of my PostgreSQL DB so much slower than in my MySQL DB?

I've been testing the performance of my PGSQL DB and MySQL DB with around a million entries and I've come to the conclusion that MySQL seems to execute my queries much faster, but I feel it should be ...
Romek's user avatar
  • 109
-1 votes
1 answer
1k views

conversion failed when converting date and/or time from character string only on one of the server

I have a sql query as follows: select * from Txn_Master where Txn_Type = 'TPD' and Txn_Date = Getdate() order by Txn_ID DESC But in this sql-server query when i enter date manually like '01/06/2023'...
Prince Arya's user avatar
1 vote
0 answers
44 views

Best performance for cleaning table

I have two tables with millions of rows in which an average of 300 or more rows are added every day between the two tables. Many of these rows are rows that do not provide any information and I need ...
Marcos's user avatar
  • 111
1 vote
1 answer
266 views

Parallel index-only scan with two sub-queries is slow

I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2. ...
Cowabunga's user avatar
  • 135
1 vote
0 answers
196 views

Postgres sorted join with millions of rows, low cardinality, slow query - time to partition tables?

I have an API that is used to query for data about programs and other forms of content. We have a few tables that I need to join on to fullfil an ad-hoc query, and I am running into trouble speeding ...
flanders9er's user avatar
0 votes
0 answers
34 views

Optimization of google query with multiple LEFT JOIN and OR operators!

I have a SQL query that runs twice daily and ends up spilling disk space, the query selects data from left joining three tables and use multiple OR operators to fetch results and then insert all ...
Greg 's user avatar
0 votes
0 answers
31 views

PostgreSQL request taking a lot of time to sum 30 columns

I've an interesting case, I've been able to solve it but in a strange way. Here's my request : WITH geom_cte as (SELECT geom as geom) SELECT SUM(number_a * percentage) AS number_a, ... ...
Bil11's user avatar
  • 1
0 votes
0 answers
28 views

Improve performance of postgis query with distinct

Hej, in my hobby project "luft.jetzt" I want to show the user the latest data from air monitoring stations around him. Due to my poor database skills, I am unable to write a performant ...
Malte's user avatar
  • 1
0 votes
0 answers
246 views

tuning big partitioned table to make it faster

I have a partitioned table, call it Tabel1, that is based on partition by LIST on ID_X column. This ID_X column is also indexed and it is part of a composite primary key with ID_Y column. I do this ...
padjee's user avatar
  • 167
1 vote
0 answers
29 views

Multipile queries vs a long query with joins in my situation

I am currently working on a project where, each user can possess multipile portfolios. Each portfolio contains products, each product contains assets, which can be associated to different kinds that ...
orangetiger's user avatar
0 votes
1 answer
128 views

Will update statistics fix wait type IO_COMPLETION issue in Microsoft SQL Server?

I am using Microsoft SQL Server 2017 and recently we have come up with a situation where we see there is Wait Type IO_COMPLETION is contributing 60%. After performing update statistics this issue got ...
Vikrant More's user avatar
2 votes
1 answer
123 views

LEFT JOIN not using index when `column1` < `value` AND `column2` > value

I'm trying to LEFT JOIN a table to another table where a column value is between two column values in the second table, and it is not using an index when doing this seemingly simple query. It has ...
Marshall C's user avatar
0 votes
1 answer
68 views

Best approach to search for a word in a corpus of pdf and docx files

I am new to the community, so feel free to correct any clumsy mistakes. I have a large set of pdf and docx files ~10TB. I want to perform searches for specific words that will yield the document, the ...
elmo_N's user avatar
  • 1
0 votes
1 answer
250 views

Optimizing 2x 1.8GB tables takes 50 minutes

I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 ...
adrianTNT's user avatar
  • 195

1 2 3
4
5
60