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

mysql 5.1.37 query index not being used

I have a query that is performing poorly. Server version: 5.1.37-1ubuntu5.1 (Ubuntu) SELECT * FROM `influencers` WHERE (`influencers`.`twitter_id` = 86861293) LIMIT 1 show create table influencers influencers CREATE TABLE `influencers` ( `id`…
Joelio
  • 4,621
  • 6
  • 44
  • 80
3
votes
1 answer

"Commit prior transaction" when using Explain Plan in SQL Developer

While Using Explain Plan option in SQL Developer, getting below error (mentioned in screenshot). There is no uncommitted transaction in the session. I am getting this error even when first time opening SQL Developer. SQL Developer Version:
Tajinder
  • 2,248
  • 4
  • 33
  • 54
3
votes
1 answer

Print SQL execution plan with python's module psycopg2

Is there any way I can print the information from a SQL execution plan to see it in my Terminal when using python and the psycopg2 module? I tried the following, but nothing is shown in the Terminal: cur.execute(cur.mogrify('explain analyze ' +…
J0ANMM
  • 7,849
  • 10
  • 56
  • 90
3
votes
1 answer

Error message: "PostgreSQL said: could not write block 119518 of temporary file: No space left on device" PostgreSQL

I have a query that, intuitively, should work just fine. But, almost immediately after executing, I am served with this error message: ERROR: could not write block 119518 of temporary file: No space left on device Query failed PostgreSQL said:…
Sebastian
  • 957
  • 3
  • 15
  • 27
3
votes
1 answer

Hive query records processed count

I want to know how many records processed or % of records proccessed by a query to fetch result in hive. I tried describe formatted for query, but unable to do. describe formatted (select * from sample)
kiran
  • 445
  • 1
  • 5
  • 11
3
votes
3 answers

In mysql, is "explain ..." always safe?

If I allow a group of users to submit "explain $whatever" to mysql (via Perl's DBI using DBD::mysql), is there anything that a user could put into $whatever that would make any database changes, leak non-trivial information, or even cause…
tye
  • 1,157
  • 9
  • 11
3
votes
1 answer

How to output "explain analyze" result to a table column?

I have my_table with two text columns: my_query, which contains various DML queries in text form; and and my_explanation, which is entirely populated with nulls. I'd like to do something like: UPDATE my_table SET my_explanation = execute explain…
Dan Jones
  • 31
  • 2
3
votes
1 answer

EXPLAIN ANALYZE on CREATE INDEX with PostgreSQL

We can execute EXPLAIN ANALYZE on a query and on commands like CREATE TABLE but seems like we cannot on a CREATE INDEX. What is the reason for this? Must one only get statistics about an index only elsewhere?
audlift-bit
  • 163
  • 1
  • 7
3
votes
1 answer

Understanding COUNT behavior in queries vs. EXPLAIN vs. functions

I am curious to understand (and perhaps improve) an issue I am having with PostgreSQL 9.6. Names simplified but everything else is taken from a psql session. I start with a materialized view, mv. First, I create two simple functions: CREATE FUNCTION…
rg6
  • 329
  • 2
  • 10
3
votes
2 answers

Postgres 9.6: Parallel query does not take max_parallel_workers_per_gather setting

Postgres 9.6; Centos 6.7 ; 24 cores BigTable1 contains 1,500,000,000 rows; weight 180GB. max_worker_processes = 20 max_parallel_workers_per_gather = 12 1) When running EXPLAIN SELECT date_id, id1, id2, id3, id4, topdomain, ftype,…
Anya
  • 51
  • 1
  • 2
3
votes
2 answers

How can I EXPLAIN several consecutive queries without executing them?

Suppose I have a pair of arbitrary SQL queries, each one depending upon the former ones, e.g. CREATE VIEW v1 ( c3 ) AS SELECT c1 + c2 FROM t1; SELECT sum(c3) FROM v1; DROP VIEW v1; (but note I am not asking about these specific queries - this is…
einpoklum
  • 118,144
  • 57
  • 340
  • 684
3
votes
1 answer

understanding MySQL Explain output

I have a couple of questions regarding MySQL explain. In the first step of the evaluation, it utilizes a REF, for join type. However, upon my research on ref it states the following: All rows with matching index values are read from this table for…
Sam
  • 299
  • 1
  • 2
  • 11
3
votes
2 answers

mongodb keep_mutation stage explain

I use mongo's explain() to check the performance of some queries, sometimes a keep_mutation stage will show up like the following: "executionStats" : { ... "executionStages" : { "stage" :…
webberpuma
  • 691
  • 1
  • 6
  • 21
3
votes
2 answers

Does higher rows count in mysql explain means good or bad?

I have one old MyISAM table where when I submit some count query, table gets locked. If I do the same query, on the same InnoDB table, query gets executed fast. The problem is, the old MyISAM table is still used in production and is under heavy…
black-room-boy
  • 659
  • 2
  • 11
  • 23
3
votes
1 answer

MySQL EXPLAIN - it keeps giving me different explanations each time

I have a really large, complex query I'm trying to optimise using MySQL EXPLAIN SELECT or EXPLAIN EXTENDED SELECT. If I run it against the query, I'll see every table in the query is using Using where in the Extra column, which is great. No data…
Jack
  • 9,615
  • 18
  • 72
  • 112
1 2 3
…
39 40