From: Zoltan Boszormenyi <zboszor@dunaweb.hu>
To: pgsql-performance@postgresql.org
Subject: Auto-tuning a VIEW?
Date: Wed, 14 Dec 2005 22:30:29 +0100
Message-ID: <43A08EF5.4060306@dunaweb.hu> (raw)
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
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
Subject: Re: Auto-tuning a VIEW?
In-Reply-To: <43A08EF5.4060306@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