Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8F208650E8E for ; Thu, 3 Jul 2008 08:45:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 91803-08 for ; Thu, 3 Jul 2008 08:45:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from fugue.toroid.org (fugue.toroid.org [85.10.196.113]) by postgresql.org (Postfix) with ESMTP id 19A95650E82 for ; Thu, 3 Jul 2008 08:45:53 -0300 (ADT) Received: from penne.toroid.org (penne-vpn [10.8.0.6]) by fugue.toroid.org (Postfix) with ESMTP id A55D4558349; Thu, 3 Jul 2008 13:45:50 +0200 (CEST) Received: by penne.toroid.org (Postfix, from userid 1000) id 6C688ADC305; Thu, 3 Jul 2008 17:15:50 +0530 (IST) Date: Thu, 3 Jul 2008 17:15:50 +0530 From: Abhijit Menon-Sen To: Gregory Stark Cc: pgsql-performance@postgresql.org Subject: Re: switchover between index and sequential scans Message-ID: <20080703114550.GA8940@toroid.org> References: <20080703075900.GA5735@toroid.org> <87hcb7b7ut.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87hcb7b7ut.fsf@oxford.xeocode.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/39 X-Sequence-Number: 30639 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