Why postgres is using an odd index with no relation to the query

Question

(Debian 7, Postgres 9.3, dedicated machine with huge cache)

I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two and in the explain query Postgres is using an odd index not at all related to the query stuff like this:

Query:

select l.name,
       count(1) as quantity
from process_data pd
     join process_location l on pd.fk_location = l.id_process_location
where pd.active and pd.fk_status = 1
group by l.name
order by l.name
limit 1000

The explain query give me this:

+----------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                  QUERY PLAN                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Limit  (cost=166513.62..166513.88 rows=107 width=33)                                                                                         |
|   ->  Sort  (cost=166513.62..166513.88 rows=107 width=33)                                                                                    |
|         Sort Key: s.name                                                                                                                     |
|         ->  HashAggregate  (cost=166508.94..166510.01 rows=107 width=33)                                                                     |
|               ->  Hash Join  (cost=4.84..165278.56 rows=246076 width=33)                                                                     |
|                     Hash Cond: (d.fk_location = s.id_process_location)                                                                       |
|                     ->  Index Scan using idx_process_data_last_execution_start on process_data d  (cost=0.43..161890.61 rows=246076 width=8) |
|                     ->  Hash  (cost=3.07..3.07 rows=107 width=41)                                                                            |
|                           ->  Seq Scan on process_location s  (cost=0.00..3.07 rows=107 width=41)                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------+

As we can see, the query is using the index idx_process_data_last_execution_start, wich is:

"idx_process_data_last_execution_start" btree (priority, last_execution_start) WHERE fk_status = 1 AND active

None of its columns are mentioned in the query, so the question is: Why it's being used and how it's being helpful?

The second question is, why it's not using this index I created:

"idx_process_data_fk_location_active_status_1" btree (fk_location) WHERE active AND fk_status = 1

That would make much more sense and it's even smaller in size. The odd index is 41mb long and the second one is 30mb long.

I'm trying hard to understand how indexes work.

Answer 1

none of the columns are mentioned

Yes they are, right here:

where pd.active and pd.fk_status = 1

that matches the condition on the index and thus the index can be used to support the counting of the rows. Reading through all the rows in the index should be faster than doing a seq scan on the process_data table.

Why it's not using the other index I cannot tell. Maybe because the difference in size doesn't really matter for expected number of rows (246076)

Btw: there is absolutely no difference in performance between count(1) and count(*)

A very good site to understand how indexes work is "Use the index, Luke"
http://use-the-index-luke.com/