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
postgres LIKE % operator vs similarity - pg_trgm.similarity_threshold . different query plans
If I use:
SET pg_trgm.similarity_threshold = 0.9;
... where column % 'some string s';
PostgreSQL does an index scan on: gin (column gin_trgm_ops)
In contrast, which should be the same:
... where similarity(column, 'some string s' ) >= 0.9
That…
Vertago
- 315
- 2
- 16
0
votes
2 answers
Why does PostgreSQL not use the index in this scenario?
I have a couple of tables in PostgreSQL 14.6, with indexes as stated here:
# \d uprn
Table "public.uprn"
Column | Type | Collation | Nullable |…
M Somerville
- 4,499
- 30
- 38
0
votes
1 answer
MySQL. Help me understand the following execution plan
I have two tables clients and shows.
Clients Table
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| clientID …
ShrihariShetty
- 1
- 1
0
votes
0 answers
Can Subquery Scalar Values Be Evaluated Before The Query Condition Is Evaluated In PostgreSQL?
Overview
I'm trying to filter on one of two columns depending on the result of a subquery. However, on analysing the queries it shows that the subquery values are evaulated too late - leading to less efficient index or full table scans.
Examples
The…
jBit
- 2,971
- 1
- 24
- 39
0
votes
1 answer
equally configured galera clusters generating different query plans
We have 2 instances of the same galera cluster, so the same database schema with a data difference of 1 month. respectively they are a staging and a production environment.
They run on an identical hardware configuration, same operating system, same…
urobo
- 786
- 2
- 8
- 19
0
votes
1 answer
Does a covering index have an effect on JOIN + BETWEEN operations
I am wondering about how a covering index can help in the case where you first JOIN on a column, and then have BETWEEN constraints on a second and third column. Would a combined index on these three have a positive effect?
Example (fiddle).
CREATE…
Bram Vanroy
- 27,032
- 24
- 137
- 239
0
votes
0 answers
Postgresql - random_page_cost =1.1 and NVMe disk: slower query
My SysAdmin colleague told me that the Postgres'hosts use NVME disk.
How can I check that with Linux command?
Why does the planner/optimizer seem to get it wrong when I set random_page_cost =1.1
To bench it, I created table t_1(id int):
CREATE…
Eric Renault
- 15
- 3
0
votes
1 answer
Replace OUTER APPLY
I want to replace some OUTER APPLYs in my SQL because they seem to be a little bit slow and eating resources () on a poor VPS. I have no idea what to use instead? LEFT OUTER JOIN (??)
Here's my code
SELECT e.Id,
Decision.Comment,
…
Cătălin Rădoi
- 1,804
- 23
- 43
0
votes
0 answers
How to improve query plan?
I try to improve my query plan
SELECT top 100
cr1.Id,
cr1.ArticleId,
cr1.CrossArticleId,
cr1.CrossesStatusId,
cr1.CrossesSourceId
FROM Detail.Crosses AS cr1
INNER JOIN Web.Detail.Article AS a1 ON a1.Id = cr1.ArticleId
INNER…
Artur Makvetsyan
- 1
- 2
0
votes
1 answer
PostgreSQL: Dirtied blocks when using many OR conditions in query
I'm using PostgreSQL 13.7.
When trying to optimize a query that is made of several sub-queries, I noticed that reducing the number of OR clauses in the final part significantly improves performance (800ms to 70ms). The part in question is this (only…
Alechko
- 1,406
- 1
- 13
- 27
0
votes
0 answers
Query plan update with strange behavior
I have a table defined as the script below
CREATE TABLE Schema1.Object1(
Column1 [int] IDENTITY(1,1) NOT NULL,
Column2 [int] NOT NULL,
Column3 [int] NOT NULL,
Column4 [varchar](255) NOT NULL,
Column5 [varchar](15) NOT NULL,
…
Luca Murzio
- 37
- 1
- 5
0
votes
1 answer
Match any value from a long list with a postgres GIN index
The schema:
# Table name: foos
#
# id :integer not null, primary key
# bar_ids :integer is an Array
#
# Indexes
#
# index_foos_on_bar_ids (bar_ids) USING gin
The query:
SELECT * FROM foos
WHERE (
bar_ids && '{
28151247,
…
Qaz
- 1,556
- 2
- 20
- 34
0
votes
0 answers
Join on UUID column not using index for multiple values
I have a PostgreSQL database that I cloned.
Database 1 has varchar(36) as primary keys
Database 2 (the clone) has UUID as primary keys.
Both contain the same data. What I don't understand is why queries on Database 1 will use the index but…
mango
- 5,577
- 4
- 29
- 41
0
votes
1 answer
Relpages and reltuples under MVCC
If I understand correctly, under MVCC(multi version concurrency control), dead tuples are left in the page, until the Vacuum comes in and mark them "unused", and until "vacuum full" comes in and reorgnize them to defragment the space -- so we use…
somenickname
- 539
- 7
- 19
0
votes
1 answer
Gap in the execution-time parts in the query plan
I have trouble understanding the following query plan (anonyized). There seems to be a gap in how much time the actual parts of the query took and a part is missing.
The relevant part:
-> Sort (cost=306952.44..307710.96 rows=303409 width=40)…
valenterry
- 757
- 6
- 21