To: "Greg Sabino Mullane" <greg@turnstep.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge: Arbitrary Cross-tab 
In-reply-to: <a1aa78544af9cfda7c505876591e95dc@biglumber.com> 
References: <a1aa78544af9cfda7c505876591e95dc@biglumber.com>
Comments: In-reply-to "Greg Sabino Mullane" <greg@turnstep.com>
	message dated "Fri, 20 Aug 2004 02:16:50 -0000"
Date: Thu, 19 Aug 2004 23:25:21 -0400
Message-ID: <15579.1092972321@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Greg Sabino Mullane" <greg@turnstep.com> writes:
>> Nope; Gaetano's right, you cannot assume that.  It's entirely possible
>> for the planner to choose different plans depending on the OFFSET.
>> (Maybe not very likely, with such small offsets, but could happen.)
 
> Interesting. I realized that there was a finite chance of different
> plans being chosen, but it seemed neigh-impossible since there is
> no WHERE clause and the offsets only vary from 0-7. What sort of
> different plans would it choose, out of curiosity?

For the particular trivial case you were showing (no WHERE, no GROUP BY,
no ORDER BY, no DISTINCT, no nada) it's probably true that only a
seqscan plan would ever be chosen.  I was worrying that people would
take this example and try to add "just that other little thing" to it
and get burnt.

> Are there any particular cases where the same plan is guaranteed to be
> used?

No, I'd never care to make such a guarantee.  The planner is entirely
driven by cost estimates.  Even if I could say something definitive
about the behavior with the default cost parameters, it wouldn't
necessary hold up when someone had taken an axe to random_page_cost
or something like that.  (It's not impossible that the thing would pick
an indexscan plan for even this trivial case, were you to set
random_page_cost below 1.)

			regards, tom lane