What is explain Plan and Query cost in PostgreSQL DB

 What is explain Plan and Query cost in PostgreSQL DB

dvdrental=# select count(*) from actor;

 count

-------

   200

(1 row)

dvdrental=# select reltuples,relpages from pg_class where relname='actor';

 reltuples | relpages

-----------+----------

       200 |        2

(1 row)

How to get explain plan 

dvdrental=# explain select * from actor;

                       QUERY PLAN

--------------------------------------------------------

 Seq Scan on actor  (cost=0.00..4.00 rows=200 width=25)

(1 row)

dvdrental=# \d actor;

                                            Table "public.actor"

   Column    |            Type             | Collation | Nullable |                 Default

-------------+-----------------------------+-----------+----------+-----------------------------------------

 actor_id    | integer                     |           | not null | nextval('actor_actor_id_seq'::regclass)

 first_name  | character varying(45)       |           | not null |

 last_name   | character varying(45)       |           | not null |

 last_update | timestamp without time zone |           | not null | now()

dvdrental=# explain select * from actor where first_name like 'a%';

                      QUERY PLAN

------------------------------------------------------

 Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)

   Filter: ((first_name)::text ~~ 'a%'::text)

(2 rows)

dvdrental=# explain select * from actor where first_name like 'a%' order by first_name;

                         QUERY PLAN

------------------------------------------------------------

 Sort  (cost=4.51..4.51 rows=1 width=25)

   Sort Key: first_name

   ->  Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)

         Filter: ((first_name)::text ~~ 'a%'::text)

(4 rows)

dvdrental=# explain select * from actor order by first_name;

                          QUERY PLAN

--------------------------------------------------------------

 Sort  (cost=11.64..12.14 rows=200 width=25)

   Sort Key: first_name

   ->  Seq Scan on actor  (cost=0.00..4.00 rows=200 width=25)

(3 rows)

dvdrental=# create index test_idx on actor(first_name);

CREATE INDEX

dvdrental=#

dvdrental=# explain select * from actor order by first_name;

                          QUERY PLAN

--------------------------------------------------------------

 Sort  (cost=11.64..12.14 rows=200 width=25)

   Sort Key: first_name

   ->  Seq Scan on actor  (cost=0.00..4.00 rows=200 width=25)

(3 rows)

dvdrental=# explain select * from actor where first_name like 'a%';

                      QUERY PLAN

------------------------------------------------------

 Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)

   Filter: ((first_name)::text ~~ 'a%'::text)

(2 rows)

dvdrental=# explain select * from actor order by first_name;

                          QUERY PLAN

--------------------------------------------------------------

 Sort  (cost=11.64..12.14 rows=200 width=25)

   Sort Key: first_name

   ->  Seq Scan on actor  (cost=0.00..4.00 rows=200 width=25)

(3 rows)

dvdrental=# explain select * from actor where first_name like 'am%' and first_name like 'ca%' order by first_name;

                                          QUERY PLAN

-----------------------------------------------------------------------------------------------

 Sort  (cost=5.01..5.01 rows=1 width=25)

   Sort Key: first_name

   ->  Seq Scan on actor  (cost=0.00..5.00 rows=1 width=25)

         Filter: (((first_name)::text ~~ 'am%'::text) AND ((first_name)::text ~~ 'ca%'::text))

(4 rows)

dvdrental=# explain select first_name from actor where first_name like 'am%' and first_name like 'ca%' order by first_name;

                                          QUERY PLAN

-----------------------------------------------------------------------------------------------

 Sort  (cost=5.01..5.01 rows=1 width=6)

   Sort Key: first_name

   ->  Seq Scan on actor  (cost=0.00..5.00 rows=1 width=6)

         Filter: (((first_name)::text ~~ 'am%'::text) AND ((first_name)::text ~~ 'ca%'::text))

(4 rows)

How to get Query cost

dvdrental=#

dvdrental=# select relpages from pg_class  where relname='actor';

 relpages

----------

        2

(1 row)

dvdrental=# show seq_page_cost;

 seq_page_cost

---------------

 1

(1 row)

dvdrental=# select count(*) from actor;

 count

-------

   200

(1 row)

dvdrental=# show cpu_tuple_cpst;

ERROR:  unrecognized configuration parameter "cpu_tuple_cpst"

dvdrental=# show cpu_tuple_cost;

 cpu_tuple_cost

----------------

 0.01

(1 row)

dvdrental=# explain select * from actor;

                       QUERY PLAN

--------------------------------------------------------

 Seq Scan on actor  (cost=0.00..4.00 rows=200 width=25)

(1 row)

Query Cost =relpages*seq_page_cost+count*cpu_tuple_cost

dvdrental=# select 2*1+200*0.01;

 ?column?

----------

     4.00

(1 row)

dvdrental=#