Stack Overflow
Stack Overflow

Questions tagged [explain]

Explain is a SQL command that shows the execution plan of a query.

Explain is an useful command used in various Database servers(like Oracle, MySQL, PostgreSQL, etc). It shows the execution plan of a query.

Oracle: 

EXPLAIN PLAN FOR SELECT …

PostgreSQL, MySQL:

EXPLAIN SELECT …

MSSQL doesn't support explain, but uses

"SET SHOWPLAN_TEXT ON".

instead.

599 questions
2
votes
0 answers

XGboost with monotonic constraints for explainability

I am building in Python a credit scorecard using this public dataset: https://www.kaggle.com/sivakrishna3311/delinquency-telecom-dataset It's a binary classification problem: Target = 1 -> Good applicant Target = 0 -> Bad applicant I only have…
Giampaolo Levorato
  • 1,055
  • 1
  • 8
  • 22
2
votes
1 answer

PostgreSQL EXPLAIN: How do I see a plan AS IF certain tables had millions of rows?

This is a question about PostgreSQL's EXPLAIN command. This command shows you how the optimizer will execute your SQL based on the data in your tables. We are not in prod yet, so all of our tables have ~100 rows or less. Is there a way to get…
Daniel Kaplan
  • 62,768
  • 50
  • 234
  • 356
2
votes
0 answers

postgreSQL slow query with high shared read

i have a problem with my postgreSQL database, actually i'm developer not DBA so i don't have enough experience to deal with problems like this. I ran this query at my psql: explain (analyze true, buffers true) select json_build_object('tracker_id',…
DrJempo
  • 75
  • 1
  • 9
2
votes
1 answer

2 exactly same mysql queries give 2 differents 'explain' output : why?

I found a very weird mysql behaviour : when I run a specific query twice, the explain of this query is different the second time : query = SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`,…
Eric
  • 4,821
  • 6
  • 33
  • 60
2
votes
2 answers

Why and how SQL condition not in psql explain plan?

I try to analyze plans for join query benchmark https://github.com/gregrahn/join-order-benchmark For example, I execute the following command: EXPLAIN SELECT * FROM aka_name AS an, cast_info AS ci, company_name AS cn, keyword AS k, …
Mihail Salnikov
  • 137
  • 2
  • 11
2
votes
1 answer

Understanding rows_examined_per_scan and rows_produced_per_join

I'm not mysql guy, but I've got opportunity to work with that recently - I've got to optimize some query on Mysql5.7 (I have to cover that also on 5.6, but started with 5.7 as it has apparently more info in explain) running on AWS Aurora. It takes a…
Adam Tokarski
  • 659
  • 4
  • 13
2
votes
1 answer

How can I get DAG of Spark Sql Query execution plan?

I am doing some analysis on spark sql query execution plans. the execution plans that explain() api prints are not much readable. If we see spark web UI, a DAG graph is created which is divided into jobs, stages and tasks and much more readable. Is…
akash patel
  • 163
  • 9
2
votes
1 answer

Do EXTENDED STATISTICS have any trade offs in PostgreSQL?

Extended Statistics help the planner with evaluating the query complexity in order to choose the correct algorithm for processing the query. Creating new statistics on a couple of dependant columns in a table probably creates some complex structure…
toddddos
  • 321
  • 2
  • 11
2
votes
2 answers

Why - or when - doesn't MySQL use indexes for OR conditions, if it does for AND conditions?

I have a table the_table with attributes the_table.id, the_table.firstVal and the_table.secondVal (the primary key is the_table.id, of course). After defining an index over the first non-key attribute like this: CREATE INDEX idx_firstval ON…
Mew
  • 368
  • 1
  • 11
2
votes
3 answers

LEFT and RIGHT keywords in ECL

I'm new to ECL and having trouble trying to understand RIGHT and LEFT keywords (used in DEDUP, GROUP and other functions). How do they work? The ECL reference guide has a single paragraph about what it's supposed to do. for example, what is it doing…
ocarlos-
  • 41
  • 2
2
votes
1 answer

PostgreSQL: Is it possible to print the query plan of a running query?

I ran the same report query regularly and most of the time the query completes within minutes. However, it sometimes gets stuck for hours in a nondeterministic way. When it got stuck, I repeated the same query with EXPLAIN ANALYZE, but the latter…
Marcin Barczyński
  • 361
  • 1
  • 11
2
votes
3 answers

Why is this query taking so long?

This query is appearing in my slow log on a mysql system, # Query_time: 37 Lock_time: 0 Rows_sent: 5 Rows_examined: 405199 select euroapps.id, euroapps.name, euroapps.imageurl, euroapps.created, application_price.retail_price, euroapps.count…
kitenski
  • 639
  • 2
  • 16
  • 25
2
votes
0 answers

Can JDBC statement run execution plan / explain plan?

Can JDBC statement run explain plan on query string? The code throws SQL exception Error message: Incorrect syntax near the keyword 'plan'. Stacktrace is null I just copy from internet of using stmt.execute. However, it seems that stmt.execute()…
ITpeople
  • 29
  • 1
  • 5
2
votes
1 answer

MySQL GROUP BY with Using Temporary unnecessarily?

I am trying to optimize a query. Using EXPLAIN tells me it is Using temporary. This is really inefficient given the size of the table (20m+ records). Looking at the MySQL documentation Internal Temporary Tables I don't see anything that would imply…
newms87
  • 834
  • 1
  • 10
  • 23
2
votes
3 answers

Slow SQL query using Order By

I am far from a SQL guru and I am trying to execute: SELECT `apps`.* FROM `apps` INNER JOIN `similars` ON (`apps`.id = `similars`.similar_app_id OR `apps`.id = `similars`.app_id) WHERE (`similars`.app_id = 542 OR `similars`.similar_app_id =…
Stuart
  • 81
  • 2
  • 7
1 2 3
…
39 40