Message-ID: <43A08EF5.4060306@dunaweb.hu>
Date: Wed, 14 Dec 2005 22:30:29 +0100
From: Zoltan Boszormenyi <zboszor@dunaweb.hu>
User-Agent: Mozilla Thunderbird 1.0.7-1.1.fc3 (X11/20050929)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Auto-tuning a VIEW?
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

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