From: Joshua D. Drake <jd@commandprompt.com>
To: James Russell <internationalhobo@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential scan being used despite indexes
Date: Tue, 31 Jan 2006 19:29:51 -0800
Message-ID: <43E02B2F.9070202@commandprompt.com> (raw)
In-Reply-To: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com>
References: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com>
>
> 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?
What does explain analyze say?
Joshua D. Drake
>
> Many thanks if you can help.
>
> James
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
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: jd@commandprompt.com, internationalhobo@gmail.com
Subject: Re: Sequential scan being used despite indexes
In-Reply-To: <43E02B2F.9070202@commandprompt.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