From: Zoltan Boszormenyi <zboszor@dunaweb.hu>
To: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Auto-tuning a VIEW?
Date: Wed, 14 Dec 2005 23:39:27 +0100
Message-ID: <43A09F1F.6030105@dunaweb.hu> (raw)
In-Reply-To: <op.s1smezd8cigqcu@peufeu.com>
References: <43A08EF5.4060306@dunaweb.hu>
<op.s1smezd8cigqcu@peufeu.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: zboszor@dunaweb.hu, lists@peufeu.com
Subject: Re: Auto-tuning a VIEW?
In-Reply-To: <43A09F1F.6030105@dunaweb.hu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox