Message-ID: <43A09F1F.6030105@dunaweb.hu>
Date: Wed, 14 Dec 2005 23:39:27 +0100
From: Zoltan Boszormenyi <zboszor@dunaweb.hu>
User-Agent: Mozilla Thunderbird 1.0.7-1.1.fc3 (X11/20050929)
MIME-Version: 1.0
To: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Auto-tuning a VIEW?
References: <43A08EF5.4060306@dunaweb.hu> <op.s1smezd8cigqcu@peufeu.com>
In-Reply-To: <op.s1smezd8cigqcu@peufeu.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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