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
0
votes
1 answer
Ideal Postgres Index For Json Data With Integer Timestamp
I have millions of records in this table using Amazon Aurora Postgres 10.7:
create table "somedb"."sometable"
(
id varchar(4096) not null constraint "sometable_pkey" primary key,
tag varchar(255) not null,
json jsonb not null
);
Example…
jn1kk
- 5,012
- 2
- 45
- 72
0
votes
3 answers
SQL CAST causes Arithmetic Overflow Error even when row is NOT in result set
This one has us perplexed ...
We have a query that uses CAST to convert a float to a decimal, this query joins a number of tables to find the rows to return. One of the rows in one of the tables contains a value that when CAST to a decimal causes…
Jack
- 3,444
- 5
- 34
- 50
0
votes
0 answers
Query plan on UDF keeps changing
Got an interesting one that I've struggled to resolve for several years now and is starting to become a bigger issue. We have a UDF that is used thoroughly throughout our system and retrieves a single value back from a journal table. It usually runs…
Sami.C
- 561
- 1
- 11
- 24
0
votes
0 answers
Why would the same SQL Server physical operation have a different estimated costs?
I'm looking into optimizing some queries in SQL Server 2016, and I ran into this situation which I couldn't understand.
This particular query is trying to get the name of two sets of Objects in the dbo.Objects table. To do this, the Query Plan shows…
M. McCrary
- 35
- 3
0
votes
2 answers
mongo sort before match in aggregation
Given a collection of a few million documents that look like:
{
organization: ObjectId("6a55b2f1aae2fe0ddd525828"),
updated_on: 2019-04-18 14:08:48.781Z
}
and 2 indices, on both keys {organization: 1} and {updated_on: 1}
The following query…
Dmitry Fink
- 1,032
- 1
- 13
- 31
0
votes
1 answer
Why does PostgreSQL sorts on a boolean WHERE condition?
I am testing some queries over a bunch of materialized views. All of them have the same structure, like this one:
EXPLAIN ANALYZE SELECT mr.foo, ..., CAST(SUM(mr.bar) AS INTEGER) AS stuff
FROM foo.bar mr
WHERE
mr.a = 'TRUE' AND
…
Settembre Nero
- 141
- 1
- 8
0
votes
0 answers
Redshift Query is spending considerably more time in first run than subsequent run
We have a queries(likely complex and big) generated by our BI application.
When I am running the query for the first time it takes around 8 to 9 mins to execute but
when I am executing it second time it is taking less time(15 secs).
I am not sure…
mt_leo
- 67
- 1
- 12
0
votes
2 answers
Query Plan Recompiled suddenly and degrades performance
Scenario: We have a simple select query
Declare P@
SELECT TOP(1) USERID
FROM table
WHERE non_clusteredindex_column = (@P) ORDER BY PK_column DESC
It usually executes with in 0.12sec since 1 year. But Yesterday suddenly exactly after mid night it…
Damodara Lanka
- 51
- 2
- 9
0
votes
1 answer
Oracle sql query plan does not uses indices with or statement and a subquery
I have a query with 'or' statement and 'inner select' statement. When I check query plan it uses full table access and I don't know why this happens.
I created this test case to show my problem:
CREATE TABLE PERSON (
ID …
Alperen Üretmen
- 307
- 1
- 13
0
votes
3 answers
How to Index SQL with multiple AND conditions nested inside OR
I want to speed up the following sql (cost is 19685.75). Can I index this sql which have multiple complex nested AND conditions combining with OR in the WHERE statement?
SELECT DISTINCT
ON ("crawler_url"."url") U0."id"
FROM…
Ben Chan
- 63
- 1
- 8
0
votes
0 answers
Improve performance of Temp table insert (with lots of columns)
I have to create a temp table #Trades from a select query. The select query returns 77,987 rows and have about 175 columns (mix of varchar, numeric, small int and int).
I am current using the following query to create the temp table:
SELECT Col1,…
developer
- 1,401
- 4
- 28
- 73
0
votes
0 answers
Mysql Query Plan different in PHP compared to Query Plan generated via Navicat
I am quite confused at the time of posting this.
I do not understand why the query plan for the query is different when I fire the EXPLAIN in php vs firing the EXPLAIN for the EXACT same query in navicat.
Query:
SELECT
MAX(`SaleID`) AS…
Jason D'Silva
- 1
- 3
0
votes
0 answers
Postgres query planner tuning
We have a db (9.6) that contains measurement data. The relevant query regards 3 tables:
aufnehmer (i.e. sensor), 5e+2 entries,
zeitpunkt (i.e. point in time), 4e+6 entries
wert (i.e. value), 6e+8 entries
aufnehmer : zeitpunkt = m : n with wert as…
Matthias
- 263
- 4
- 11
0
votes
1 answer
Cache greenplum query plan globally?
I'd like to save planner cost using plan cache, since OCRA/Legacy optimizer will take dozens of millionseconds.
I think greenplum cache query plan in session level, when session end or other session could not share the analyzed plan. Even more, we…
Lei Chi
- 216
- 1
- 14
0
votes
1 answer
MySQL index usage on join
I know there are several questions similar to this one, but those I've found do not relate directly to my problem.
Some initial context: I have a facts table, called ft_booking, with around 10MM records. I have a dimension called dm_date, with…
Lucas Lima
- 832
- 11
- 23