From: James Russell <internationalhobo@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Sequential scan being used despite indexes
Date: Wed, 1 Feb 2006 12:14:47 +0900
Message-ID: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: internationalhobo@gmail.com
Subject: Re: Sequential scan being used despite indexes
In-Reply-To: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox