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
1 answer

Getting Index Scan instead Index Seeking if IN clause larger in Azure Sql

This is the Non clustered index we have : ALTER TABLE [Allocation].[allocation_plan_detail] ADD CONSTRAINT [UQ_AP_TENANT_TYPE_STATUS_PLAN_ITEM_CLUB] UNIQUE NONCLUSTERED ( [tenant_id] ASC, [item_type] ASC, [allocation_plan_status] ASC, …
SUVAM ROY
  • 103
  • 1
  • 10
1
vote
1 answer

Postgres Query uses Bitmap index scan on GIN indexed item. Does not GIN use B-Tree internally?

From postgres docs on GIN - Internally, a GIN index contains a B-tree index constructed over keys. But in my use case, I see Bitmap indexes instead - My Schema and Indexes are created as below. CREATE TABLE customer ( jdata jsonb NOT NULL, …
samshers
  • 1
  • 6
  • 37
  • 84
1
vote
1 answer

PostgreSQL Query Planner/Optimizer: Is there a way to get candidate plans?

In PostgreSQL, we can use "EXPLAIN ANALYZE" on a query to get the query plan of a given SQL Query. While this is useful, is there anyway that we are able to get information on other candidate plans that the optimizer generated (and subsequently…
Ymi
  • 609
  • 6
  • 18
1
vote
0 answers

PostgreSQL using slow index when no data is available

I have the following table structure: CREATE TABLE openiot.medidas_analizadores ( id int4 NOT NULL, datetime timestamptz NOT NULL, (21 other data columns...) e_ap_tot float8 NULL, CONSTRAINT medidas_analizadores_pk …
José D.
  • 4,175
  • 7
  • 28
  • 47
1
vote
1 answer

Query slows down 5 fold after copying DB (on the same computer!)

In a location based app,there's a specific query which has to run fast: SELECT count(*) FROM users WHERE earth_box(ll_to_earth(40.71427000, -74.00597000), 50000) @> ll_to_earth(latitude, longitude) However, when after copying the database by…
yoni keren
  • 300
  • 2
  • 14
1
vote
2 answers

Is there a shared query plan cache for Postgres?

I have a complex postgres query that I've optimised with pg_hint_plan. Planning time is about 150ms while query time is about 30ms. The plan should never change, therefore there's no point in gathering statistics each any every time for each…
Rol
  • 501
  • 4
  • 13
1
vote
0 answers

Postgresql - table performance degradation after ANALYZE

I'm trying to understand this situation: in my test environment (pg14 on debian) I have a table with many rows (18.000.000). Suppose the table is "entities", and in simple terms it has this structure: CREATE TABLE entities ( id BIGINT not…
Marco Casalboni
  • 11
  • 2
1
vote
0 answers

Sequential scan in Postgis reads more than 10x relpages - why?

I am running a query on PostgreSQL+PostGIS+Timescale. The planner, for a subnode, is using a Sequential Scan, so far is reasonable. Nothing too fancy, a filter with a couple of ST_Within and-ed together. The table is, according to pg_class, 252…
Settembre Nero
  • 141
  • 1
  • 8
1
vote
1 answer

Strange Query Plan for SQL Query - Clustered Index Seek

I've got a really strange issue with a Query plan generated for a very simple SQL query. The query is searching a full text index, and returning the count of records. For some reason, this SQL query is producing a Non Clustered Scan on an index,…
JBird
  • 13
  • 4
1
vote
1 answer

PostgreSQL 12.4 query planner ignores sub-partition constraint, resulting in table scan

I have a table T (A int, B int, C long, D varchar) partitioned by each A and sub-partitioned by each B (i.e. list partitions with a single value each). A has cardinality of <10 and B has cardinality of <100. T has about 6 billion rows. When I run…
Niels Voorhoeve
  • 13
  • 2
1
vote
1 answer

Is there a way to explicitly tell the Postgres query planner that columns are dependent?

I have a two tables like this: (My actual tables are different. I'm using these to simplify the problem.) purchases ( item_id, order_id, PRIMARY KEY(item_id, order_id) ) payments ( item_id, order_id, payment_id ) When I issue a query…
Nathan Breit
  • 1,661
  • 13
  • 33
1
vote
0 answers

Query planner behaviour degradation after PostgreSQL update from (10.11 to 11.6)

After updating postgres, I noticed that one of the queries I was using became much slower. After running EXPLAIN ANALYZE I see that it is now using a different index on the same query. Among other columns, my table has an applicationid column which…
Alechko
  • 1,406
  • 1
  • 13
  • 27
1
vote
1 answer

Problems with planner and ddl statements in Apache Calcite

When I try to validate a sql statement that contains "CREATE TABLE" it throws a error: java.lang.AssertionError: Was not expecting value 'CREATE_TABLE' for enumeration 'org.apache.calcite.sql.SqlKind' in this context at…
anthonylouis
  • 45
  • 1
  • 2
  • 6
1
vote
1 answer

How can I optimize a postgresql query where one dependent column is a timestamp

I have a table with a foreign key and a timestamp for when the row was most recently updated. rows with the same foreign key value are updated at roughly the same time, plus or minus an hour. I have an index on (foreign_key, timestamp). This is on…
Doug
  • 43
  • 1
  • 5
1
vote
0 answers

How to point to Hibernate which column to use in WHERE condition?

I have a weird problem with postgresql. Somewhy planner thinks that accessing data via foreign key index is very slow and uses sequental scan. There's a simplified table structure: create table flight ( id bigint not null constraint…
Xobotun
  • 1,121
  • 1
  • 18
  • 29
1 2 3
…
8 9