Improving performance with a Function instead of a View

[Hadley Willan]

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;

So if there are three localised joins they are bound to the single
locale.

E.G

create view v_object as
select loc.id as locale,
obj.id as object_id,
obj.user_data as user_data,

type.id as object_type,
type_res.disp_name as object_type_display_name

size.id as object_size,
size_res.disp_name as object_size_disp_name

from locale as loc,
object as obj
left join object_type as type on type.id = obj.object_type
left join object_type_res as type_res on
type_res.object_type = obj.object_type
left join object_size as size on size.id = obj.object_size
left join object_size_res as size_res on
size_res.object_size = obj.object_size
where ( type_res.locale = loc.id OR type_res.locale IS NULL ) AND
( size_res.locale = loc.id OR size_res.locale IS NULL );

In this example the left joins are required to ensure the columns are
returned even if null as not all fields are required.

Anyway, there is a performance problem, and we have a temporary
solution.

I was wondering if it is possible to create a function that will return
a set of data with the correct view names and have this function perform
additional and fast checks server side?

Regards
--
Hadley Willan » Director » ha***********@deeperdesign.com » +64(21) 28
41 463
Deeper Design Limited » [phone] » www.deeperdesign.com

[Mike Mascari]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Hadley Willan]

Thanks, but I don't believe this to be a problem because my JDBC layer
when I construct the query is using setObject( parameter, getId,
Types.BIGINT )

so by the time it arrives at the database that cast should have already
occured?

I could be wrong but running the Explain Analyse shows indexes being
used, but the right join for the locale stuff is the killer.

Thanks

On Thu, 2004-02-05 at 13:31, Mike Mascari wrote:

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

--
Hadley Willan » Director » ha***********@deeperdesign.com » +64(21) 28
41 463
Deeper Design Limited » [phone] » www.deeperdesign.com

[Kris Jurka]

On Thu, 5 Feb 2004, Hadley Willan wrote:

Thanks, but I don't believe this to be a problem because my JDBC layer
when I construct the query is using setObject( parameter, getId,
Types.BIGINT )

so by the time it arrives at the database that cast should have already
occured?

The JDBC driver will not do any casting for you. The cross type indexing
problem is is a backend issue and has been addressed in the cvs version,
but this has long been a problem for JDBC users.

Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org