Sequential scan being used despite indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi there,

I'm running a simple query with 2 inner joins (say A, B and C). Each of the join columns has indexes. If I run queries that join just A and B, or just B and C, postgres uses indexes. But if I run "A join B join C" then the "B join C" part starts using a sequential scan and I can't figure out why.

Here's the query, which basically retrieves all meta-data for all messages in a given forum. The relationship is pretty simple. Forums contain threads, which contain messages, which each have associated meta-data:

SELECT message.message_id, message_meta_data.value
FROM thread
    JOIN message USING (thread_id)
    JOIN message_meta_data ON (message.message_id=message_meta_data.message_id)
WHERE thread.forum_id=123;

Explaining:
Hash Join  (cost=337.93..1267.54 rows=180 width=35)
Hash Cond: ("outer".message_id = "inner".message_id)
->  Seq Scan on message_meta_data  (cost=0.00..739.19 rows=37719 width=30)
->  Hash  (cost=337.79..337.79 rows=57 width=13)
    ->  Nested Loop  (cost=0.00..337.79 rows=57 width=13)
          ->  Index Scan using thread_forum_id_idx on thread (cost=0.00..41.61 rows=13 width=4)
                 Index Cond: (forum_id = 6)
          ->  Index Scan using message_thread_id_idx on message (cost=0.00..22.72 rows=5 width=17)
                 Index Cond: ("outer".thread_id = message.thread_id)

As you can see, the message and message_meta_data tables use a Seq Scan. The only way I can think of forcing it to use the Index Scan in all cases would be to use two separate nested queries: The outer query would retrieve the list of messages in the forum, and the inner query would retrieve the list of metadata for an individual message. Obviously I want to avoid having to do that if possible.

Any ideas?

Many thanks if you can help.

James

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux