Postgresql explain

Chennai.py meetup

24th Oct 2015

@h6165

Abhishek Yadav

Active in Chennai.py since Sep 2013

Talked before in Nov 2013

Co-organizer: Chennai.rb

ரூபீ ப்ரோக்ராமர

0. Definitions

The definition

EXPLAIN show us the potential query plan along with costs

EXPLAIN helps us verify our speculations on query performance.

It also gives some insight into the algorithms Postgresql uses

EXPLAIN ANALYSE shows the query plan and costs along with executing the query.

BEGIN .. EXPLAIN ANALYSE ... ROLLBACK

Executes the query, explains it and rollsback

The syntax

EXPLAIN <query>;

EXPLAIN ANALYSE <query>;

BEGIN;
EXPLAIN ANALYSE <query>;
ROLLBACK;

The anatomy

EXPLAIN SELECT count(*) FROM comments;

                             QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=1382.00..1382.01 rows=1 width=0)
   -> Seq Scan on comments  (cost=0.00..1257.00 rows=50000 width=0)
(2 rows)

No. of rows output

The cost

The algorithm

The anatomy

• The EXPLAIN output is a tree
• It should be read bottom-up (leaf to root)
• Cost of a step is a summation of child steps
• 'rows' are the rows emitted by a step, not the rows scanned

The cost

• Quantification of the effort needed on a arbitrary scale
• The exact number doesn't mean anything. Can be used for comparisons
• Lower the better
• Is proportional to the no. of blocks read, no. of random blocks read. And to CPU operations too by a small measure

1. Explain where

Explain where: Quiz

Which query costs better - one with where filter or the one without?

(there are no indexes)

SELECT * FROM comments;

SELECT * FROM comments WHERE post_id=1;

Explain where: Quiz

EXPLAIN SELECT * FROM comments;

                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on comments  (cost=0.00..1257.00 rows=50000 width=72)
(1 row)

One with the filter is more costly. Read cost is the same, CPU cost is higher

EXPLAIN SELECT * FROM comments WHERE post_id=1;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on comments  (cost=0.00..1382.00 rows=5 width=72)
   Filter: (post_id = 1)
(2 rows)

2. Let there be index

Explain and index

CREATE INDEX ON comments (post_id);
EXPLAIN SELECT * FROM comments WHERE id=12345;


                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using comments_pkey on comments  (cost=0.41..8.43 rows=1 width=72)
   Index Cond: (id = 12345)
(2 rows)

Adding an index on filter clause may reduce the cost.

Explain and index: Quiz

Will adding index on the gender field improve query cost ? 

Is second one better than the first ?

SELECT * FROM users WHERE gender='M'; 

CREATE INDEX ON users (gender);
SELECT * from users WHERE gender='M'; 

EXPLAIN SELECT * FROM users WHERE gender='m';

                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on users  (cost=0.00..211.28 rows=4990 width=33)
   Filter: ((gender)::text = 'm'::text)
(2 rows)

Low cardinality indexes are ignored!

No

CREATE INDEX ON users (gender);
EXPLAIN SELECT * FROM users WHERE gender='m';


                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on users  (cost=0.00..211.28 rows=4990 width=33)
   Filter: ((gender)::text = 'm'::text)
(2 rows)

Explain and index: Quiz

• An index on a column that can yield very few possible values
• Cardinality = no of possible values / total rows
• Database may ignore index and will do a full scan if cardinality is too low
• The limit varies across databases, but is generally 10-20%
• Explain helps us check the usefulness of an index. An index always increases write costs, so this is important

Explain and index: Low cardinality

3. Explain JOIN

Explain JOIN: Quiz

SELECT posts.*, comments.* 
  FROM posts LEFT JOIN comments
  ON posts.id = comments.post_id;

JOIN : Adding index on columns in ON clause.

Will that improve query performance?

CREATE INDEX ON comments (post_id);
CREATE INDEX ON posts (id);

EXPLAIN
  SELECT posts.*, comments.*
   FROM posts LEFT JOIN comments
   ON posts.id = comments.post_id;

                               QUERY PLAN
------------------------------------------------------------------------
 Hash Right Join  (cost=394.50..2589.00 rows=50000 width=150)
   Hash Cond: (comments.post_id = posts.id)
   ->  Seq Scan on comments  (cost=0.00..1257.00 rows=50000 width=72)
   ->  Hash  (cost=257.00..257.00 rows=11000 width=78)
         ->  Seq Scan on posts  (cost=0.00..257.00 rows=11000 width=78)
(5 rows)

JOIN: Will adding indexes on columns in ON clause improve cost?

No

CREATE INDEX ON posts (id);
CREATE INDEX on comments (post_id);

EXPLAIN
  SELECT posts.*, comments.*
   FROM posts LEFT JOIN comments
   ON posts.id = comments.post_id;

                               QUERY PLAN
------------------------------------------------------------------------
 Hash Right Join  (cost=394.50..2589.00 rows=50000 width=150)
   Hash Cond: (comments.post_id = posts.id)
   ->  Seq Scan on comments  (cost=0.00..1257.00 rows=50000 width=72)
   ->  Hash  (cost=257.00..257.00 rows=11000 width=78)
         ->  Seq Scan on posts  (cost=0.00..257.00 rows=11000 width=78)
(5 rows)

Explain JOIN: Quiz

JOIN: Will adding indexes on columns in ON clause improve cost?

Explain JOIN: Quiz

Does not use any index in any step.

Performs Seq Scan on all 11000 posts rows and 50000 comments.

The final Join step is also high in cost (initial as well as overall)

                               QUERY PLAN
------------------------------------------------------------------------
 Hash Right Join  (cost=394.50..2589.00 rows=50000 width=150)
   Hash Cond: (comments.post_id = posts.id)
   ->  Seq Scan on comments  (cost=0.00..1257.00 rows=50000 width=72)
   ->  Hash  (cost=257.00..257.00 rows=11000 width=78)
         ->  Seq Scan on posts  (cost=0.00..257.00 rows=11000 width=78)
(5 rows)

JOIN: Will adding indexes on columns in ON clause improve cost?

Explain JOIN: Quiz

• JOIN algorithms are not capable of using any indexes on the ON columns
• The best way to optimize JOINS is by adding filters on at least one table

 EXPLAIN
  SELECT posts.*, comments.*
   FROM posts LEFT JOIN comments
   ON posts.id = comments.post_id
   WHERE posts.id < 20;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=4.42..492.32 rows=95 width=150)
   ->  Index Scan using posts_id_idx on posts  (cost=0.29..8.65 rows=21 width=78)
         Index Cond: (id < 20)
   ->  Bitmap Heap Scan on comments  (cost=4.14..22.98 rows=5 width=72)
         Recheck Cond: (posts.id = post_id)
         ->  Bitmap Index Scan on comments_post_id_idx  (cost=0.00..4.14 rows=5 width=0)
               Index Cond: (posts.id = post_id)

4. Explain ORDER BY

SELECT * from comments ORDER BY updated_at LIMIT 10;

Can ORDER BY speed up using an index

CREATE INDEX ON comments (created_at);
SELECT * from comments ORDER BY created_at LIMIT 10;

Will the second be better than first?

Explain Order By: Quiz

Can ORDER BY take advantage of an index

EXPLAIN
  SELECT * from comments ORDER BY updated_at LIMIT 10;

                                 QUERY PLAN
----------------------------------------------------------------------------
 Limit  (cost=2337.48..2337.51 rows=10 width=72)
   ->  Sort  (cost=2337.48..2462.48 rows=50000 width=72)
         Sort Key: updated_at
         ->  Seq Scan on comments  (cost=0.00..1257.00 rows=50000 width=72)
(4 rows)

Yes

Explain Order By: Quiz

EXPLAIN
  SELECT * from comments ORDER BY created_at LIMIT 10;

                                  QUERY PLAN
--------------------------------------------------------------------------
 Limit  (cost=0.29..1.16 rows=10 width=72)
   ->  Index Scan using comments_created_at_idx on comments
        (cost=0.29..4332.16 rows=50000 width=72)
(2 rows)

/Explain

@h6165

Abhishek Yadav

Active in Chennai.py since Sep 2013

Talked before in Nov 2013

Co-organizer: Chennai.rb

ரூபீ ப்ரோக்ராமர