Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id EB6F4635123 for ; Thu, 5 Mar 2009 22:33:10 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 48793-04 for ; Thu, 5 Mar 2009 22:33:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.180]) by mail.postgresql.org (Postfix) with ESMTP id 759486350BF for ; Thu, 5 Mar 2009 22:33:09 -0400 (AST) Received: by el-out-1112.google.com with SMTP id y26so238052ele.10 for ; Thu, 05 Mar 2009 18:33:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=GBBBrdRuvZpfTcRPoruZ+oXpy/VdFTBU3QH9WATYLQo=; b=vU7BgAQEC5jP2NVEgMZQV23qsYACe1juPjnaxEQyeDKZEXQCpI69a5u/3fUHveR3nk a7mumpS8MX+BI50Dq5oupLB6ioOHUblj7563MQjW2SNR0WiZm5tRtTG7pxOUEgg0vVsa ziTjOueCsbrJohDEXYYIlWh2iZT5HR7YsIzrU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=dYEjtX3yNdOUrignzafAU07rK8htdtOGJZ7fIIwMH6U55w4UppePycazjmbNeSOR1/ uqdzaZEETPqafm/lcVoihPQyxq+kv872MyuSNZ5g8aLuNTs475lg8ZX5RavBGvd05uxN YDC0pLRc/AuaiMU/mcLEK+ZvKU2dnuBpyZ4Cc= MIME-Version: 1.0 Received: by 10.220.96.84 with SMTP id g20mr649531vcn.98.1236306787556; Thu, 05 Mar 2009 18:33:07 -0800 (PST) In-Reply-To: <8053.1236277851@sss.pgh.pa.us> References: <78a8986f0903050756i7b1f6c27teb4c67d4578f11ae@mail.gmail.com> <8053.1236277851@sss.pgh.pa.us> Date: Thu, 5 Mar 2009 21:33:06 -0500 Message-ID: <603c8f070903051833j29eba13tb3216610aa187a5@mail.gmail.com> Subject: Re: Index scan plan estimates way off. From: Robert Haas To: Tom Lane Cc: Jonathan Hseu , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/58 X-Sequence-Number: 32980 On Thu, Mar 5, 2009 at 1:30 PM, Tom Lane wrote: > Jonathan Hseu writes: >> =A0Sort =A0(cost=3D11684028.44..11761274.94 rows=3D30898601 width=3D40) >> =A0 =A0Sort Key: "time" >> =A0 =A0-> =A0Bitmap Heap Scan on ticks =A0(cost=3D715657.57..6995196.08 = rows=3D30898601 >> width=3D40) >> =A0 =A0 =A0 =A0 =A0Recheck Cond: (contract_id =3D 1) >> =A0 =A0 =A0 =A0 =A0-> =A0Bitmap Index Scan on contract_id_time_idx >> (cost=3D0.00..707932.92 rows=3D30898601 width=3D0) >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (contract_id =3D 1) >> (6 rows) > >> This plan doesn't complete in a reasonable amount of time. =A0I end up h= aving >> to kill the query after it's been running for over an hour. > > The bitmap scan should be at least as efficient as the plain indexscan, > so I suppose the problem is that the sort is slow. =A0What's the datatype > of "time"? =A0Can this machine actually support 256MB+ work_mem, or is th= at > likely to be driving it into swapping? > > You might learn more from enabling trace_sort and watching the > postmaster log entries it generates. =A0On the whole I think the planner > isn't making a stupid choice here: sorting a large number of rows > usually *is* preferable to making an indexscan over them, unless the > table is remarkably close to being in physical order for the index. It seems like this is only likely to be true if most of the data needs to be read from a magnetic disk, so that many seeks are involved. That might not be the case here, since the machine has an awful lot of RAM. ...Robert