Notes

Work In Progress

Database table is a heap. Rows stored unordered.

Full Scan (Seq Scan) conceptially looks like this in Ruby:

for block in table do
  read block
  for row in block do
    if filter_cond(row)
      output(row)
    end
  end
end

Index is redundant database objects to speed up retriving rows based on certiain conditions.
Since index is redundant database objects, INSERT/UPDATE will need to update the index.
Index gives a shortcut to access the data.
Index is list of pointers to blocks to rows satisfy filter conditions.

Postgres has physical operations to get rows by index: index scan and bitmap heap scan.

Selectivity

High = Many results from query. Slower to search.
Low = Little results from query. Faster to search.

B-Tree.

Compound/Composite index. Index on (a, b, c) can be used to search
a or ab or abc. But not for searching b or bc or c. So order is important!

Index-only scan means all the rows we want to get are in the index, no need to
touch the table, so called index-only scan.

Partial index. Index with filter condition.