TL;DR: When an "EXPLAIN (BUFFERS)" shows an "Index Scan", it does not show the number of pages needed to be read from the index. Is it just omitting that number, or does it actually not read pages (and I misunderstand how the index works)?
We have a large read-only table, that looks like this:
database=> \d my_table
Table "my_table"
Column | Type | Modifiers
-----------------------+------------------+-----------
id | integer |
date | date |
country_id | smallint |
...other columns...
Indexes:
"my_table_id_date_idx" btree (id, date)
and a typical query against this table has an EXPLAIN like this...
database=> EXPLAIN (BUFFERS, ANALYZE) SELECT id, date, country_id FROM my_table WHERE id = 50 AND date BETWEEN '2015-04-01' AND '2015-04-07';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on my_table (cost=448.40..70400.85 rows=18810 width=10) (actual time=9.011..1447.817 rows=10224 loops=1)
Recheck Cond: ((id = 50) AND (date >= '2015-04-01'::date) AND (date <= '2015-04-07'::date))
Buffers: shared hit=232 read=9994
-> Bitmap Index Scan on my_table_id_date_idx (cost=0.00..443.69 rows=18810 width=0) (actual time=6.467..6.467 rows=10224 loops=1)
Index Cond: ((id = 50) AND (date >= '2015-04-01'::date) AND (date <= '2015-04-07'::date))
Buffers: shared hit=2 read=30
Total runtime: 1450.175 ms
(7 rows)
Here is how I'm analyzing this:
- The query will perform a "Bitmap Index Scan", which requires it to read 32 pages. Up until now, I've assumed these 32 pages contain the data for the index itself. In the end, it creates a list of pages to read.
- The query then performs the "Bitmap Heap Scan", which will order the pages to read, then read them. In the end, this reads over 10k pages.
We've recently changed this (read-only) table to CLUSTER on its index, which has dramatically reduced the pages needed to be read. Now an explain looks like this...
database=> EXPLAIN (BUFFERS, ANALYZE) SELECT id, date, country_id FROM my_table WHERE id = 50 AND date BETWEEN '2015-04-01' AND '2015-04-07';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using my_table_id_date_idx on my_table (cost=0.57..36703.31 rows=20594 width=10) (actual time=0.029..4.830 rows=10224 loops=1)
Index Cond: ((id = 50) AND (date >= '2015-04-01'::date) AND (date <= '2015-04-07'::date))
Buffers: shared hit=160
Total runtime: 6.658 ms
(4 rows)
In this case, it's doing an Index Scan. As best as I know, the difference between an Index scan and a Bitmap Index/Heap Scan is that the former will read pages in the order defined by the index, while the latter will create a bitmap of pages to read (possibly from multiple indexes), order the results, and read them in order. It seems to me that in both cases, one would need to read the pages of the index to actually determine the resulting data pages to read.
However, in the "Index Scan" the line for "Buffers" has 160 pages, which based on my other tests is the number of pages of actual data, and doesn't include the 32 pages I saw above in the Bitmap Index Scan for the index itself.
database=> set enable_indexscan to false;
SET
database=> EXPLAIN (BUFFERS, ANALYZE) SELECT id, date, country_id FROM my_table WHERE id = 50 AND date BETWEEN '2015-04-01' AND '2015-04-07';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on my_table (cost=491.14..76781.03 rows=20594 width=10) (actual time=1.003..3.873 rows=10224 loops=1)
Recheck Cond: ((id = 50) AND (date >= '2015-04-01'::date) AND (date <= '2015-04-07'::date))
Buffers: shared hit=160
-> Bitmap Index Scan on my_table_id_date_idx (cost=0.00..486.00 rows=20594 width=0) (actual time=0.981..0.981 rows=10224 loops=1)
Index Cond: ((id = 50) AND (date >= '2015-04-01'::date) AND (date <= '2015-04-07'::date))
Buffers: shared hit=32
Total runtime: 5.595 ms
(7 rows)
Does this imply that the index scan does not actually read from the index? How does it know what pages to read, then? Is the number of index pages read just omitted in the Index Scan explain output? Or perhaps my understanding of what that value in the Bitmap Index Scan represents is incorrect.