So far, you have seen when an index is used and when it is not. In addition to this, bitmap scans have been discussed.
However, there is more to indexing. The following two examples will only differ slightly although the performance difference might be fairly large. Here is the first query:
test=# EXPLAIN SELECT * FROM t_test WHERE id = 34234;
QUERY PLAN ---------------------------------------------------------------- Index Scan using idx_id on t_test (cost=0.43..8.45 rows=1 width=9) Index Cond: (id = 34234)
There is nothing unusual here. PostgreSQL uses an index to find a single row. What happens if only a single column is selected?
test=# EXPLAIN SELECT id FROM t_test WHERE id = 34234; QUERY PLAN ---------------------------------------------------------------- ...
