Re: Index not being used

On Tue, 15 Jun 2004 13:06:40 -0700, Shane Wegner
<shane-keyword-pgsql.a1e0d9@cm.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