[postgis-devel] reuse/caching of values in a query

[Paragon Corporation]

Paul,
It does but across rows not columns.  The hack we use nowadays to overcome
this is 


(SELECT big_cal
>From blah OFFSET 0) As foo

That works better than ORDER BY actually (which as Nicklas noted doesn't
always force a materialize though offset seems to).  Materializing which is
not a good solution for large data sets where the whole dataset need not be
returned.  Someone pointed out this problem is not limited to PostgreSQL.
Oracle behaves the same way as someone noted here

http://www.postgresonline.com/journal/index.php?/archives/113-How-to-force-P
ostgreSQL-to-use-a-pre-calculated-value.html#c921

We have yet to test SQL Server if it behaves similarly.  

As David Fetter mentioned -- CTEs might solve the issue.  We were going to
experiment with that to see if that overcomes this issue.  Nevertheless --
yes we find it just as painful as Nicklas does since we do do a lot of
statistical calcs and things of that sort that necessitate us repeating
these things across columns.  It makes the difference between say a query
that runs in 10sec vs one that runs in under 3 secs.

Thanks,
L & R



-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Wednesday, July 08, 2009 5:59 PM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] reuse/caching of values in a query

On Wed, Jul 8, 2009 at 12:56 PM, <nicklas.aven at jordogskog.no> wrote:
> I'm I wrong thinking it is an important matter for postgis as a 
> calculation-intence part of the postgresql-world?

Maybe, maybe not. More to the point, is it the biggest performance fish to
fry? For example, implementing a prepared distance operation might save more
CPU cycles over the population of PostGIS use cases.

In any event, re-using function results is certainly a PgSQL core problem,
not ours. I actually thought that having a marked the function as immutable
gave us some of effect, but perhaps not.

P
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel