3

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

  • We can deduce from the query plan that process_data is supposed to be the "big table". But don't make us guess, just say so. Also, did you only want to know why the particular index is used or are you looking for an optimized solution? I think I have an idea for a faster approach. In this case, start a new question and provide table definitions and the number of rows in each table. You can always link to this question for context. – Erwin Brandstetter Nov 8 '14 at 2:54
  • @ErwinBrandstetter I really want to optimize this query and also learn about Postgres tuning so I can ask less and even help more. What would be the name of this new question? I'm kinda new to StackExchange. – Ivan De Sousa Paz Nov 11 '14 at 18:39
  • You'll have to find a matching title yourself. Maybe something like "Optimize partial index usage for query with GROUP BY and LIMIT". – Erwin Brandstetter Nov 12 '14 at 1:23
7

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/

  • Both presented indexes have the same WHERE condition, but the other index is smaller, provides the fk column and could allow an index-only scan (depends). Their is not a single clue why Postgres should use the inferior index. The question is still open. We might not have the full picture in the question. – Erwin Brandstetter Nov 8 '14 at 3:02
  • So what kind of information can I provide to give you a better picture? As I'm trying hard to be a better DBA, I really want full comprehension of it. – Ivan De Sousa Paz Nov 10 '14 at 14:39
  • @IvanDeSousaPaz: Check the information for the tag [postgresql performance]. Plus cardinalities (number of rows) or your tables, and which parts of the query can change or not. Put it all in a new question. You can always add more information to this one as well, but don't change the nature of the question after answers have been given. – Erwin Brandstetter Nov 12 '14 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.