From: Abhijit Menon-Sen <ams@oryx.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: switchover between index and sequential scans
Date: Thu, 3 Jul 2008 17:15:50 +0530
Message-ID: <20080703114550.GA8940@toroid.org> (raw)
In-Reply-To: <87hcb7b7ut.fsf@oxford.xeocode.com>
References: <20080703075900.GA5735@toroid.org>
<87hcb7b7ut.fsf@oxford.xeocode.com>
Hi Greg.
At 2008-07-03 11:05:46 +0100, stark@enterprisedb.com wrote:
>
> And increase the statistics target on header_fields and re-analyze?
Aha! Thanks for the tip. I just changed the default_statistics_target to
100 (from 10) and ANALYSEd header_fields and mailbox_messages, and now
it ALWAYS uses the index scan if I specify a LIMIT. That is,
select count(*) from header_fields where message in
(select message from mailbox_messages limit N)
always uses the index scan on header_fields_message_key, even when N is
equal to the number of rows in mailbox_messages (109410).
Aggregate (cost=30779.98..30779.99 rows=1 width=0) (actual time=175040.923..175040.926 rows=1 loops=1)
-> Nested Loop (cost=3279.73..30760.93 rows=7617 width=0) (actual time=2114.426..169137.088 rows=1771029 loops=1)
-> HashAggregate (cost=3279.73..3281.73 rows=200 width=4) (actual time=2076.662..2649.541 rows=109365 loops=1)
-> Limit (cost=0.00..1912.10 rows=109410 width=4) (actual time=0.029..1386.128 rows=109410 loops=1)
-> Seq Scan on mailbox_messages (cost=0.00..1912.10 rows=109410 width=4) (actual time=0.022..744.190 rows=109410 loops=1)
-> Index Scan using header_fields_message_key on header_fields (cost=0.00..136.92 rows=38 width=4) (actual time=0.678..1.416 rows=16 loops=109365)
Index Cond: (header_fields.message = "outer".message)
Total runtime: 175041.496 ms
Note the massive _under_estimation in the hash aggregate and the
nestloop. If I don't specify a limit, it'll use a seq scan again.
Very interesting.
-- ams
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: ams@oryx.com, stark@enterprisedb.com
Subject: Re: switchover between index and sequential scans
In-Reply-To: <20080703114550.GA8940@toroid.org>
* 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