Difference between revisions of "EXPLAIN (PostgreSQL)"

From wikieduonline
Jump to navigation Jump to search
 
(19 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
https://www.postgresql.org/docs/current/sql-explain.html
 
https://www.postgresql.org/docs/current/sql-explain.html
  
<code>EXPLAIN --</code> show the execution plan of a statement
+
<code>EXPLAIN</code> show the execution plan of a statement
  
 
  .../...
 
  .../...
   ->  [[Index Scan]] using your_key on your_table your_table  (cost=0.43..8.39 rows=1 width=952) (actual time=1.030..1.030 rows=0 [[loops]]=39649)
+
   ->  [[Index Scan]] using your_key on your_table your_table  ([[cost]]=0.43..8.39 rows=1 width=952) (actual time=1.030..1.030 rows=0 [[loops]]=39649)
 
         Index Cond: (id = your_id)
 
         Index Cond: (id = your_id)
 
         Filter: (your_filter_id = ######)
 
         Filter: (your_filter_id = ######)
Line 10: Line 10:
 
  Planning Time: 0.441 ms
 
  Planning Time: 0.441 ms
 
  Execution Time: 50387.333 ms
 
  Execution Time: 50387.333 ms
 +
 +
==Simple query on a table with a single integer column and 10000 rows==
 +
<ref>https://www.postgresql.org/docs/9.1/sql-explain.html</ref>
 +
<pre>EXPLAIN SELECT * FROM foo;
 +
 +
                      QUERY PLAN
 +
---------------------------------------------------------
 +
Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
 +
(1 row)</pre>
 +
 +
[[JSON]] formatting:
 +
 +
EXPLAIN (FORMAT JSON) SELECT * FROM foo;
 +
            QUERY PLAN         
 +
--------------------------------
 +
  [                            +
 +
    {                          +
 +
      "Plan": {                +
 +
        "Node Type": "Seq Scan",+
 +
        "Relation Name": "foo", +
 +
        "Alias": "foo",        +
 +
        "Startup Cost": 0.00,  +
 +
        "Total Cost": 155.00,  +
 +
        "Plan Rows": 10000,    +
 +
        "Plan Width": 4        +
 +
      }                        +
 +
    }                          +
 +
  ]
 +
(1 row)
  
 
== Related terms ==
 
== Related terms ==
 
* [[Bitmap scan]]
 
* [[Bitmap scan]]
* [[EXPLAIN ANALYZE]]
+
* [[EXPLAIN ANALYZE (MySQL)]]
 +
* [[EXPLAIN ANALYZE (PostgreSQL)]]
 +
* [[WAL]]
 +
* [[pg_statistic]]
 +
* [[PostgreSQL parallel query]]
  
 
== Activities ==
 
== Activities ==

Latest revision as of 14:11, 14 April 2022

https://www.postgresql.org/docs/current/sql-explain.html

EXPLAIN show the execution plan of a statement

.../...
 ->  Index Scan using your_key on your_table your_table  (cost=0.43..8.39 rows=1 width=952) (actual time=1.030..1.030 rows=0 loops=39649)
       Index Cond: (id = your_id)
       Filter: (your_filter_id = ######)
       Rows Removed by Filter: 1
Planning Time: 0.441 ms
Execution Time: 50387.333 ms

Simple query on a table with a single integer column and 10000 rows[edit]

[1]

EXPLAIN SELECT * FROM foo; 

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)

JSON formatting:

EXPLAIN (FORMAT JSON) SELECT * FROM foo;
           QUERY PLAN           
--------------------------------
 [                             +
   {                           +
     "Plan": {                 +
       "Node Type": "Seq Scan",+
       "Relation Name": "foo", +
       "Alias": "foo",         +
       "Startup Cost": 0.00,   +
       "Total Cost": 155.00,   +
       "Plan Rows": 10000,     +
       "Plan Width": 4         +
     }                         +
   }                           +
 ]
(1 row)

Related terms[edit]

Activities[edit]

See also[edit]

  • https://www.postgresql.org/docs/9.1/sql-explain.html
  • Advertising: