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';
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------------------------
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 Scan using 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 all
select 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=50482 width=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=32855 width=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=32855 width=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 null
source | character varying(15) | not null
id | character varying(25) | not null
current | 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 null
symbol | character varying(50) | not null
name | 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 confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please contact
the sender and delete the material from any computer
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
