Skip site navigation (1) Skip section navigation (2)

Re: Sequential scan being used despite indexes

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: James Russell <internationalhobo(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Sequential scan being used despite indexes
Date: 2006-02-01 03:29:51
Message-ID: 43E02B2F.9070202@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-performance
>
> 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/


In response to

Responses

pgsql-performance by date

Next:From: Michael FuhrDate: 2006-02-01 03:58:04
Subject: Re: Sequential scan being used despite indexes
Previous:From: James RussellDate: 2006-02-01 03:14:47
Subject: Sequential scan being used despite indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group