Re: Creating Index

"CN" <cnliou9(at)fastmail(dot)fm> writes:
> I thought since both
> SELECT * FROM view1 WHERE year > 2003
> and
> SELECT * FROM view2 WHERE year > 2003
> returns 0 rows, subquery in view1 should consume no CPU and thus both
> queries should consume roughly the same amount of time.

The reason view1 is a lot slower is that the condition on "year" isn't
getting pushed down into the plan; instead the entire view result is
computed and then filtered at the top step:

> Subquery Scan view1 (cost=180.69..1579.97 rows=28483 width=56) (actual
> time=4676.80..4676.80 rows=0 loops=1)
> Filter: ("year" > 2003)
> -> Append (cost=180.69..1579.97 rows=28483 width=56) (actual
> time=168.35..4526.08 rows=28482 loops=1)

In view2 the year condition is successfully pushed all the way to the
bottom scans:

> -> Index Scan using i2c3c4 on table2
> (cost=0.00..5.04 rows=1 width=21) (actual
> time=0.57..0.57 rows=0 loops=1)
> Index Cond: (c3 > 2003)

thus eliminating a lot of join work.

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.

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.

In short: add a cast to make the UNION arms have the same datatypes.

regards, tom lane