(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.