Re: Index not being used

On Tue, 15 Jun 2004 13:06:40 -0700, Shane Wegner
<shane-keyword-pgsql(dot)a1e0d9(at)cm(dot)nu> wrote:
>The index I created reads:
>create index books_idx1 on books(publisher_id,place_id,illustrator_id,
>edition_id,type_id,category_id,binding_id,id);

This index is useless, drop it. Is there an index on books(id)?

>The other ids in the joining tables are all serial values
>and are primary keys so are indexed automatically.
>
>explain analyze output: [lots of seq scans and hash joins]

Try

EXPLAIN ANALYSE
SELECT *
FROM orders_and_books AS o
INNER JOIN books AS b ON o.book_id = b.id
WHERE o.order_id = 753;

This should give a nested loop using primary key index scans on both
tables. Then add

LEFT JOIN publishers ON publisher_id=publishers.id
LEFT JOIN places ON place_id=places.id
...

one by one until the plan changes to hash joins again and show us the
results.

Servus
Manfred