Re: Interesting speed anomaly

On Thu, Dec 15, 2005 at 12:04:33AM +0100, Zoltan Boszormenyi wrote:
> The VIEW is created like this (shorter example):
>
> create view v1 (code,num) as
> select 'AAA',id from table1
> union
> select 'BBB',id from table2;
>
> I created the indexes on the individual tables as
>
> create index index1 on table1 (('AAA'||id));
> create index index2 on table2 (('BBB'||id));
>
> Every index has the same literal the table is associated with in the VIEW.
>
> Here is the explain analyze output, on PostgreSQL 8.0.3.
> I can test the same from 8.1.1 tomorrow.
>
> *************************************************
<snip>
> *************************************************
>
> It's interesting that if I rewrite this huge VIEW + the WHERE condition
> manually like this (the above short example continues)
>
> select * from table1 where 'AAA'||id = 'AAA2005000001'
> union
> select * from table2 where 'BBB'||id = 'AAA2005000001';
>
> then it will use the expression indexes and it runs under about 300 msecs.
> Replacing UNION with UNION ALL further reduces the runtime,
> as someone suggested on the pgsql-performance list.

Those queries aren't the same though. The view is equivalent to

SELECT *
FROM
(select 'AAA' AS prefix,id from table 1
union select 'AAA',id from table 2
) view
WHERE prefix||id = '...'

In this case the prefixes have already been unioned together, so there's
no chance for the planner to use the function index.

If break the WHERE clause into seperate clauses, such as

WHERE prefix='AAA' AND id = '2005000001'

then I think the planner will know what selects it can simply ignore. If
that doesn't work, then add 'AAA'||id AS fullid to each of the selects
in the view and that should allow the function indexes to be used.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461