Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
1,964
questions
145
votes
4
answers
161k
views
Optimizing queries on a range of timestamps (two columns)
I use PostgreSQL 9.1 on Ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
- 8,501
129
votes
7
answers
150k
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,393
92
votes
4
answers
83k
views
Are views harmful for performance in PostgreSQL?
The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6):
The danger with using views is filtering a query against a view,
expecting to read a very ...
- 1,399
69
votes
3
answers
106k
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 ...
- 1,023
66
votes
3
answers
92k
views
When to use views in MySQL?
When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?
One reason that I would prefer to use views is that the database schema ...
- 3,132
47
votes
2
answers
90k
views
How to partition existing table in postgres?
I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to ...
- 1,163
40
votes
3
answers
5k
views
Unexpected scans during delete operation using WHERE IN
I've got a query like the following:
DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)
...
- 1,465
40
votes
1
answer
18k
views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
- 172k
39
votes
5
answers
157k
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 ...
- 6,702
34
votes
3
answers
69k
views
How to speed up select distinct?
I have a simple select distinct on some time series data:
SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';
And it takes ...
- 617
32
votes
1
answer
39k
views
Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?
I am running a query that is giving the warning about a memory Excessive Grant.
There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the ...
- 15.8k
32
votes
1
answer
31k
views
EXPLAIN ANALYZE shows no details for queries inside a plpgsql function
I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside:
create function f1()
returns integer as
$$
declare
event tablename%ROWTYPE;
....
....
begin
FOR event IN
...
- 331
30
votes
5
answers
157k
views
How to optimize very slow SELECT with LEFT JOINs over big tables
I was googling, self-educating & looking for solution for hours but with no luck. I found a few similar questions here but not this case.
My tables:
persons (~10M rows)
attributes (location, age,...
- 411
29
votes
5
answers
7k
views
Why does my SELECT DISTINCT TOP N query scan the entire table?
I've run into a few SELECT DISTINCT TOP N queries which appear to be poorly optimized by the SQL Server query optimizer. Let's start by considering a trivial example: a million row table with two ...
- 32k
29
votes
4
answers
741
views
Why does the estimated cost of (the same) 1000 seeks on a unique index differ in these plans?
In the queries below both execution plans are estimated to perform 1,000 seeks on a unique index.
The seeks are driven by an ordered scan on the same source table so seemingly should end up seeking ...
- 83.3k
26
votes
3
answers
1k
views
SQL Server splits A <> B into A < B OR A > B, yielding strange results if B is non-deterministic
We have encountered an interesting issue with SQL Server. Consider the following repro example:
CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY);
INSERT INTO #test (s_guid) VALUES ('7E28EFF8-...
- 3,145
26
votes
1
answer
26k
views
Index not used with = ANY() but used with IN
Table t has two indexes:
create table t (a int, b int);
create type int_pair as (a int, b int);
create index t_row_idx on t (((a,b)::int_pair));
create index t_a_b_idx on t (a,b);
insert into t (a,b)
...
- 1,135
26
votes
2
answers
37k
views
Slow index scans in large table
Update 2020-08-04:
Since this answer is apparently still being viewed regularly I wanted to provide an update on the situation. We're currently using PG 11 with table partitioning on timestamp and are ...
- 429
25
votes
2
answers
2k
views
Cardinality Estimate for LIKE operator (Local Variables)
I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and ...
- 652
25
votes
1
answer
4k
views
Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?
I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
- 55.6k
24
votes
2
answers
3k
views
Is it possible to give the optimizer more or all time it needs?
Given that the optimizer cannot take all the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off.
I was ...
- 582
24
votes
1
answer
3k
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 ...
- 32k
23
votes
2
answers
2k
views
Queries without Good Enough Plan Found
I have a SQL Server 2012 database. I noticed value of Reason for early termination of statement optimization for some queries and all gave Good Enough Plan Found. Now my questions are:
What are all ...
- 899
23
votes
2
answers
3k
views
Why are the plans different if the queries are logically alike?
I wrote two functions to answer Day 3's first homework question from Seven Databases in Seven Weeks.
Create a stored procedure where you can input a movie title or actor's name you like, and it ...
- 2,318
23
votes
3
answers
58k
views
MySQL InnoDB page_cleaner settings might not be optimal
Seeing this note in mysqld.log:
[Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)
There seems to be ...
- 342
23
votes
1
answer
832
views
Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY
SQL Server 2019 CTP3.1 introduced an optimization to address last-page insert contention. This takes the form of an index option called OPTIMIZE_FOR_SEQUENTIAL_KEY.
One imagines this could be an ...
- 24.7k
22
votes
2
answers
35k
views
Does an empty column value occupy same storage space as a filled column value?
I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not ...
- 353
21
votes
2
answers
41k
views
Postgres JOIN conditions vs WHERE conditions
Postgres newbie here.
I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See ...
- 407
20
votes
3
answers
13k
views
Does "WHERE 1=1" usually have an impact on query performance?
I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).
...
- 333
20
votes
3
answers
18k
views
Trace Flag 4199 - Enable globally?
This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you ...
- 681
20
votes
3
answers
52k
views
SQL Server - Select most recent record from each group when performance is critical
I run a SQL Server 2016 database where I have the following table with 100+ millions rows:
StationId | ParameterId | DateTime | Value
1 | 2 | 2020-02-04 15:00:000 | 5....
19
votes
7
answers
5k
views
Optimizing numeric range (interval) searches in SQL Server
This question is similar to Optimizing IP Range Search? but that one is restricted to SQL Server 2000.
Suppose I have 10 million ranges provisionally stored in a table structured and populated as ...
- 83.3k
19
votes
4
answers
19k
views
Good, Bad or Indifferent: WHERE 1=1
Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up ...
- 1,215
19
votes
3
answers
891
views
Forcing Flow Distinct
I have a table like this:
CREATE TABLE Updates
(
UpdateId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ObjectId INT NOT NULL
)
Essentially tracking updates to objects with an increasing ID.
The ...
- 293
19
votes
4
answers
5k
views
SQL Server cardinality hint
Is there a way how to 'inject' a cardinality estimation to a SQL Server optimizer (any version)?
i.e. something similar to Oracle's cardinality hint.
My motivation is driven by the article, How ...
- 253
19
votes
3
answers
17k
views
Splitting SQL query with many joins into smaller ones helps?
We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on ...
- 453
18
votes
3
answers
7k
views
Colleague says to never use an OR statement in SQL, is this true?
My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't ...
- 321
18
votes
2
answers
98k
views
"Copying to tmp table" extremely slow
This is my example of query:
SELECT
nickname,
CASE class_id
WHEN 1 THEN 'Druid'
WHEN 2 THEN 'Necromancer'
WHEN 3 THEN 'Mage'
WHEN 4 THEN 'Priest'
WHEN 5 THEN '...
- 181
18
votes
2
answers
41k
views
Do covering indexes in PostgreSQL help JOIN columns?
I have a whole lot of tables that look vaguely like this:
CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES ...
- 669
18
votes
2
answers
6k
views
Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan.
Populating the table:
CREATE TABLE dbo.Test
...
- 2,619
18
votes
2
answers
75k
views
MySQL performance problem using indexed datetime column
I tried to solve the following problem for about one hour now and still didn't get any further with it.
Okay, I have a table (MyISAM):
+---------+-------------+------+-----+-------------------+-----...
- 283
17
votes
1
answer
877
views
When can SARGable predicates be pushed into a CTE or derived table?
Sandbag
While working on Top Quality Blog Posts®, I came across some optimizer behavior I found really infuriating interesting. I don't immediately have an explanation, at least not one I'm happy ...
- 38.8k
17
votes
1
answer
6k
views
Why is Clustered Index Scan Number of Executions so high?
I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time.
The only difference between ...
- 271
17
votes
1
answer
546
views
Constant Scan applying
This question is a continuation of optimizer behavior exploration in regards to VALUES construct started here and here. I want to ask about VALUES and APPLY this time.
Using CROSS APPLY to alias an ...
- 2,354
16
votes
4
answers
2k
views
Is a join optimized to a where clause at runtime?
When I write a query like this...
select *
from table1 t1
join table2 t2
on t1.id = t2.id
Does the SQL optimizer, not sure if that is the correct term, translate that to...
select *
from table1 t1, ...
- 263
16
votes
3
answers
36k
views
Why does MySQL ignore the index even on force for this order by?
I run an EXPLAIN:
mysql> explain select last_name from employees order by last_name;
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+
| ...
- 1,003
16
votes
4
answers
9k
views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
- 1,677
16
votes
1
answer
19k
views
Why is this sqlite query much slower when I index the columns?
I have a sqlite database with two tables, each with 50,000 rows in, containing names of (fake) people. I've constructed a simple query to find out how many names there are (given name, middle initial, ...
16
votes
3
answers
2k
views
Oracle is not using a unique index for a long key
I have a table with 250K rows in my test database. (There are a few hundred millions in production, we can observe the same issue there.) The table has an nvarchar2(50) string identifier, not null, ...
- 447
16
votes
2
answers
19k
views
Fastest validation query in PostgreSQL
What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource?
I thought that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we ...
- 737