From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Sabino Mullane <greg@turnstep.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge: Arbitrary Cross-tab
Date: Thu, 19 Aug 2004 23:25:21 -0400
Message-ID: <15579.1092972321@sss.pgh.pa.us> (raw)
In-Reply-To: <a1aa78544af9cfda7c505876591e95dc@biglumber.com>
References: <a1aa78544af9cfda7c505876591e95dc@biglumber.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, greg@turnstep.com
Subject: Re: SQL Challenge: Arbitrary Cross-tab
In-Reply-To: <15579.1092972321@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox