A query is running much slower than you expected. You suspect an index is being ignored or you are missing an index on a particular column.
The EXPLAIN command displays the query plan that Postgres uses. It shows the decisions it makes and the costs it calculated that led to those decisions.
richardh=> EXPLAIN SELECT co_name, dy_type,dy_notes FROM companies JOIN diary ON co_id=dy_company; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..25.81 rows=30 width=44) -> Seq Scan on diary (cost=0.00..1.03 rows=3 width=28) -> Index Scan using companies_co_id_key on companies (cost=0.00..8.14 rows=10 width=16) EXPLAIN |
The costs given are relative to each other - no absolute meaning. The rows are an estimate on the number of rows being returned and the width represents the amount of data involved per record. In the (trivial) example above, Postgres performs a sequential scan of the diary table and then uses the index on companies.co_id to perform the join.
Complex queries produce complex query plans - pay careful attention to the rows part. If Postgres thinks it will fetch 1000 rows from a 1500 row table then it will ignore the index and use a Seq Scan instead. If it estimates the rows wrong it will make the wrong decision.