X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 55A089DC85C for ; Tue, 31 Jan 2006 23:14:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32117-01 for ; Tue, 31 Jan 2006 23:14:50 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from uproxy.gmail.com (uproxy.gmail.com [66.249.92.196]) by postgresql.org (Postfix) with ESMTP id 938759DC827 for ; Tue, 31 Jan 2006 23:14:43 -0400 (AST) Received: by uproxy.gmail.com with SMTP id s2so25325uge for ; Tue, 31 Jan 2006 19:14:47 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=l5bmq9Vly9sk2g1StkNndxE0/AnZi8A7WgRA5E+na1e1wwsboitCA+udYXQwN7g5lJMJKCnZAeI7rNcX7EZ8PlkoBcQi74vcJ9hjOJb0BvFC79oCl02DkJXVIYN0yOktoW54brdkMUhIdWdUqvnJjTmu+OxI//zEpI8nu9cdbE8= Received: by 10.48.143.5 with SMTP id q5mr1530413nfd; Tue, 31 Jan 2006 19:14:47 -0800 (PST) Received: by 10.49.59.5 with HTTP; Tue, 31 Jan 2006 19:14:47 -0800 (PST) Message-ID: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com> Date: Wed, 1 Feb 2006 12:14:47 +0900 From: James Russell To: pgsql-performance@postgresql.org Subject: Sequential scan being used despite indexes MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_301_27951016.1138763687607" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.333 required=5 tests=[HTML_MESSAGE=0.001, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 1.333 X-Spam-Level: * X-Archive-Number: 200601/506 X-Sequence-Number: 16984 ------=_Part_301_27951016.1138763687607 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3Dmessage_meta_data.message_id) WHERE thread.forum_id=3D123; Explaining: Hash Join (cost=3D337.93..1267.54 rows=3D180 width=3D35) Hash Cond: ("outer".message_id =3D "inner".message_id) -> Seq Scan on message_meta_data (cost=3D0.00..739.19 rows=3D37719 width= =3D30) -> Hash (cost=3D337.79..337.79 rows=3D57 width=3D13) -> Nested Loop (cost=3D0.00..337.79 rows=3D57 width=3D13) -> Index Scan using thread_forum_id_idx on thread (cost=3D 0.00..41.61 rows=3D13 width=3D4) Index Cond: (forum_id =3D 6) -> Index Scan using message_thread_id_idx on message (cost=3D 0.00..22.72 rows=3D5 width=3D17) Index Cond: ("outer".thread_id =3D message.thread_id) As you can see, the message and message_meta_data tables use a Seq Scan. Th= e 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 ------=_Part_301_27951016.1138763687607 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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&= quot; 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=3Dmessage_= meta_data.message_id)
WHERE thread.forum_id=3D123;

Explaining:
Hash Join  (cost=3D337.93..1267.54 rows=3D180 width=3D35)
Hash Cond: ("outer".message_id =3D "inner".message_id)<= br> ->  Seq Scan on message_meta_data  (cost=3D0.00..739.19 rows= =3D37719 width=3D30)
->  Hash  (cost=3D337.79..337.79 rows=3D57 width=3D13)
    ->  Nested Loop  (cost=3D0.00..337.79 rows= =3D57 width=3D13)
          ->  Index Scan using thread_forum_id_idx on thread (cost=3D0.00..41.61 rows=3D13 width=3D4)
            &nb= sp;    Index Cond: (forum_id =3D 6)
          ->  Index Scan using message_thread_id_idx on message (cost=3D0.00..22.72 rows=3D5 width=3D17)
            &nb= sp;    Index Cond: ("outer".thread_id =3D 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
------=_Part_301_27951016.1138763687607--