Firing up 8.2.1 I notice that sub-items in a view are optimized out if
they aren't being selected.

For example, "select item1, item2 from a_view" would take just as long
as "select item1, item2, item3, item4 from a_view"

This isn't usually a problem, but if item3 or item4 are significantly
more complex (and slow) than item1 and item2 this is a big problem. In
8.1 and previous versions of postgresql this wouldn't happen. Is there
some setting that can be set to re-enable this feature?

Attached is a small sql script that shows the problem. When run on 8.2
or 8.2.1 it takes twice as long as when run on 8.1 because of this
un-feature. This can be run on a completely fresh, blank database and it
will create all of the languages, tables, etc that it needs. It will
also clean up everything afterwards. The funciton "slow_function" is for
illistration purposes only, and is made only to take time.

Thanks in advance for any help,

Nathan Bell
IT Engineer
Action Target, Inc.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Nathan Bell <nathanb@actarg.com> writes:
> Firing up 8.2.1 I notice that sub-items in a view are optimized out if
> they aren't being selected.

You mean "not optimized out", I suppose. Declare your function as
non-volatile if you want the optimizer to assume it's OK to discard.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Yeah, I saw the "not optimized out" typo as soon as I hit send.

What if the item that is taking a long time isn't a function, but rather
a sub-select?
Can I set the sub-select to stable, or perhaps set the entire view to
non-volatile to achieve the same result?
If not, can I set the sub-select to a different non-volatile view or do
I need to create a non-volatile function that returns the result?

Thanks for the help.

Tom Lane wrote:

>Nathan Bell <nathanb@actarg.com> writes:
>
>
>>Firing up 8.2.1 I notice that sub-items in a view are optimized out if
>>they aren't being selected.
>>
>>
>
>You mean "not optimized out", I suppose. Declare your function as
>non-volatile if you want the optimizer to assume it's OK to discard.
>
> regards, tom lane
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Nathan Bell <nathanb@actarg.com> writes:
> What if the item that is taking a long time isn't a function, but rather
> a sub-select?

The point is that the view won't be flattened if there are nonvolatile
functions in its SELECT list.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

I wrote:
> The point is that the view won't be flattened if there are nonvolatile
> functions in its SELECT list.

Sheesh ... s/nonvolatile/volatile/ of course ... this thread seems
afflicted with getting-it-backward disease :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Tue, Jan 16, 2007 at 02:55:08PM -0700, Nathan Bell wrote:
> Yeah, I saw the "not optimized out" typo as soon as I hit send.
>
> What if the item that is taking a long time isn't a function, but rather
> a sub-select?

The planner should be able to see that the item is non-volatile itself.
It only needs to be told for functions because it can't see into them.

> Can I set the sub-select to stable, or perhaps set the entire view to
> non-volatile to achieve the same result?

No.

> If not, can I set the sub-select to a different non-volatile view or do
> I need to create a non-volatile function that returns the result?

You could create a function that does the job, but that's generally not
necessary.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFrUwHIB7bNG8LQkwRAuRuAJ9pnh6IIM99M+PPcVYdZW GNyYCxCgCfYsMN
tpNCFoTWOYJErv2cEPfNDqU=
=Xz3d
-----END PGP SIGNATURE-----