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
3
votes
2 answers
MongoDB optimal Index | Query planner behavior
I have a MongoDB Sharded Cluster hosting 250+ million documents.
Document structure is as follows:
{
"app_id": "whatever",
"created": ISODate("2018-05-06T12:13:45.000Z"),
"latest_transaction": ISODate("2019-03-06T11:11:40.000Z"),
…
SpiXel
- 4,338
- 1
- 29
- 45
3
votes
1 answer
Why is Oracle's query planner adding a filter predicate that replicates a constraint?
I have a simple Oracle query with a plan that doesn't make sense.
SELECT
u.custid AS "custid",
l.listid AS "listid"
FROM
users u
INNER JOIN lists l ON u.custid = l.custid
And here’s what the autotrace explain tells me it has for a…
Andy Lester
- 91,102
- 13
- 100
- 152
3
votes
1 answer
SQLite R*Tree index not used with DISTINCT
In SQLite 3.20.1, I have an R*Tree index (dog_bounds) and a temporary table (frisbees) created as follows:
-- Changes infrequently and has ~100k entries
CREATE VIRTUAL TABLE dog_bounds USING rtree (
dog_id,
min_x, max_x,
min_y,…
sjones
- 301
- 4
- 8
3
votes
2 answers
Posgres 10: "spoofing" now() in an immutable function. A safe idea?
My app reports rainfall and streamflow information to whitewater boaters. Postgres is my data store for the gauge readings that come in 15 minute intervals. Over time, these tables get pretty big and the availabity of range partitioning in Postgres…
Debaser
- 427
- 1
- 5
- 17
3
votes
1 answer
UPDATE with SELECT subquery runs extremely slowly on MySQL 5.7 (but was fine on 5.5)
Thank you all in advance. I have encountered an issue in upgrading my database from MySQL 5.5 to 5.7 that has me completely and totally confounded. The upgrade was not done using mysqldump or similar, but as a rebuild from several tab separated…
convensive
- 53
- 5
3
votes
1 answer
SQLite query using TEMP B-TREE FOR ORDER BY instead of index
I have a very simple table that includes 2 columns, message_id and server_timestamp, but when I look at the plan for this query:
sqlite> explain query plan select message_id, server_timestamp from messages group by message_id order by…
Lawrence
- 657
- 1
- 6
- 9
2
votes
0 answers
Why does varchar(max) column in concat function cause query plan to blow up
Context
Tables ranging from 1M - 200M records. All tables in question have zero indexes on them as they are staging/landing areas for our raw data sources. The purpose of having cast(NULL as varchar(max)) below was to handle 1 situation where our…
Adam
- 2,422
- 18
- 29
2
votes
1 answer
Where condition dramatically reduces performance unexpectedly
I am attempting to optimize a very large query that has a particularly bad join condition. I have reduced the larger query down to two tables to illustrate the negative behavior I am seeing.
CREATE TABLE #test1 (Id1 INT, Id2 NVARCHAR(256))
CREATE…
Fernando Rojo
- 33
- 4
2
votes
1 answer
Correct planner estimate of 1:1 join without FK or left join
In below example, I am joining two identical tables on two columns:
create table a (id int,txt text);
create table b (id int,txt text);
insert into a select *,* from generate_series(1,40000);
insert into b select *,* from…
Peter
- 932
- 5
- 19
2
votes
0 answers
How do I convince postgres to choose the MUCH more efficient of two nearly identical indexes (6 orders of magnitude more efficient)
I have some huge postgres tables that seem to be using the wrong index. In a big way. Like, in a 'if I remove one index, the query performance goes up by six orders of magnitude' way. (For those of you counting, that's ~1ms to 32 minutes.) We vacuum…
Adam Lang
- 49
- 4
2
votes
1 answer
Why plainning time is doubled when I call IMMUTABLE function with exatly same arguments second time?
Compare:
At first case I call same find_period with same arguments. Because the function is IMMUTABLE I suppose that plan for it will be reused (same function, same arguments, same plan), but seems it is not reused Why?
Source function:
CREATE OR…
Eugen Konkov
- 22,193
- 17
- 108
- 158
2
votes
2 answers
Why optimization is not applied/different index is used?
I have two pretty similar queries:
WITH USAGE AS NOT MATERIALIZED ( SELECT
ocd.*,
1 AS conf_suma
FROM(
SELECT o, o.agreement_id AS agreement_id
FROM "order_bt" o
WHERE o.sys_period @> sys_time()
AND …
Eugen Konkov
- 22,193
- 17
- 108
- 158
2
votes
1 answer
Why my query is slower when sorting in RAM vs disk in Postgres?
I use AWS RDS PG 12.5 (db.t3.xlarge / 4vCPUs / 16GB RAM / SSD storage).
I was trying to optimize a query by tuning work_mem parameter to avoid spilling data on the disk to sort the data.
As expected, when increasing the work_mem from 4MB to 100MB, a…
mich
- 347
- 3
- 10
2
votes
0 answers
PostgreSQL: Multicolumn index (jsonb, integer) used partially with @> and = conditions
Setup
A table with one jsonb column attributes and a non unique numeric ID campaignid:
CREATE TABLE coupons (
id integer NOT NULL,
created timestamp with time zone DEFAULT now( ) NOT NULL,
campaignid bigint NOT NULL,
attributes…
Alechko
- 1,406
- 1
- 13
- 27
2
votes
2 answers
Postgres uses Hash Join with Seq Scan when Inner Select Index Cond is faster
Postgres is using a much heavier Seq Scan on table tracking when an index is available. The first query was the original attempt, which uses a Seq Scan and therefore has a slow query. I attempted to force an Index Scan with an Inner Select, but…
Noah Sragow
- 21
- 1
- 3