From: K C Lau <kclau60@netvigator.com>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Date: Thu, 22 Sep 2005 22:56:47 +0800
Message-ID: <6.2.1.2.0.20050922224720.02c365f8@localhost> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD3FF@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD3FF@Herge.rcsinc.local>
At 22:37 05/09/22, Merlin Moncure wrote:
> > >create or replace view VCurPlayer as select * from Player a
> > >where a.AtDate = (select Max(b.AtDate) from Player b where a.PlayerID=
> > >b.PlayerID);
>
>Here is a trick I use sometimes with views, etc. This may or may not be
>effective to solve your problem but it's worth a shot. Create one small
>SQL function taking date, etc. and returning the values and define it
>immutable. Now in-query it is treated like a constant.
We don't use functions as a rule, but I would be glad to give it a try.
I would most appreciate if you could define a sample function and rewrite
the VCurPlayer view above. Both PlayerID and AtDate are varchar fields.
>Another useful application for this feature is when you have nested
>views (view 1 queries view 2) and you need to filter records based on
>fields from view 2 which are not returned in view 1. Impossible?
>
>in view 2 add clause where v2.f between f_min() and f_max(), them being
>immutable functions which can grab filter criteria based on inputs or
>values from a table.
>
>Merlin
Best regards,
KC.
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-performance@postgresql.org
Cc: kclau60@netvigator.com, merlin.moncure@rcsonline.com
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
In-Reply-To: <6.2.1.2.0.20050922224720.02c365f8@localhost>
* 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