UNION index use help - Mailing list pgsql-sql

Ok, I'm thoroughly confused.

Simple query:

tb3=> explain analyze select bin, alias as symbol from alias_hs a join
bin_hs using (id,source) where upper(alias) like 'PPARG';
QUERYPLAN 
------------------------------------------------------------------------
--------------------------------------------------------------Nested Loop  (cost=0.00..20.05 rows=1 width=19) (actual
time=0.114..0.118 rows=1 loops=1)  ->  Index Scan using idx_alias_hs_alias on alias_hs a
(cost=0.00..9.02 rows=2 width=29) (actual time=0.073..0.074 rows=1
loops=1)        Index Cond: (upper(alias) ~=~ 'PPARG'::text)        Filter: (upper(alias) ~~ 'PPARG'::text)  ->  Index
Scanusing idx_bin_hs_id_source on bin_hs  (cost=0.00..5.50 
rows=1 width=28) (actual time=0.035..0.037 rows=1 loops=1)        Index Cond: ((("outer".id)::text = (bin_hs.id)::text)
AND
(("outer".source)::text = (bin_hs.source)::text))Total runtime: 0.167 ms
(7 rows)

A very similar query:

tb3=> explain analyze select bin,symbol from gene_hs g join bin_hs b on
(gene_id = id) where upper(symbol) like 'PPARG';                                                             QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------------------------Nested Loop  (cost=0.00..20.03 rows=2 width=18) (actual
time=0.068..0.073 rows=1 loops=1)  ->  Index Scan using idx_gene_hs_symbol on gene_hs g
(cost=0.00..9.01 rows=2 width=19) (actual time=0.031..0.032 rows=1
loops=1)        Index Cond: (upper((symbol)::text) ~=~ 'PPARG'::character
varying)        Filter: (upper((symbol)::text) ~~ 'PPARG'::text)  ->  Index Scan using idx_bin_hs_id_source on bin_hs b
(cost=0.00..5.50 rows=1 width=19) (actual time=0.030..0.032 rows=1
loops=1)        Index Cond: (("outer".gene_id)::text = (b.id)::text)Total runtime: 0.119 ms
(7 rows)

Now I create a union over the two of them:
create view test as select bin, alias as symbol from alias_hs a join bin_hs using
(id,source)union allselect bin,symbol from gene_hs g join bin_hs b on (gene_id = id)

tb3=> explain analyze select * from test where upper(symbol) like
'PPARG';
QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------Subquery Scan test  (cost=0.00..13327.60 rows=253 width=40) (actual
time=479.139..1380.005 rows=2 loops=1)  Filter: (upper(symbol) ~~ 'PPARG'::text)  ->  Append  (cost=0.00..12570.37
rows=50482width=19) (actual 
time=0.055..1194.445 rows=80610 loops=1)        ->  Subquery Scan "*SELECT* 1"  (cost=0.00..7051.67 rows=17627
width=19) (actual time=0.054..742.596 rows=47755 loops=1)              ->  Merge Join  (cost=0.00..6875.40 rows=17627
width=19)
(actual time=0.052..677.190 rows=47755 loops=1)                    Merge Cond: (("outer".id)::text =
("inner".id)::text)                    Join Filter: (("outer".source)::text =
("inner".source)::text)                    ->  Index Scan using idx_alias_hs_id on alias_hs a
(cost=0.00..2501.30 rows=72214 width=29) (actual time=0.023..98.377
rows=72214 loops=1)                    ->  Index Scan using idx_bin_hs_id_source on bin_hs
(cost=0.00..7819.21 rows=172194 width=28) (actual time=0.015..221.023
rows=61520 loops=1)        ->  Subquery Scan "*SELECT* 2"  (cost=827.69..5518.70
rows=32855 width=18) (actual time=77.123..382.122 rows=32855 loops=1)              ->  Hash Join  (cost=827.69..5190.15
rows=32855width=18) 
(actual time=77.119..340.501 rows=32855 loops=1)                    Hash Cond: (("outer".id)::text =
("inner".gene_id)::text)                    ->  Seq Scan on bin_hs b  (cost=0.00..3172.94
rows=172194 width=19) (actual time=3.464..106.064 rows=86097 loops=1)                    ->  Hash  (cost=745.55..745.55
rows=32855width=19) 
(actual time=72.237..72.237 rows=0 loops=1)                          ->  Seq Scan on gene_hs g  (cost=0.00..745.55
rows=32855 width=19) (actual time=0.012..41.666 rows=32855 loops=1)Total runtime: 1381.068 ms
(16 rows)

I can't figure out what is going on here.

Just in case here are the table structures:

tb3=> \d bin_hs                  Table "core.bin_hs"Column  |         Type          |       Modifiers
---------+-----------------------+------------------------bin     | bigint                | not nullsource  | character
varying(15)| not nullid      | character varying(25) | not nullcurrent | boolean               | not null default false 
Indexes:   "idx_bin_hs_bin" btree (bin)   "idx_bin_hs_id_source" btree (id, source)

tb3=> \d gene_hs           Table "core.gene_hs"Column  |         Type          | Modifiers
---------+-----------------------+-----------gene_id | character varying(25) | not nullsymbol  | character varying(50)
|not nullname    | text                  | 
Indexes:   "gene_hs_pkey" PRIMARY KEY, btree (gene_id)   "idx_gene_hs_symbol" btree (upper(symbol::text)
varchar_pattern_ops)

tb3=> \d alias_hs          Table "core.alias_hs"Column |         Type          | Modifiers
--------+-----------------------+-----------id     | character varying(25) |source | character varying(15) |alias  |
text                 | 
Indexes:   "idx_alias_hs_alias" btree (upper(alias) text_pattern_ops)   "idx_alias_hs_id" btree (id)

Am I doing something really stupid?

Thanks,
Dmitri
The information transmitted is intended only for the person or entity to which it is addressed and may contain
confidentialand/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any
actionin reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you
receivedthis in error, please contact the sender and delete the material from any computer