Thread: Auto-tuning a VIEW?

Hi,

we have a VIEW that is an UNION of 12 SELECTs, and every
member of the UNION have a constant field to be able to distinguish
between them.

An often performed operation on this VIEW is to search for only one record
that can be found by the value of the constant field and the serial of
a table in one of the UNION members.

Unfortunately, the search operation works in such a way so these two fields
are concatenated and the results is searched for.

Here is a shorter example, so it gets obvious what I tried to describe:

create view v1 (code,num) as
select 'AAA',id from table1
union
select 'BBB',id from table2;

The query is:

select * from v1 where code||num = 'AAA2005000001';

My problem is that this is slow, even after creating expression indexes
on the tables for e.g. ('AAA'||id).

If I optimize the UNION manually, it becomes:

select * from table1 where 'AAA'||id = 'AAA2005000001'
union
select * from table2 where 'BBB'||id = 'AAA2005000001';

and because of the expression indexes it's fast.

Is there a GEQO setting that make the above optimization
on VIEWs automatic?

 From the VIEW definition, the database already knows the connection
between the fields of the view and the fields of the table(s)
so the above optimization could be performed automatically.

Best regards,
Zoltán Böszörményi

> create view v1 (code,num) as
> select 'AAA',id from table1
> union
> select 'BBB',id from table2;

    As your rows are, by definition, distinct between each subquery, you
should use UNION ALL instead of UNION to save postgres the trouble of
hunting non-existing duplicates. This will save you a few sorts.

> select * from v1 where code||num = 'AAA2005000001';

    Why don't you use code='AAA' and num='2005000001' ?

PFC írta:

>
>> create view v1 (code,num) as
>> select 'AAA',id from table1
>> union
>> select 'BBB',id from table2;
>
>
>     As your rows are, by definition, distinct between each subquery,
> you  should use UNION ALL instead of UNION to save postgres the
> trouble of  hunting non-existing duplicates. This will save you a few
> sorts.


Thanks, now the SELECT from the huge VIEW runs under one third of the
original runtime.

>> select * from v1 where code||num = 'AAA2005000001';
>
>
>     Why don't you use code='AAA' and num='2005000001' ?


That's the point, the software environment we use cannot use it.
The whole stuff is built on PowerBuilder 8.0.x, using PFC.
The communication between the sheet and the response forms
allows only one key field, and changing the foundation is risky.
One particular application that uses the before mentioned VIEW with
the huge UNION also cannot workaround the problem, that's why I asked it.

The system is using Informix 9.21 and it's dog slow. I worked with
PostgreSQL earlier, and my tests show that PostgreSQL 8.x is
at least 5 times faster on normal queries than this other DBMS.
So I am trying to port the database contents to PostgreSQL first
and test some often used processing, to see whether it's feasible to
switch later.
Interestingly, the example query I provided runs about two times faster
in Informix than in PostgreSQL. I experimented a little and found what I
described.

Best regards,
Zoltán Böszörményi

> Thanks, now the SELECT from the huge VIEW runs under one third of the
> original runtime.

    Nice.

>>> select * from v1 where code||num = 'AAA2005000001';

    I do not know if it is at all possible, but maybe you could use a rule
to, on select to your view, do instead a select on the two separate
columns used in the key, with a bit of massaging on the values using
substring()...