I just have encountered a strange use case with my Postgres. I run the following commands:

CREATE INDEX idx_index_value_and_text ON index_test ( value, text );

explain (analyze true, verbose true, buffers true) select * from index_test r where r.text='some value';

...where 'value' and 'text' are int and varchar columns from the previous setup. And I have the following reply from Postgres:

'Index Scan using idx_index_value_and_text on index_test r (cost=0.00..262.76 rows=1 width=610) (actual time=0.543..0.543 rows=0 loops=1)'

' Output: id, text, last_modified, value, n_text, n_last_modified, n_value, item_type'

' Index Cond: ((r.text)::text = 'some value'::text)'

' Buffers: shared hit=73'

'Total runtime: 0.568 ms'

What happens here? It looks like Postgres is doing Index Scan, though it cannot do it because in WHERE clause I use the SECOND column of the index. Could you explain this, please?

asked Apr 15 '13 at 11:23

Dmitry's gravatar image

Dmitry
26225


One Answer:

This is a so-called 'full index scan'. Consider this example:

Table size: 100MB Index size: 10MB

Your query doesn't select many rows (only one actually) so there are two options: -> do the Seq Scan on the 100MB table of -> read the complete index (all 10MB) to see which rows match, and then fetch those few rows from the table.

The second option can sometimes makes sense (in absence of a better index). Although it is using the index, it is not using the first power of indexing: finding data quickly with the B-tree. It's just using it as a smaller version of the table.

answered Apr 15 '13 at 11:48

Markus%20Winand's gravatar image

Markus Winand ♦♦
93651120

Thanks Markus! But can we really guess it from the explain output? I just created the correct index for that case (index on 'text' field), and now I see that the execution time become much lower, but the explanation of the query is exactly the same, except for the name of the index and numeric values such as buffers, time etc

(Apr 15 '13 at 13:27) Dmitry

@Dmitry True, we cannot tell that from a PostgreSQL execution plan alone, because PostgreSQL execution plans don't allow us to distinguish between access and filter predicates: http://use-the-index-luke.com/sql/explain-plan/postgresql/filter-predicates

(Apr 15 '13 at 13:45) Markus Winand ♦♦