Thread: how to use all the options of EXPLAIN?

Could anyone please tell me how to use all the options of explain in Postgresql 9.
http://www.postgresql.org/docs/9.0/static/sql-explain.html

I can use EXPLAIN ANALYZE.

FORMAT:


explain     (format yaml)   select * from tab1;
              QUERY PLAN             
---------------------------------------
 - Plan:                              +
     Node Type: "Seq Scan"            +
     Relation Name: "tab1"+
     Alias: "tab1"        +
     Startup Cost: 0.00               +
     Total Cost: 21558.94             +
     Plan Rows: 1688234               +
     Plan Width: 333
(1 row)

But

explain     (format yaml)  analyze  select * from tab1;
ERROR:  syntax error at or near "analyze"
LINE 1: explain     (format yaml)  analyze  select * from tab1


BUFFERS:

explain  (buffers true)   select * from tab1;
ERROR:  EXPLAIN option BUFFERS requires ANALYZE

But

explain  (buffers true) analyze  select * from tab1;
ERROR:  syntax error at or near "analyze"
LINE 1: explain  (buffers true) analyze  select * from tab1...


Same for COSTS.

Does any of our experts use these options? If yes, please tell me how.

Thanks.

On 15/09/11 15:43, AI Rumman wrote:
> Could anyone please tell me how to use all the options of explain in
> Postgresql 9.
> http://www.postgresql.org/docs/9.0/static/sql-explain.html
>
> I can use EXPLAIN ANALYZE.
> explain     (format yaml)   select * from tab1;
>
> But
>
> explain     (format yaml)  analyze  select * from tab1;
> ERROR:  syntax error at or near "analyze"
> LINE 1: explain     (format yaml)  analyze  select * from tab1
>
>
> BUFFERS:
>
> explain  (buffers true)   select * from tab1;
> ERROR:  EXPLAIN option BUFFERS requires ANALYZE
>
> But
>
> explain  (buffers true) analyze  select * from tab1;
> ERROR:  syntax error at or near "analyze"
> LINE 1: explain  (buffers true) analyze  select * from tab1...
>
>
> Same for COSTS.
>
> Does any of our experts use these options? If yes, please tell me how.


Try putting the ANALYZE inside the parentheses..
eg
EXPLAIN (buffers true, analyze) select * from tab1;

When I am using the following query, I got shared hit=8857.

EXPLAIN (buffers true, analyze) select * from act;
                                                        QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------------
 Seq Scan on act  (cost=0.00..6308.82 rows=542312 width=735) (actual time=0.007..516.199 rows=542312 loops=1)
   Buffers: shared hit=8857
 Total runtime: 1004.067 ms

Wherears, when I am using the following query, I got  Buffers: shared hit=416 read=46350
 
 EXPLAIN (buffers true, analyze) select * from crm;
 
                                                          QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on crm  (cost=0.00..21558.94 rows=1688234 width=333) (actual time=0.040..1738.903 rows=1688291 loops=1)
   Buffers: shared hit=416 read=46350
 Total runtime: 3257.872 ms

Could you please tell me why the different that is why I get read for one query and not for others?

Thanks.

On Thu, 2011-09-15 at 12:17 +0600, AI Rumman wrote:
> When I am using the following query, I got shared hit=8857.
>
> EXPLAIN (buffers true, analyze) select * from act;
>                                                         QUERY
> PLAN
>
--------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on act  (cost=0.00..6308.82 rows=542312 width=735) (actual
> time=0.007..516.199 rows=542312 loops=1)
>    Buffers: shared hit=8857
>  Total runtime: 1004.067 ms
>
> Wherears, when I am using the following query, I got  Buffers: shared
> hit=416 read=46350
>
>  EXPLAIN (buffers true, analyze) select * from crm;
>
>                                                           QUERY
> PLAN
>
-------------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on crm  (cost=0.00..21558.94 rows=1688234 width=333) (actual
> time=0.040..1738.903 rows=1688291 loops=1)
>    Buffers: shared hit=416 read=46350
>  Total runtime: 3257.872 ms
>
> Could you please tell me why the different that is why I get read for one
> query and not for others?
>

The act table was in PostgreSQL cache memory, so it reads it from there.
A small part of the crm table was in PostgreSQL cache memory, it read
this part from there, but had to ask the OS to get the rest of the table
from the OS memory or from disk.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com