A query planner is a subsystem of a SQL database system, that creates the execution plan for a SQL query.
A query planner is a subsystem of a SQL database system, that creates the execution plan for a SQL query.
Questions tagged [query-planner]
135 questions
2
votes
1 answer
Query plan for MDX query?
I have been slowly trying to learn MDX coming from a background of using SQL. In DBMSs such as PostgreSQL, one can get a query plan for their query e.g. using EXPLAIN. Is this possible in any version of MDX and if so, how? I have not managed to come…
baceda9220
- 41
- 1
- 4
2
votes
0 answers
Why type casting of a constant breaks query performance
Test table and indexes (PostgreSQL 12.1):
CREATE TABLE t (dt timestamp with time zone);
CREATE INDEX ind ON t USING btree (dt);
INSERT
INTO t(dt)
SELECT
(
timestamp '2020-01-01 00:00:00' +
random() * (
timestamp…
Dima Pavlov
- 115
- 9
2
votes
1 answer
Postgres using partial index on enum type for equality condition but not for inequality
Using PostgreSQL 9.6.9, I have:
a custom SQL enum type with 3 levels used in a column of some
table xy.
a composite partial index on the highest level (level3) of this enum on table xy.
a lot of dummy data (more than 200000 rows)
And using EXPLAIN…
Christophe Roussy
- 16,299
- 4
- 85
- 85
2
votes
1 answer
Identically formatted queries with different parameters have radically different execution times in MySQL 8.0
EDIT: I have now solved this problem. Thank you Rick James for your help! Also: it wasn't part of the solution, but you were 100% right about prefix indexes. Performance actually went up slightly when I took them out.
.
.
.
I am having a strange…
convensive
- 53
- 5
2
votes
1 answer
How does now() get evaluated when passed into a function as a parameter
I have a table that is range partitioned on timestamp with timezone field. I was pretty surprised to find that the following where condition caused the planner to query every single 'child' table in the partition:
WHERE reading_time > (now() -…
Debaser
- 427
- 1
- 5
- 17
2
votes
1 answer
Query inlining vs. flattening
I've been digging the Internet over and over and couldn't find any reasonable answer. What's the difference between inlining and flattening in SQL query? I do actually use both interchangeably, eventually they lead to the same result - a big single…
Borys
- 2,676
- 2
- 24
- 37
2
votes
1 answer
How to improve the text column performance in below mentioned SQL query
There is an SQL union all query with 3 union all queries. The query execution time changed significantly after I had added a text column CAST(c.getQuestionId AS VARCHAR(300)) in the query. The database being used is SQL SERVER 2014. Performance…
user1254579
- 3,901
- 21
- 65
- 104
2
votes
1 answer
Postgres won't use index depending on specific value of id in where clause
I've been tinkering/reading for a while, but can't find any optimizations that work here... I've indexed the relevant ids in the joins, I tried a manual vacuum, and I also tried clustering on an index so that maybe the query optimizer wouldn't…
dcc310
- 1,038
- 1
- 9
- 13
2
votes
1 answer
Oracle 12c Subquery Factoring Inline View now has bad plan?
Update 11/2
After some additional troubleshooting, my team was able to tie this Oracle bug directly to a parameter change that was made on the 12c database the night before the query stopped working. After experiencing some performance issues from…
user2858650
2
votes
0 answers
SQL Server stored procedure very slow with date variable
I am running a stored procedure in SQL Server, something like:
INSERT INTO #TempTable
SELECT CustomerID
FROM Customer
WHERE DateCreated > @TimeStamp
This is ignoring the index on the date column. I run the select query it is fine with a…
Charles Bryant
- 995
- 2
- 18
- 30
2
votes
0 answers
query plan cost confusion
the following query with different limit count, make out different query plan and very different scan rows :
mysql> explain SELECT * FROM `funds_cash_apply` WHERE 1 and `id` > '0' and `channel` in ('70','81') and `state` = '1' and `state_pay` = '0'…
Chinaxing
- 8,054
- 4
- 28
- 36
2
votes
1 answer
Why does Postgresql not use Index Only Scan on my composite index in this case?
My table has integer columns "a", "b". There are only few (<30) different values for "a", but for each "a", huge number of different "b" exists (>10**7). To speed things up, I created composite index (a,b). I observe that
select count(*) from tab…
Tomas Kulich
- 14,388
- 4
- 30
- 35
2
votes
1 answer
JOIN with OR - Query Planner Choosing Nested Loops
I am wondering why does MS SQL Server Query Planner create nested loops rather than choosing a Union for a JOIN with OR Conditions.
Note: From searching on SO, does not seem to be MSSQL specific
E.g.
SELECT * FROM TableA a
JOIN TableB b
ON a.One =…
Michal Ciechan
- 13,492
- 11
- 76
- 118
1
vote
1 answer
Xmin queries in Postgres not using parallel sequential scans
I'm building an application which uses two different SQL queries. I'm running EXPLAIN ANALYZE on these two queries on the same Postgres table to understand their performance.
Query 1: Xmin query
explain analyze
select * from table where…
Dev K
- 41
- 2
1
vote
1 answer
Query with identical query plan but different limits have vastly different execution times
I have the below table (simplified) where there are approximately 2 million records in the table, with tickets.spam = FALSE for the majority of records, and only 31 records with tickets.spam = TRUE.
CREATE TABLE tickets (
id INT PRIMARY KEY,
…
thebenman
- 1,621
- 14
- 35