Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
3
votes
1answer
47 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(...
0
votes
2answers
50 views

Postgres create Index

im running out with ideas for creating postgres index for a better performance. This example below is a selection of a big table with many entries for a diary to person relation. usually, i create an ...
0
votes
1answer
57 views

Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?

I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during ...
1
vote
1answer
26 views

Strategies for long term data growth optimization for a query with an index on a joined table

Given two simple, contrived example tables create schema test; create table test.exchange ( id serial primary key, created_at date not null ); create index on test.exchange(created_at); create ...
0
votes
1answer
32 views

order by slowing down query with multiple joins and limit/offset on larger result sets

I am having trouble with the following query taking quite a long time to process when results are large. The limit and offset can change as this is used with pagination. The range on ...
0
votes
1answer
39 views

Query optimizer triggering statistics update seems to be slowing down queries

We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around ...
1
vote
1answer
34 views

PostgreSQL - trying to speed up query responses from view that has join and group by

I have these two tables: precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates). Column | ...
1
vote
1answer
42 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. ...
0
votes
0answers
19 views

Group by with clause in postgresql taking lots of time

I am new to sql and trying to write a query in postgresql which is taking lot of time during execution (~8 mins), total number of data in the table is around 298650 rows. and i am using below stated ...
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
30 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 ...
0
votes
0answers
13 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
0answers
55 views

Postgres SQL CPU and RAM optimisation to improve decryption performance with pg_crypto

I am currently working on an application that requires column-based decryption for a few thousand of rows on a regular basis. The queries are decrypted using pgp_sym_decrypt, where several columns are ...
0
votes
1answer
45 views

Why is this postgres query ignoring the index in the subquery?

I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index: sophia=> explain analyze select count(distinct(source_doc)) from public....
-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 ...
0
votes
1answer
68 views

SQL CTE Expression with Cross Join - Can it be simplified?

I am looking to simplify the below SQL Query. Currently it's taking 44 mins to run. The main issue seems to be the WHERE [Versions] = (SELECT max([Versions]) from BPC_CTE) statement. What I'd like to ...
0
votes
2answers
64 views

Slow planning time on PostgreSQL 12.4

I have a database with Openstreetmap location data, and in particular there is a polygon table with around 500 million rows. I created indexes on all of my queries consisting of using gist(way) where ...
1
vote
2answers
95 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"."...
0
votes
1answer
23 views

Query with big IN clause

I'm having an performance issue with a query, the query is something like: Select * from requests where start_date > {start_date} and end_date < {end_date} and employee in {employee_id_list} ...
2
votes
1answer
29 views

How does mysql optimiser chooses a composite Index

I have a table with the following structure. -id (bigint|pk) -funnel_id (bigint|fk with funnels table) -affiliate_id (bigint|fk with affiliates table) -created_timestamp (bigint) Observations ...
2
votes
1answer
39 views

Read-only database: Normalize or not for best query performace

I have a pandas DataFrame that looks a bit like this: id name date col1 col2 total 0 123456748 EXAMPLENAME 2020-09-01 SOMEDATA MOREDATA 5.99 1 123456748 EXAMPLENAME ...
2
votes
1answer
44 views

What can cause delay in SELECT statement execution and high ASYNC_NETWORK_IO even with “disregard results after execution”

We are facing performance problems on our SQL Servers. 7 servers, all running the same application, all have the same problem. SELECT @@VERSION Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0....
0
votes
1answer
74 views

Query tables with 400 million records fast [closed]

Table: Customer Data Rows: 400 million Indexes: Non-clustered (as per queries) Issue: Index Scan happening with most queries, 400 million scan take too long to complete Need advise on how to address ...
0
votes
1answer
23 views

How to make MySQL query performant WHERE one column uses greater than and one column uses less than?

I'd like to make this query perfomant-- SELECT * FROM my_table WHERE foo_id = 8 AND enabled = true AND start_time < '2020-09-12T06:59:59.999Z' AND end_time > '2020-09-07T07:00:00.000Z' This is ...
2
votes
1answer
54 views

Should I duplicate columns between tables to speed-up aggregations like SUM? [closed]

I have two tables in PostgreSQL 10.12 database: waste_card (id (PK), user_id, manufacture_date, transfer_date, address_id, card_type, .. (and other card_specific_firelds) wastes (user_id, ...
2
votes
1answer
26 views

composit index and possible keys specific query

i have query like this select date_format(referencedate, '%m/%d/%Y') as PostingDate, reference_code, "" as DocNo, Account_Number, detail_desc as Description, case ...
0
votes
1answer
33 views

MongoDB not using wildcard nested array index

I have the following collection: { _id: 12345, quizzes: [ { _id: 111111, done: true } ] }, { _id: 78910, quizzes: [ { _id: 22222, done: ...
0
votes
0answers
5 views

Can you link tables based off data in a cell/field?

I have 4 tables that I want to link. The main table is Contributors and then I have 3 tables for the events, Golf, Breakfast, and Dinner. I have a field that has a value list with 3 selections in ...
2
votes
1answer
52 views

MariaDB wants to use bizarre and extremely inefficient query plan on Aria, but not on InnoDB (5 seconds vs 1+ hour query)

I'm having some troubles getting a query to preform as expected on my MariaDB 10.4.14 system. We recently migrated the database from InnoDB to Aria so we could store the INDEX separately on a fast PCI-...
7
votes
1answer
372 views

Improving performance when ordering by a column of a joined table

I have a parent table that contains a foreign key to a lookup table (simplified example): CREATE TABLE [dbo].[Parent] ( [Id] [uniqueidentifier] NOT NULL, [LookupId] [uniqueidentifier] NULL ) ...
-1
votes
1answer
32 views

MySQL Query Fetch Time increases when LEFT JOIN with big table [closed]

I am fetching the same amount of data with 2 different queries. However, one of them have a fetching time of ~x130 compared to the other. Being the only difference between the two queries a LEFT JOIN ...
0
votes
1answer
28 views

Get top 5 students for each class in many-to-many relationship [duplicate]

So I'm doing this in MySQL. I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their ...
3
votes
1answer
54 views

Row vs Columnar DB for queries with multiple OR statements

I understand columnar databases are great for speed queries where you don't use many fields to lookup against, but what if you only used OR statements? For instance, I want all the records that have (...
1
vote
1answer
36 views

How to prevent a column from repeating in the final result when using the LISTAGG function

I have a table like this: CUST_NUMBER | CUST_NAME | CUST_PROFESSION ------------------------------------------ 1 | Mike | Teacher 1 | Mike | Engineer 2 |...
3
votes
0answers
47 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 (...
4
votes
1answer
53 views

VERY slow lateral join on relatively small database

My database consists of apps and their reviews (schema below). I'm trying to answer the following question: Given a series of dates from the earliest reviews.review_date to the latest reviews....
0
votes
1answer
60 views

Optimization for branched WHERE

I have a query that executes against a table that can grow to millions of rows. The query comes out of a QA tool that we use that is outside the standard functionality of the DB (as far as what is ...
0
votes
1answer
111 views

Query tuning update with hash flow distinct operator

Need some help in understanding the slowness in one of the UPDATE statements like below:- UPDATE TOP (100) xyz SET xyz.flag = 1 OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc....
0
votes
1answer
63 views

Can OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY cause to create totally different execution plan and also cause query to run slow?

We have a SELECT query which is taking time (around 90 secs) to execute. It has OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY in it. When we remove OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY, it completes in 4-5 ...
0
votes
0answers
9 views

Sum data from one table and divide it by count of rows in joined table

I'm using postgresql. I need to sum data from one table (timeWorked from log table, summed by taskId) and then divide it by the amount of rows in the joined table (task_worker). Also join another ...
0
votes
1answer
32 views

Is there a better way of displaying 'Count' of records beside other columns in a select query?

I have a table with below structure : Test1(c_num number ,c_first_name varchar2(50), c_last_name varchar2(50)) 1)There is a normal index on c_num column. 2)The table has nearly 5 million records. I ...
0
votes
1answer
58 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 | ...
0
votes
0answers
84 views

Query performance become slower after upgrade to MySQL 8.0.20

I have found bug/issue after update the MySQL Server from 5.7 to 8.0.20. When I run a query with the user-define function in 5.7(same session), the duration for each query is constant and stable. But ...
0
votes
0answers
17 views

High-performance query of two tables with count from second table?

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 ...
1
vote
0answers
30 views

Unexpected result by MySQL Optimizer for identical queries?

Following query is working like expected and uses index Query takes 0,0481 sec SELECT geodb_locations.name, geodb_locations.name_url, COUNT(user.uid) AS useranzahl FROM user LEFT ...
0
votes
1answer
23 views

Query specific key fields from x-number of tables?

I'm building an insurance database and I need some help with best practice on fast quering data. Let's say I have three insurance types ... Car, Home and Travel. Common for all three types, I have the ...
2
votes
1answer
80 views

Query customers attributes

I need to create a system where the user creates dynamic filters based on our customer's attributes. There are more or less 30 possible filters and 30 millions of customers, but the number of customer ...
-1
votes
1answer
37 views

Results are varying with same query

Hi I stuck in strange issue with one of my query. Formula : (28800/3600-(28382.6/3600-(1920/3600+1860/3600))) = So, Input Equation: = (28800/3600-(28382.6/3600-(1920/3600+1860/3600))) = (28800/3600-(...
0
votes
1answer
48 views

Postgres sometimes uses sequential scan instead of an index only scan for data that doesn't exist

We are using Postgres 12 and We have a simple table like this: > \d live_event_segmentation Table "public.live_event_segmentation" Column | Type ...
-1
votes
1answer
35 views

Oracle Select Query based on a column value

I have two tables and wanted to conditionally query one of the tables. For example below are the two tables with few matching columns in two tables Table A(SNO, Number,Name...) Table B ( SNO, Number,...

1
2 3 4 5
47