Questions tagged [optimization]

In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

Filter by
Sorted by
Tagged with
2
votes
2answers
1k views

How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)?

I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?) SELECT cars.*, users.*, makers.*, places.* ...
12
votes
1answer
138 views

Сardinality estimation of partially covering range predicates

At the moment I'm trying to figure out how SQL Server evaluates the cardinality of range predicates that partially cover the histogram step. On the Internet, at cardinality-estimation-for-and-for-...
0
votes
2answers
41 views

Why is Primary Key not present at the end of Unique Key in MySQL?

I have a table with following definition. CREATE TABLE `test` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, `e` int(11) DEFAULT NULL, ...
0
votes
1answer
47 views

MariaDB Optimization - insert and select

I have 2-3 php files. I am sending sms to users by sending parameter in these php files. I'm checking the 3rd and 4th table before sending sms so select. If there is an id record, I do not insert. If ...
-1
votes
1answer
51 views

Simple “SELECT” query taking too long

I'm trying to run a SELECT query on our PostgreSQL database using pgAdmin 3. It looks like this: SELECT tstamp AS "time" dvalue AS "Weight" FROM tablename WHERE tstamp BETWEEN 'time1' ...
-1
votes
0answers
14 views

PostgreSQL index scan is slow [duplicate]

I'm trying to run a SELECT query on our PostgreSQL database using pgAdmin 3. It looks like this: SELECT tstamp AS "time" dvalue AS "Weight" FROM tablename WHERE tstamp BETWEEN 'time1' ...
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 ...
0
votes
2answers
24 views

DML queries with different performance on similar servers

I am measuring the performance between two servers that serve a database via MySQL 5.6. The DB1 server is a dedicated database server and the DB2 server shares resources with other services such as ...
2
votes
1answer
58 views

Range Extraction Algorithm for Muti-Part aka Composite Indexes in MySQL

I went through Range Extraction Documentation for MySQL Indexes. They have explained for single column indices. What is the procedure to reduce complex multi column (at least two) criteria? What I am ...
0
votes
1answer
50 views

optimize query — remove nested selects

How can I rewrite the query to increase performance? Here is the query: SELECT flight.*, DAYOFWEEK(flight.flight_time) AS day_of_week, IF(SUM(...
2
votes
1answer
357 views

Best way to move CTE into query so planner can better optimize and I can add indexes speed it up? [on hold]

I'm trying to incorporate the CTE into my final query. I can do it, but it slows it down a lot because I have to do the same sub query for CTE d over and over again (I think): with d AS( SELECT ...
0
votes
1answer
368 views

How to reduce redundant time-series data in MySQL into a function?

i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this: How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but ...
4
votes
1answer
110 views

Constant Scan joining

In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me. Setup is simple ...
8
votes
2answers
318 views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
4
votes
2answers
116 views

Ms sql MERGE INTO locks whole table for updates

I have a table for statistic values, it holds millions of records, which is defined like this: CREATE TABLE [dbo].[Statistic] ( [Id] [INT] IDENTITY(1, 1) NOT NULL , [EntityId] [INT] NULL , [...
0
votes
0answers
33 views

In a trigger, does assigning a column to the “old” value incur a cost? (Oracle) [duplicate]

For the sake of readability, in one of my triggers I have :new.COLUMNNAME := old.COLUMNNAME; alongside the sets for the rest of the columns. Does this statement get removed before runtime? (That ...
23
votes
1answer
2k views

Why doesn't this query use an index spool?

I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap: CREATE TABLE ...
0
votes
0answers
23 views

merge statement taking 30mins for 1777820 rows? how can i optimize it

merge into apl_scp_direct_dispatch t Using (select siteid, item, bucketid,band_qty from i2_scp_inventory_bands@DB_DMSAPIL_LINK where band_type = 'DEPBAND' and band_level = 2 ) s ON ( s....
0
votes
0answers
32 views

Optimizing range join (x between a and b) in MariaDB

My query has a join using a BETWEEN clause and it causes a full table scan to occur if I understand the EXPLAIN output correctly. Query EXPLAIN SELECT g.id, g.name, c.id AS child_id, c.date ...
12
votes
2answers
27k views

Why is this query not using my nonclustered index, and how can I make it?

As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default. This query runs in about 2.5 seconds: SELECT TOP 1000 * FROM [...
1
vote
1answer
18 views

PostgreSQL planner over estimates and self join query large table optimization

PostgreSQL 11.5 4 core, 32gigs 250gig SSD Not using default configuration, tuned to fit system memory and cores. Table+index size 14Gigs I'm working on a query that self joins to a table via a ...
0
votes
0answers
40 views

add index and change int data type to bigint the fastest way

How do I do this without downtime, or at least minimal downtime (less than 3 hours)? Is there any way to tweak my.cnf to at least make this process of indexing and change data type faster? Below is ...
3
votes
2answers
1k views

Would a cron job to automatically optimize tables on a monthly basis be ideal?

I'm wondering if running the OPTIMIZE command via cronjob would be an intelligent move? Sometimes I forget to run optimize for months at a time. My database is frequently using select, insert, update....
1
vote
2answers
1k views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
0
votes
1answer
43 views

Query runs in serial and parallel mode in two identical databases on the same instance [duplicate]

I have two databases in the same SQL Server instance. One is a test database with a smaller number of records and total size of 500GB. Other one is production database with a size of 2.2 TB. Both ...
0
votes
0answers
28 views

Advantages and disadvantages of JSONB columns

I love using json columns in PostgreSQL, but I'm wondering if there are scenarios where jsonb would make more sense. For example, we store a "seen by" state in our messages table and need to use ...
4
votes
2answers
637 views

Column Cardinality and Total Number of Rows Are Equal

I have a table with 128'914'635 number of rows. Some columns cardinality (i.e. address, lastname, firstname) is equal to the total number of rows in the table (128'914'635). The queries are really ...
0
votes
1answer
26 views

Mysql Query Optimization [Match Against] [closed]

I have tried all the possible option to optimize the query, but no luck. Query 1: SELECT *, ( 3959 * acos ( cos( radians(51.5073509) ) * cos( radians(...
28
votes
2answers
35k views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
32
votes
5answers
114k views

Logical operators OR AND in condition and order of conditions in WHERE

Let's examine these two statements: IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ... If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will ...
0
votes
2answers
100 views

How does MySQL store data, can I detach a mysql Database?

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
0
votes
1answer
42 views

How to find all participants of a chat thread?

In my database I have the following tables: user, post, post_participant, and post_reply, largely as inspiration from this answer. One thing I'm having trouble with figuring out though is how to find ...
2
votes
0answers
17 views

Querying a High Cardinality Field

I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
0
votes
1answer
4k views

Check (health) status of MySQL schema table

I have a large MySQL database which has around 1,500 tables and the file of the database is around 30GB. I would like to suggest me some ways to monitor the health of my database in overall and how i ...
10
votes
1answer
10k views

Type conversion in expression may affect “CardinalityEstimate” in query plan choice?

I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data. We constraint the events on ...
4
votes
1answer
327 views

Bad row estimate following Compute Scalar operator in plan

I'm struggling to understand where a row estimate is coming from in an execution plan. Paste the plan link declare @BatchKey INT = 1, @ParentBatchKey INT = 1, @QuoteRef varchar(50) = 'Q00018249', @...
3
votes
1answer
353 views

Type conversion in expression may affect “CardinalityEstimate” - on a computed column?

I had a look at this question: Type conversion in expression may affect “CardinalityEstimate” in query plan choice? But that was related to collation and not with computed columns. I have the ...
0
votes
1answer
20 views

Mysql performance -optimization(insert,select,proc) [closed]

Summary How can I receive, process, and insert 1000 rows every 5 seconds? Details I use the database extensively. We have vehicle tracking software. According to this software, a tool adds ...
0
votes
1answer
25 views

postgresql different configuration for different tablespace

I'm configuring new server with both SSD/HDD disk with ubuntu 18.04. I've installed system in SSD and created two different tablespaces one in ssd and one in hdd. I would like for configuration ...
2
votes
2answers
3k views

Query processor could not produce a query plan because of the hints defined in this query

There is a similar question, but it is not the same: The query processor could not produce a query plan I have the following query, and the following filtered index, and I cannot see any reason why ...
4
votes
1answer
459 views

Disk usage of integer column vs boolean column in Postgres

I saw this question Bit vs. Boolean columns. I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 ...
1
vote
2answers
68 views

How to Speed Up my SQL Query?

I have a query that is taking much longer than I expect it should, and I can not figure out how to speed it up. For a little bit of background on the table structure, these are audited records. There'...
2
votes
1answer
40 views

How can performance of queries containing keywords like IN, EXISTS, etc. be optimized?

I was interested in some of the general ways one can use to optimize the performance of queries that use keywords like IN and EXISTS (relying on the state of the entire database). For example, ...
1
vote
1answer
8k views

Why did a query change its execution plan and how to anticipate the change?

We have a job using a dynamic query in production for years now and its execution took 5-6 seconds up until our last code deploy. Now the same query takes days to finish. It's a relatively simple ...
0
votes
1answer
40 views

Optimize slow COUNT in MySQL subquery

I have the following query that takes minutes to complete: SELECT DISTINCT Designation, Model, ( SELECT COUNT( Equipment.EquipmentID ) FROM Equipment INNER JOIN EquipmentDesignation_Vw ...
3
votes
3answers
741 views

Optimise Large Record Deletion Procedure

I'm creating a database that mirrors our production database, but is lighter and anonymised - for local development purposes. Ensuring we've enough data to operate on as an engineering team, I'm ...
110
votes
6answers
113k views

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
1
vote
1answer
21 views

PostgreSQL not using the index on queries that return large number of records

I have a table called my_table which contains about 30M records. The table has an index set on column_a. However, I'm noticing that when number of returned queries are high, the index is not used. ...
0
votes
1answer
19 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
1
vote
1answer
121 views

Query performance of index scans slower than parallel seq scan on Postgres

I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora. Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...