Re: Improving performance with a Function instead of a

Hadley Willan wrote:

> Hi all,
>     I am using some views now to put together a particular format for
> my Java client factory to produce Java Beans from the database.
>
> Because we support internationalisation we are representing values as
> an id then storing their multiple languages in unicode to support the
> same repesentation at the database.
>
> This format is:
>
> base_table, id bigint, is_disabled boolean default false.
>
> resource_table, foreign_key_to_base_table, locale_foreign_key,
> display_name, is_translated
>
> As such, my views are quite slow because there are a number of Right
> Joins occuring so that I can present a single "locale" field in the
> view that all the localised information will attach to correctly.
>
> That way I can > select * FROM v_object where locale = 'en_GB' and
> object_id = 120031;


Without taking the view definition into account, the above query could
not use an index on object_id because it is of type 'bigint', but the
integer constant is parsed as 'integer'. It must either be rewritten as:

object_id = 120031::bigint

or

object_id = '120031'

or set the sequence for this identifier to start fetching values > 4.2
billion (32-bit numbers). Of course, the view definition may have other
optimization possibilities as well...

Mike Mascari