Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TOtKW-0000Tj-0D for pgsql-performance@postgresql.org; Thu, 18 Oct 2012 16:52:20 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TOtKR-0005qI-CZ for pgsql-performance@postgresql.org; Thu, 18 Oct 2012 16:52:19 +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 q9IGqETT000294; Thu, 18 Oct 2012 12:52:14 -0400 (EDT) From: Tom Lane To: Thom Brown cc: pgsql-performance Subject: Re: Unused index influencing sequential scan plan In-reply-to: References: <102.1350578667@sss.pgh.pa.us> Comments: In-reply-to Thom Brown message dated "Thu, 18 Oct 2012 17:47:42 +0100" Date: Thu, 18 Oct 2012 12:52:13 -0400 Message-ID: <293.1350579133@sss.pgh.pa.us> X-Pg-Spam-Score: -2.3 (--) X-Archive-Number: 201210/250 X-Sequence-Number: 48209 Thom Brown writes: > On 18 October 2012 17:44, Tom Lane wrote: >> Thom Brown writes: >>> 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. > Ah, I suspected it might be, but couldn't find notes on what scenarios > it's yet to be able to work in. Thanks. I forgot to mention that there is a klugy workaround: add the required variable(s) as extra index columns. That is, create index i on t (foo(x), x); The planner isn't terribly bright about this, but it will use that index for a query that only requires foo(x), and it won't re-evaluate foo() (though I think it will cost the plan on the assumption it does :-(). regards, tom lane