Stack Overflow
Stack Overflow

Questions tagged [query-planner]

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.

135 questions
1
vote
2 answers

Slow query with many joins - expanding the magic pill?

I have a query that takes 20 minutes to run, even though I have an index for every column in the where clauses, and every column being joined: SELECT DISTINCT skt.VCDRAWING_REG_NO, skb.NDRAWING_ORG_NO, skb.NDRAWING_ORG_REV_NO, skb.CAPPLY_START_DATE,…
HerrimanCoder
  • 6,835
  • 24
  • 78
  • 158
1
vote
2 answers

why the query is executed 76 times slower when I put it into function?

When I put next query into function it goes 76times slower. The only difference at plan is: bitmap-index scan VS index scan Plan1: http://tatiyants.com/pev/#/plans/plan_1562919134481 Plan2:…
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
1
vote
2 answers

Query Plan: Is the order of JOINS important

I want to check if the order of JOINS is important in a SQL Querie for runtime and efficiency. I'm using PostgreSQL and for my check I used the example world db from MYSQL (https://downloads.mysql.com/docs/world.sql.zip) and wrote these two…
Netherstorm
  • 143
  • 1
  • 1
  • 6
1
vote
1 answer

Why index scan instead of seek while using comparison operator

There is a table Sales.SalesOrderDetail in database AdventureWorks2014. I have two queries: --Query 1 uses index IX_SalesOrderDetail_ProductID SELECT sod.SalesOrderID FROM Sales.SalesOrderDetail sod WHERE sod.SalesOrderID = 1 and: --Query 2 uses…
StepUp
  • 36,391
  • 15
  • 88
  • 148
1
vote
0 answers

Why could different data in a table be processed with different performance?

I am experiencing a strange performance problem with Postgres 10 database. I have such a table: CREATE TABLE articles ( article_id bigint NOT NULL, content jsonb NOT NULL, -- a few other fields... CONSTRAINT articles_pkey PRIMARY KEY…
greatvovan
  • 2,439
  • 23
  • 43
1
vote
1 answer

Choose legacy/OCRA optimizer automatic?

For lots of small queries, set optimizer = OFF is a good option to reduce the latency. But it is hard for me to make a decision which statement should be used? Is there any server config to auto switch it on/off based on the statement accordingly?
Lei Chi
  • 216
  • 1
  • 14
1
vote
0 answers

Why does breaking out the ID lookups result in different plans/performance

I have 2 tables: tbl_Token (~100 million rows) & tbl_EntryTokenSummary (~3.75 billion rows) I'm trying to understand why the following 2 scenarios result in different 'net' plans. I don't really understand why Scenario 1 even references the…
Corey Aldebol
  • 11
  • 3
1
vote
1 answer

sqlite choose wrong query plan

Consider following example: DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID; WITH RECURSIVE cnt(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM cnt WHERE x<2000) INSERT INTO t1(a,b) SELECT x, x FROM cnt; CREATE…
NikitaRock
  • 353
  • 2
  • 9
1
vote
1 answer

Force GIN index scan in postgresql 9.4

I'm having a table of locations(29 million rows approx) Table "public.locations" Column | Type| Modifiers ------------------------------------+-------------------+------------------------------------------------------------ id | integer |…
user2512324
  • 791
  • 1
  • 6
  • 21
1
vote
1 answer

Avoid Sort operator in index plan of an update query which does not have an ORDER BY keyword

I have an update query:- Update PM.Contractual_Allowances Set Provider_ID = 3 Where Tenant_ID = 1 and Carrier_ID = 203 For this above query I am getting execution plan as below : I am trying to understand that why do I get the sort operator when…
Ram Mehta
  • 449
  • 1
  • 6
  • 20
1
vote
1 answer

Why Mongo query for null filters in FETCH after performing IXSCAN

According to Mongo Documentation, The { item : null } query matches documents that either contain the item field whose value is null or that do not contain the item field. I can't find documentation for this, but as far as I can tell, both…
Andy
  • 7,885
  • 5
  • 55
  • 61
1
vote
1 answer

Mongo doesn't optimize $or query by combining two IXSCANs

I have an orders collection with the following index, among others: {location: 1, completedDate: 1, estimatedProductionDate: 1, estimatedCompletionDate: 1} I'm performing the following query: db.orders.find({ status: {$in: [1, 2, 3]}, location:…
Andy
  • 7,885
  • 5
  • 55
  • 61
1
vote
0 answers

Spit graphical query plans as jpegs?

I have SQL Server and I want to get a graphical query plan. Then, I want to save it as a .jpg or .png. Is this possible? What tools are available? I am new and can't seem to find much interest in this kind of work - thanks for any help.
audlift-bit
  • 163
  • 1
  • 7
1
vote
1 answer

Why PostgreSQL does so hard plan for simple query?

I have a 25mln rows "Zemla" table with index CREATE INDEX zemla_level ON public."Zemla" USING btree (level); Now I do simple query select * from "Zemla" where level = 7 and get very hard query plan Bitmap Heap Scan on "Zemla" …
alexey2baranov
  • 414
  • 5
  • 15
1
vote
1 answer

Why does adding a JOIN completely modify the query planner behaviour?

I have two queries: SELECT "recipes_recipe"."short_name", COUNT(DISTINCT "recipes_recipe"."quantity_type") AS "quantity_type_count", SUM("measures_measure"."standard") AS "volume", CASE WHEN COUNT(DISTINCT…
Gagaro
  • 752
  • 7
  • 17
1 2 3
…
8 9