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

Why is Postgres performance greatly reduced upgrading from 9.2.24 to 10.17?

Problem I have the query,SELECT p.* FROM parties p INNER JOIN bib b ON p.id=b.id;. On Postgres version 9.2.24, this query takes 12 or 13 minutes. Here is the output of EXPLAIN ANALYZE on said query: …
iluvfugu
  • 71
  • 6
0
votes
1 answer

Different execution plan for local PostgreSQL client for same query

For some reason, the same query executed locally and from my golang backend application uses different execution plans. Flow of events: I have encountered an inefficient plan running from my backend application to the database. After connecting to…
Alechko
  • 1,406
  • 1
  • 13
  • 27
0
votes
2 answers

PostgreSQL chooses wrong multicolumn index for ordered query

I am using PostgreSQL 13.4. I would like to execute the following query in order to receive the date of the most recent effect for each campaign in a set: SELECT DISTINCT ON (campaignid) campaignid, created FROM effects WHERE …
Alechko
  • 1,406
  • 1
  • 13
  • 27
0
votes
0 answers

Why is the Postgres query planner changing the plan when base table is used instead of a child table in the query?

Below is the version of PostgreSQL used: SELECT version(); version ----------------------------------------------------------------------------------------------------------- PostgreSQL 9.6.22 on…
Siddalingaprasad R
  • 1
  • 2
0
votes
0 answers

PSQL 9.6 - Why does query plan change every time I run it?

We have a production POSTGRES 9.6 db with about 100M records (LOYALTY) and new table (< few thousands as of now) "INFO" Base Table (written in django) class Loyalty(models.Model): customer = models.ForeignKey(Customer, db_index=True) order =…
zcahfg2
  • 861
  • 1
  • 12
  • 27
0
votes
0 answers

Force sql from VB.net app to cache query plan for specific sql statements using only one command (without using multiple GO statements)

I am facing the following problem . I need to get in my vb.net program SQL's query plan in xml for a query programatically.Then i will parse it and get some info but thats not the case. Via Management studio when i use the following set of commands…
Ted Kon
  • 87
  • 1
  • 6
0
votes
1 answer

Why using same field when filtering cause different execution time? (different index usage)

When I run query and filter by agreement_id it is slow, but when I filter by an alias id it is fast. (Look at the end of the query) Why using same field when filtering cause different execution time? Links to explain analyze: slow1, slow2 fast1,…
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
0
votes
1 answer

Does SQL Server consider which index is already in memory (cached) in query plan?

I was wondering if SQL server (or other RDBMS for that matter) considers what it already has in memory when creating a query plan, e.g.: There are 2 indexes that are about equally good in serving the request but one of them is already either fully…
Ilya Chernomordik
  • 27,817
  • 27
  • 121
  • 207
0
votes
1 answer

PostgreSQL n_distinct statistics setting

Are there multiple ways to set n_distinct in PostgreSQL? Both of these seem to be doing the same thing but end up changing a different value within pg_attribute. What is the difference between these two commands? alter table my_table alter column…
hooinator
  • 339
  • 2
  • 13
0
votes
1 answer

PostgreSQL upgrade to 12+ changes hash join to slow nested loop

We're trying to upgrade from the version 9 series and have a deal-breaking slow query, which runs okay in 10 and 11 but is many times slower in 12 and 13. I've tested across minor versions in the 11 and 12 series, and the minor version doesn't…
Tavin
  • 390
  • 2
  • 13
0
votes
1 answer

Postgresql, manually use analyze $table_name after each update both on indexes or on structure to update query planner? Really?

I have been trying queries and indexes for two days to find the fastest solution and only today I discover thanks to this answer that using the "analyze $table_name;" command the EXPLAIN and then the query changes completely after adding or removing…
Fred Hors
  • 3,258
  • 3
  • 25
  • 71
0
votes
1 answer

Query planner differences in similar queries

I have two tables, one for profiles and one for the profile's employment status. The two tables have a one-to-one relationship. One profile might might not have an employment status. The table schemas are as below (irrelevant columns removed for…
moray95
  • 937
  • 2
  • 9
  • 12
0
votes
0 answers

How clever is the PostgreSQL planner

I mainly use the R package dbplyr to interact with a PostgreSQL database. That works by "piping" operations which are then translated to SQL and executed in one query. This tends to result in many nested joins. I wonder how clever the planner is…
Rasmus
  • 69
  • 7
0
votes
1 answer

Apache Drill Query Execution plan don't use MongoDB Index

The query plan was showing a collection scan which is going over all the rows in the mongo collection. Hence I created an index on the where clause columns, expecting Drill to choose the index based access plan. But drill continues to use the full…
bukli
  • 172
  • 2
  • 9
0
votes
2 answers

Query plan difference inner join/right join "greatest-n-per-group", self joined, aggregated query

For a small Postgres 10 data warehouse I was checking for improvements in our analytics queries and discovered a rather slow query where the possible improvement basically boiled down to this subquery (classic greatest-n-per-group problem): SELECT…
SebastianBerndt
  • 3
  • 2
1 2 3
…
8 9