X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D2D3DDA393 for ; Thu, 6 Oct 2005 12:57:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78390-01 for ; Thu, 6 Oct 2005 15:57:00 +0000 (GMT) Received: from mail.aveo.aveopharma.com (67.109.105.227.ptr.us.xo.net [67.109.105.227]) by svr1.postgresql.org (Postfix) with SMTP id 698E0DA1C4 for ; Thu, 6 Oct 2005 12:56:59 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: UNION index use help Date: Thu, 6 Oct 2005 11:59:51 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: UNION index use help Thread-Index: AcXKjvv818FKElieRbC5UzaI6MNTzw== From: "Dmitri Bichko" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.067 required=5 tests=[AWL=0.017, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/56 X-Sequence-Number: 22909 Ok, I'm thoroughly confused. Simple query: tb3=3D> 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=3D0.00..20.05 rows=3D1 width=3D19) (actual time=3D0.114..0.118 rows=3D1 loops=3D1) -> Index Scan using idx_alias_hs_alias on alias_hs a (cost=3D0.00..9.02 rows=3D2 width=3D29) (actual time=3D0.073..0.074 rows=3D= 1 loops=3D1) Index Cond: (upper(alias) ~=3D~ 'PPARG'::text) Filter: (upper(alias) ~~ 'PPARG'::text) -> Index Scan using idx_bin_hs_id_source on bin_hs (cost=3D0.00..5.5= 0 rows=3D1 width=3D28) (actual time=3D0.035..0.037 rows=3D1 loops=3D1) Index Cond: ((("outer".id)::text =3D (bin_hs.id)::text) AND (("outer".source)::text =3D (bin_hs.source)::text)) Total runtime: 0.167 ms (7 rows) A very similar query: tb3=3D> explain analyze select bin,symbol from gene_hs g join bin_hs b on= (gene_id =3D id) where upper(symbol) like 'PPARG'; QUERY PLAN ------------------------------------------------------------------------ -------------------------------------------------------------- Nested Loop (cost=3D0.00..20.03 rows=3D2 width=3D18) (actual time=3D0.068..0.073 rows=3D1 loops=3D1) -> Index Scan using idx_gene_hs_symbol on gene_hs g (cost=3D0.00..9.01 rows=3D2 width=3D19) (actual time=3D0.031..0.032 rows=3D= 1 loops=3D1) Index Cond: (upper((symbol)::text) ~=3D~ 'PPARG'::character varying) Filter: (upper((symbol)::text) ~~ 'PPARG'::text) -> Index Scan using idx_bin_hs_id_source on bin_hs b (cost=3D0.00..5.50 rows=3D1 width=3D19) (actual time=3D0.030..0.032 rows=3D= 1 loops=3D1) Index Cond: (("outer".gene_id)::text =3D (b.id)::text) Total runtime: 0.119 ms (7 rows) Now I create a union over the two of them: create view test as=20 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 =3D id) tb3=3D> explain analyze select * from test where upper(symbol) like 'PPARG'; =20 QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ -------------------- Subquery Scan test (cost=3D0.00..13327.60 rows=3D253 width=3D40) (actua= l time=3D479.139..1380.005 rows=3D2 loops=3D1) Filter: (upper(symbol) ~~ 'PPARG'::text) -> Append (cost=3D0.00..12570.37 rows=3D50482 width=3D19) (actual time=3D0.055..1194.445 rows=3D80610 loops=3D1) -> Subquery Scan "*SELECT* 1" (cost=3D0.00..7051.67 rows=3D176= 27 width=3D19) (actual time=3D0.054..742.596 rows=3D47755 loops=3D1) -> Merge Join (cost=3D0.00..6875.40 rows=3D17627 width=3D= 19) (actual time=3D0.052..677.190 rows=3D47755 loops=3D1) Merge Cond: (("outer".id)::text =3D ("inner".id)::text) Join Filter: (("outer".source)::text =3D ("inner".source)::text) -> Index Scan using idx_alias_hs_id on alias_hs a (cost=3D0.00..2501.30 rows=3D72214 width=3D29) (actual time=3D0.023..98.3= 77 rows=3D72214 loops=3D1) -> Index Scan using idx_bin_hs_id_source on bin_hs (cost=3D0.00..7819.21 rows=3D172194 width=3D28) (actual time=3D0.015..221= =2E023 rows=3D61520 loops=3D1) -> Subquery Scan "*SELECT* 2" (cost=3D827.69..5518.70 rows=3D32855 width=3D18) (actual time=3D77.123..382.122 rows=3D32855 loop= s=3D1) -> Hash Join (cost=3D827.69..5190.15 rows=3D32855 width=3D= 18) (actual time=3D77.119..340.501 rows=3D32855 loops=3D1) Hash Cond: (("outer".id)::text =3D ("inner".gene_id)::text) -> Seq Scan on bin_hs b (cost=3D0.00..3172.94 rows=3D172194 width=3D19) (actual time=3D3.464..106.064 rows=3D86097 loop= s=3D1) -> Hash (cost=3D745.55..745.55 rows=3D32855 width=3D= 19) (actual time=3D72.237..72.237 rows=3D0 loops=3D1) -> Seq Scan on gene_hs g (cost=3D0.00..745.5= 5 rows=3D32855 width=3D19) (actual time=3D0.012..41.666 rows=3D32855 loops=3D= 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=3D> \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=3D> \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=3D> \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 mate= rial. Any review, retransmission, dissemination or other use of, or takin= g 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 comput= er