Re: Creating Index

> The reason why view1 isn't well optimized is that you've been sloppy
> about datatypes.  It looks to me like the "SELECT CASE" business yields
> NUMERIC while the other arm of the UNION yields INTEGER for table4.c3.
> For various subtle semantic reasons we do not try to push down
> conditions into UNIONs when the UNION arms yield different datatypes.

Absolutely right! After switching table3.c5 to INTEGER, query to view1 is
lightening fast:

--------------Subquery Scan view1  (cost=0.00..23.18 rows=4 width=48) (actualtime=0.13..0.13 rows=0 loops=1)  ->
Append (cost=0.00..23.18 rows=4 width=48) (actual time=0.12..0.12  rows=0 loops=1)        ->  Subquery Scan "*SELECT*
1" (cost=0.00..23.18 rows=3        width=48) (actual time=0.11..0.11 rows=0 
 
loops=1)              ->  Nested Loop  (cost=0.00..23.18 rows=3 width=48)              (actual time=0.10..0.10 rows=0
loops=1)                   ->  Index Scan using i2c3c4 on table2                     (cost=0.00..5.04 rows=1 width=21)
(actual
 
time=0.09..0.09 rows=0 loops=1)                          Index Cond: (c3 > 2003)                    ->  Index Scan
usingtable3_pkey on table3                     (cost=0.00..18.09 rows=4 width=27) (never 
 
executed)                          Index Cond: (table3.c1 = "outer".c1)                    SubPlan
-> Index Scan using table1_pkey on table1                       (cost=0.00..3.01 rows=1 width=1) (never 
 
executed)                            Index Cond: (c1 = $2)        ->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.00
rows=1       width=12) (actual time=0.01..0.01 rows=0 
 
loops=1)              ->  Seq Scan on table4  (cost=0.00..0.00 rows=1 width=12)              (actual time=0.01..0.01
rows=0
 
loops=1)                    Filter: (c1 > 2003)Total runtime: 0.69 msec

> 7.4 would optimize this query successfully anyway because the condition
> isn't actually on the column with inconsistent datatype --- but 7.3 just
> punts if *any* of the UNION columns have inconsistent datatypes.

Apparently this postgreSQL beast has always been well under control by
the fingers of you genious developers!

Long live the king!
Ooops! I'm sorry! Please pardon my English!

Long live postgreSQL, the no. 1 DBMS, and its masters - the developers!

Best Regards,

CN

-- 
http://www.fastmail.fm - Email service worth paying for. Try it for free