Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TOtD0-0004PG-E5
	for pgsql-performance@postgresql.org; Thu, 18 Oct 2012 16:44:34 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TOtCv-0005gx-KH
	for pgsql-performance@postgresql.org; Thu, 18 Oct 2012 16:44:33 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q9IGiRZI000103;
	Thu, 18 Oct 2012 12:44:27 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thom Brown <thom@linux.com>
cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Unused index influencing sequential scan plan
In-reply-to: 
 <CAA-aLv4aukjF=03XNV4o2UKWqweGd+XaHK0y1aTsgGGXmzVcDA@mail.gmail.com>
References: 
 <CAA-aLv4aukjF=03XNV4o2UKWqweGd+XaHK0y1aTsgGGXmzVcDA@mail.gmail.com>
Comments: In-reply-to Thom Brown <thom@linux.com>
	message dated "Thu, 18 Oct 2012 17:11:51 +0100"
Date: Thu, 18 Oct 2012 12:44:27 -0400
Message-ID: <102.1350578667@sss.pgh.pa.us>
X-Pg-Spam-Score: -2.3 (--)
X-Archive-Number: 201210/247
X-Sequence-Number: 48206

Thom Brown <thom@linux.com> writes:
> I've created a test table containing 21 million random dates and
> times, but I get wildly different results when I introduce a
> functional index then ANALYSE again, even though it doesn't use the
> index:

As Peter said, the existence of the index causes ANALYZE to gather stats
about the expression, which will affect rowcount estimates whether or
not the planner chooses to use the index.

> And as a side note, how come it's impossible to get the planner to use
> an index-only scan to satisfy the query (disabling sequential and
> regular index scans)?

Implementation restriction - we don't yet have a way to match index-only
scans to expressions.

			regards, tom lane