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
25
votes
2 answers
Why Planing time and Execution time are so different Postgres?
I make such a query
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(id) q, day
FROM my_table
WHERE role_id && ARRAY[15, 17]
GROUP BY "day"
ORDER BY "day" DESC;
And Postgres responds to me with this:
Planning time:…
Thor Samsberg
- 2,219
- 5
- 22
- 30
6
votes
1 answer
Query plan caching with pl/pgsql
I am having troubles understanding how the query plan caching works for pl/pgsql.
I want to built all-in-one queries with JOINs and IFs, so I will have multiple and different query parameters, and I will be searching in more that one tables.
At…
slevin
- 4,166
- 20
- 69
- 129
5
votes
1 answer
Postgresql IN vs ANY operator performance differences with subquery
I have two queries which do the same thing.
1
SELECT *
FROM "Products_product"
WHERE ("Products_product"."id" IN
(SELECT U0."product_id"
FROM "Products_purchase" U0
WHERE (U0."state" = 1
…
Tiancheng Liu
- 782
- 2
- 9
- 22
4
votes
1 answer
Why does removing the BINARY function call from my SQL query change the query plan so dramatically?
I have a SQL query that looks for a specific value in a table and then does inner joins across three tables to fetch the result set. The three tables are fabric_barcode_oc, fabric_barcode_items & fabric_barcode_rolls
Initial Query
The initial…
random_coder_101
- 1,782
- 3
- 24
- 50
4
votes
2 answers
How to fix PostgreSQL generic plan estimate for any($1) array parameter?
I've run into an issue where PostgreSQL (13.1 on windows, default config) would start preferring a generic plan after 5 executions because the plan estimate is clearly wrong. Thing is, the custom plan is up to 10 times faster than the generic plan,…
griffin
- 1,261
- 8
- 24
4
votes
2 answers
Sorting a large spatial selection is not using GiST index (Postgres 11.5)
I'm having a table (demo) with a sequence as its primary key (seqno) and a geometry property contained within a JSONB column (doc). I have configured a primary key constraint for the sequence column and a GiST index for the geometry. I have already…
JFX
- 432
- 1
- 4
- 10
4
votes
1 answer
Performance difference date_trunc('day', ) vs ::date
If I want to group a column of timestamps, say registered_at by the day on which they occurred, I can use either date_trunc('day', registered_at) or registered_at::date. The first removes the hours and smaller units from the timestamp, but still…
Ruben Helsloot
- 12,582
- 6
- 26
- 49
4
votes
3 answers
Mysql index hints in Hibernate query
How can we specify Mysql index hints in Hibernate query ?
Using index hints in Mysql queries becomes imperative in cases where query planner doesn't choose right indexes (ref:…
Vijay Kansal
- 809
- 12
- 26
4
votes
2 answers
Actual number of rows greater than estimated number of rows
I wonder why I have actual number of rows greater than estimated number of rows?
The table has a clustered primary key defined as :
CONSTRAINT [PK_AIRQUALITYTS] PRIMARY KEY CLUSTERED
(
[FeatureID] ASC,
[ParameterID] ASC,
[MeasurementDateTime]…
user641812
- 335
- 5
- 19
4
votes
2 answers
Function parameter anyelement, PostgreSQL bug?
I do not see the bug in this implementation:
CREATE FUNCTION foo(anyelement) RETURNS SETOF int AS $f$
SELECT id FROM unnest(array[1,2,3]) t(id)
WHERE CASE WHEN (pg_typeof($1)::text)='integer' THEN $1::int>2 ELSE true END
$f$ LANGUAGE SQL…
Peter Krauss
- 13,174
- 24
- 167
- 304
4
votes
2 answers
How to read PostgreSQL EXPLAIN, order: top down or bottom up?
Here's an example plan on explain.depesz.com:
Limit (cost=65301.950..65301.950 rows=1 width=219) (actual time=886.074..886.074 rows=0 loops=1)
-> Sort (cost=65258.840..65301.950 rows=17243 width=219) (actual time=879.683..885.211 rows=17589…
TheFooProgrammer
- 2,439
- 5
- 28
- 43
3
votes
1 answer
Why is there much difference in query plan with union vs "or" operator
While executing below two queries, I notice serious difference in query plan. Why is that?
select * from table1
where id = 'dummy' or id in (select id from table2 where id = 'dummy')
Query plan
Seq Scan on table1 (cost=8.30..49611.63 rows=254478…
Spring boot progammer
- 137
- 2
- 7
3
votes
0 answers
How can I make postgres use this index instead of a sequential scan in an EXISTS query on a large table with all equal foreign key values?
I am currently trying to improve a postgres query. The following is a minimal setup for reproducing the essence of my problem on postgres-12.6:
CREATE TABLE data_node (id BIGINT PRIMARY KEY);
CREATE TABLE data_entry (id BIGINT PRIMARY KEY, node_fk…
aschnab
- 31
- 1
3
votes
2 answers
How many disk pages are read by a query in Postgres?
I'd like to know how many pages (table + index, if any) are read from disk (and not from cache) when running a single Postgres query. Even better, if there is any way to extract this info from an EXPLAIN ANALYZE.
Settembre Nero
- 141
- 1
- 8
3
votes
1 answer
Why is Postgres query planner affected by LIMIT?
EXPLAIN ANALYZE SELECT "alerts"."id",
"alerts"."created_at",
't1'::text AS src_table
FROM "alerts"
INNER JOIN "devices"
ON "devices"."id" = "alerts"."device_id"
INNER JOIN "sites"
ON …
Julius Žaromskis
- 2,026
- 2
- 14
- 20