DB related

$ User.where(id: 1).joins(:articles).explain


EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id" WHERE "users"."id" = 1
                                  QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..37.24 rows=8 width=0)
   Join Filter: (articles.user_id = users.id)
   ->  Index Scan using users_pkey on users  (cost=0.00..8.27 rows=1 width=4)
         Index Cond: (id = 1)
   ->  Seq Scan on articles  (cost=0.00..28.88 rows=8 width=4)
         Filter: (articles.user_id = 1)
(6 rows)

Seq Scan (no index, slow)

scans the entire relation (table).

Index Scan ( index, but access a page several times)

performs a B-tree traversal, walks through the leaf nodes to find all matching entries, and fetches the corresponding table data.

Index Only Scan (index, no access table data)

performs a B-tree traversal and walks through the leaf nodes to find all matching entries. There is no table access needed.

Bitmap Index Scan / Bitmap Heap Scan / Recheck Cond (index, access page one time only base on bitmap)

1. A plain index-scan fetches one tuple-pointer at a time from the index, and immediately visits that tuple in the table.

2. A bitmap scan fetches all the tuple-pointers from the index in one go, sorts them using an in-memory "bitmap" data structure, and then visits the table tuples in physical tuple-location order.

3. Instead of accessing the Heap right after fetching a row from the index, the Bitmap Index Scan completes the index lookup first, keeping track of all rows that might be interesting (in a, you guess it, bitmap). The Bitmap Heap Scan than accesses all the interesting heap pages sequentially.

The Bitmap Index+Heap Scan operations are an optimization of the regular Index Scan

http://guides.rubyonrails.org/active_record_querying.html

https://use-the-index-luke.com/sql/explain-plan/postgresql/operations

cost=0.00..37.24

The 1st number = the cost to return the first row from that node.

The 2nd number = the cost to return all of the rows for that node.