What's the deal with Deno? We talk with a major contributor to find out. Listen now.

Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
66 views

How to speed up SQL Server 2014 Spatial functions

I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database. Note that I have simplified the names of the tables and columns. To illustrate I have ...
0
votes
0answers
16 views

PostgreSQL LWLock: lock_manager issue

In our application we need to insert a lot of historical data that will be consumed later by some mobile devices. These data is organized by a numeric incremental attribute that the device will use to ...
0
votes
2answers
71 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 ...
0
votes
1answer
117 views

Large dataset of 400+ million rows to compare

I am trying to solve this logic problem but my current MySQL schema is giving me a very poor performance with a large dataset. I have a table which contains almost half a billion telephone records. ...
4
votes
1answer
1k 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 gives me a count of the unique people that visited a given page ...
2
votes
1answer
702 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 ...
3
votes
1answer
52 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
1answer
76 views

Very slow JOIN + ORDER BY query only in dev environment

I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query. This query is taking 75 ...
0
votes
2answers
54 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
59 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 ...
0
votes
1answer
503 views

Query goes in Copying to tmp table stauts in MySQL and never returns

I am getting some strange behaviors for my MySQL query status. Here is my query code: SELECT a.id as art_id, b.album_artist_id, s.song_title,s.song_seo,a.artist_seo,a.artist_name, b.album_title, b....
1
vote
2answers
173 views

How to improve query performance?

I have a query that executes very often on our system and there is no option to change the query text. Are there any suggestions on what could be done? Any help would be appreciated. The query: ...
0
votes
1answer
107 views

Calculate the duration in an interval and outside of events that can last over several days

I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11 For an event on the same day no problem. But over several days I manage to ...
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 ...
1
vote
1answer
109 views

Neo4j import performance [closed]

I have a Neo4j 3.5.3 installation on my Ubuntu laptop (which is Intel i5, 4 GB RAM, SSD disk) and I'm trying to import a moderate-sized dataset from CSV files into the graph. Here is the full Cypher ...
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 ...
1
vote
1answer
120 views

SELECT operation with four joins results in bad performance

I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places. Resources Query:...
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
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. ...
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, ...
4
votes
1answer
397 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 ...
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 ...
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 | ...
0
votes
2answers
96 views

large number of tables and postgres query performace

I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 ...
3
votes
1answer
117 views

Subquery in FROM slower than two separate queries

I'm working on a system where users can buy "punch cards" to join events. A client buys a card from an organiser. The card is valid for specific event types and for a specified number of events. The ...
0
votes
0answers
57 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
2answers
2k views

Slow query log is not storing slow queries

Using 5.6.35 MySQL Community Server (GPL). I have setup the local MySQL instance as follows show variables like '%slow%query%log%' slow_query_log ON slow_query_log_file /usr/local/mysql/data/...
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 ...
2
votes
1answer
1k 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 ...
7
votes
1answer
1k views

Unbelievably slow and unusable Query Store

I'm begining with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It's just keep saying "Waiting" for hours and hours. Query on the ...
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....
1
vote
2answers
879 views

SQl query to create a report based on multiple relational tables

I have an e-commerce website and I'm in the process of creating a sales report. These reports need to be divided by year and month. Each sale made on the website will generate a relational link ...
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 ...
1
vote
2answers
98 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 ...
17
votes
4answers
38k views

Effect of an index on update statements where update column is not in an index

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute. While tuning my database to improve performance, I keep ...
0
votes
1answer
33 views

Imported database very slow on a simple query

A production database has been exported to a separate location for various reasons, however the dumped/imported database is performing much slower on some very specific queries. Here is 2 queries on ...
0
votes
5answers
79 views

Why does this mysql query take so long?

I have a MySQL server (ver 5.6) that is well equipped with hardware (Linux, in AWS, 32GB RAM, 56 CPUs), and according to top and iotop, it isn't even feeling warm; yet this query takes something like ...
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
455 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
2
votes
1answer
25 views

Mysql self Join query with Limit never terminates

I'm running an experiment with the query Select Distinct table_1.id1, table_2.id1 FROM image as table_1, image as table_2 WHERE table_1.id2 = table_2.id2 LIMIT K; When I run the query ...
1
vote
3answers
171 views

Performance issues after upgrading to MySQL 8

UPDATE (tl;dr;): I filed a bug report here: https://bugs.mysql.com/bug.php?id=99593 which has since been acknowledged and a workaround provided. See answer below for details. Certain queries seem to ...
0
votes
3answers
38 views

Wrong index been peeked

I have an index that filters 99% of the table i.e. ix_magic_composite (for that query arguments). When I add another or filter it chooses the wrong index, i.e. fTS even if I create an index that ...
0
votes
0answers
88 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
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 ...

1
2 3 4 5
47