Message-ID: <4602D680.2020106@modgraph-usa.com>
Date: Thu, 22 Mar 2007 11:18:24 -0800
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Steve Atkins <steve@blighty.com>,  pgsql-performance@postgresql.org
Subject: Re: Performance of count(*)
References: <Pine.LNX.4.62.0703221149210.13747@wr-linux02>
	<200703221208.19358.albert@sedifa.com>
	<Pine.LNX.4.64.0703221323590.1503@ismoli.solenovo.jns>
	<20070322141807.GE11402@mathom.us>
	<46029DE3.9030505@modgraph-usa.com>
	<4602A15B.7000908@wildenhain.de>
	<4602BB19.9050306@modgraph-usa.com>
	<228F06CD-3642-4A42-813A-648285A3BDBC@blighty.com>
	<4602C921.3070303@modgraph-usa.com>
	<20588.1174588646@sss.pgh.pa.us>
In-Reply-To: <20588.1174588646@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> "Craig A. James" <cjames@modgraph-usa.com> writes:
>> Steve Atkins wrote:
>>> As long as you're ordering by some row in the table then you can do that in
>>> straight SQL.
>>>
>>> select a, b, ts from foo where (stuff) and foo > X order by foo limit 10
>>>
>>> Then, record the last value of foo you read, and plug it in as X the next
>>> time around.
> 
>> We've been over this before in this forum: It doesn't work as advertised.
>> Look for postings by me regarding the fact that there is no way to tell
>> the optimizer the cost of executing a function.  There's one, for example,
>> on Oct 18, 2006.
> 
> You mean
> http://archives.postgresql.org/pgsql-performance/2006-10/msg00283.php
> ?  I don't see anything there that bears on Steve's suggestion.
> (The complaint is obsolete as of CVS HEAD anyway.)

Mea culpa, it's October 8, not October 18:

   http://archives.postgresql.org/pgsql-performance/2006-10/msg00143.php

The relevant part is this:

"My example, discussed previously in this forum, is a classic.  I have a VERY expensive function (it's in the class of NP-complete problems, so there is no faster way to do it).  There is no circumstance when my function should be used as a filter, and no circumstance when it should be done before a join.  But PG has no way of knowing the cost of a function, and so the optimizer assigns the same cost to every function.  Big disaster.

"The result?  I can't use my function in any WHERE clause that involves any other conditions or joins.  Only by itself.  PG will occasionally decide to use my function as a filter instead of doing the join or the other WHERE conditions first, and I'm dead.

"The interesting thing is that PG works pretty well for me on big tables -- it does the join first, then applies my expensive functions.  But with a SMALL (like 50K rows) table, it applies my function first, then does the join.  A search that completes in 1 second on a 5,000,000 row database can take a minute or more on a 50,000 row database."

Craig