pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSequential scan being used despite indexes
6+ messages / 4 participants
[nested] [flat]
* Sequential scan being used despite indexes
@ 2006-02-01 03:14 James Russell <internationalhobo@gmail.com>
2006-02-01 03:29 ` Re: Sequential scan being used despite indexes Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: James Russell @ 2006-02-01 03:14 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequential scan being used despite indexes
2006-02-01 03:14 Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
@ 2006-02-01 03:29 ` Joshua D. Drake <jd@commandprompt.com>
2006-02-01 03:58 ` Re: Sequential scan being used despite indexes Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 6+ messages in thread
From: Joshua D. Drake @ 2006-02-01 03:29 UTC (permalink / raw)
To: James Russell <internationalhobo@gmail.com>; +Cc: 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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequential scan being used despite indexes
2006-02-01 03:14 Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
2006-02-01 03:29 ` Re: Sequential scan being used despite indexes Joshua D. Drake <jd@commandprompt.com>
@ 2006-02-01 03:58 ` Michael Fuhr <mike@fuhr.org>
2006-02-01 04:33 ` Re: Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2006-02-01 03:58 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: James Russell <internationalhobo@gmail.com>; pgsql-performance
On Tue, Jan 31, 2006 at 07:29:51PM -0800, Joshua D. Drake wrote:
> > Any ideas?
>
> What does explain analyze say?
Also, have the tables been vacuumed and analyzed?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequential scan being used despite indexes
2006-02-01 03:14 Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
2006-02-01 03:29 ` Re: Sequential scan being used despite indexes Joshua D. Drake <jd@commandprompt.com>
2006-02-01 03:58 ` Re: Sequential scan being used despite indexes Michael Fuhr <mike@fuhr.org>
@ 2006-02-01 04:33 ` James Russell <internationalhobo@gmail.com>
2006-02-01 04:43 ` Re: Sequential scan being used despite indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-02-01 05:08 ` Re: Sequential scan being used despite indexes Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 6+ messages in thread
From: James Russell @ 2006-02-01 04:33 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
[Sorry, my last reply didn't go to the list]
Reading about this issue further in the FAQ, it seems that I should ensure
that Postgres has adequate and accurate information about the tables in
question by regularly running VACUUM ANALYZE, something I don't do
currently.
I disabled SeqScan as per the FAQ, and it indeed was a lot slower so
Postgres was making the right choice in this case.
Many thanks,
James
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequential scan being used despite indexes
2006-02-01 03:14 Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
2006-02-01 03:29 ` Re: Sequential scan being used despite indexes Joshua D. Drake <jd@commandprompt.com>
2006-02-01 03:58 ` Re: Sequential scan being used despite indexes Michael Fuhr <mike@fuhr.org>
2006-02-01 04:33 ` Re: Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
@ 2006-02-01 04:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 6+ messages in thread
From: Christopher Kings-Lynne @ 2006-02-01 04:43 UTC (permalink / raw)
To: James Russell <internationalhobo@gmail.com>; +Cc: Michael Fuhr <mike@fuhr.org>; Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
> Reading about this issue further in the FAQ, it seems that I should
> ensure that Postgres has adequate and accurate information about the
> tables in question by regularly running VACUUM ANALYZE, something I
> don't do currently.
Well then you'll get rubbish performance always in PostgreSQL...
I strongly suggest you run autovacuum if you don't really understand
PostgreSQL vacuuming/analyzing.
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequential scan being used despite indexes
2006-02-01 03:14 Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
2006-02-01 03:29 ` Re: Sequential scan being used despite indexes Joshua D. Drake <jd@commandprompt.com>
2006-02-01 03:58 ` Re: Sequential scan being used despite indexes Michael Fuhr <mike@fuhr.org>
2006-02-01 04:33 ` Re: Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
@ 2006-02-01 05:08 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2006-02-01 05:08 UTC (permalink / raw)
To: James Russell <internationalhobo@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On Wed, Feb 01, 2006 at 01:33:08PM +0900, James Russell wrote:
> Reading about this issue further in the FAQ, it seems that I should ensure
> that Postgres has adequate and accurate information about the tables in
> question by regularly running VACUUM ANALYZE, something I don't do
> currently.
Many people use a cron job (or the equivalent) to run VACUUM ANALYZE
at regular intervals; some also use the pg_autovacuum daemon, which
is a contrib module in 8.0 and earlier and part of the backend as of
8.1.
How often to vacuum/analyze depends on usage. Once per day is
commonly cited, but busy tables might need it more often than that.
Just recently somebody had a table that could have used vacuuming
every five minutes or less (all records were updated every 30
seconds); pg_autovacuum can be useful in such cases.
> I disabled SeqScan as per the FAQ, and it indeed was a lot slower so
> Postgres was making the right choice in this case.
The planner might be making the right choice given the statistics
it has, but it's possible that better statistics would lead to a
different plan, perhaps one where an index scan would be faster.
What happens if you run VACUUM ANALYZE on all the tables, then run
the query again with EXPLAIN ANALYZE?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-02-01 05:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-01 03:14 Sequential scan being used despite indexes James Russell <internationalhobo@gmail.com>
2006-02-01 03:29 ` Joshua D. Drake <jd@commandprompt.com>
2006-02-01 03:58 ` Michael Fuhr <mike@fuhr.org>
2006-02-01 04:33 ` James Russell <internationalhobo@gmail.com>
2006-02-01 04:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-02-01 05:08 ` Michael Fuhr <mike@fuhr.org>
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