pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUNION index use help
3+ messages / 2 participants
[nested] [flat]
* UNION index use help
@ 2005-10-06 15:59 Dmitri Bichko <dbichko@aveopharma.com>
2005-10-06 16:32 ` Re: UNION index use help Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Dmitri Bichko @ 2005-10-06 15:59 UTC (permalink / raw)
To: 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';
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UNION index use help
2005-10-06 15:59 UNION index use help Dmitri Bichko <dbichko@aveopharma.com>
@ 2005-10-06 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-10-06 16:32 UTC (permalink / raw)
To: Dmitri Bichko <dbichko@aveopharma.com>; +Cc: pgsql-sql
"Dmitri Bichko" <dbichko@aveopharma.com> writes:
> Ok, I'm thoroughly confused.
You didn't say which PG version you are using, but if it's something
reasonably recent then it should be able to push upper qual conditions
down into a UNION. I think you are getting bit by this restriction:
* 3. For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can
* push quals into each component query, but the quals can only reference
* subquery columns that suffer no type coercions in the set operation.
* Otherwise there are possible semantic gotchas.
You're getting burnt because you're unioning a text with a varchar.
Make the column types the same and it'll work better.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UNION index use help
@ 2005-10-06 17:25 Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dmitri Bichko @ 2005-10-06 17:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
That's exactly it - thanks, works perfectly now!
For the record, it's 8.0.3
Dmitri
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, October 06, 2005 12:32 PM
> To: Dmitri Bichko
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] UNION index use help
>
>
> "Dmitri Bichko" <dbichko@aveopharma.com> writes:
> > Ok, I'm thoroughly confused.
>
> You didn't say which PG version you are using, but if it's
> something reasonably recent then it should be able to push
> upper qual conditions down into a UNION. I think you are
> getting bit by this restriction:
>
> * 3. For subqueries using UNION/UNION
> ALL/INTERSECT/INTERSECT ALL, we can
> * push quals into each component query, but the quals can
> only reference
> * subquery columns that suffer no type coercions in the set
> operation.
> * Otherwise there are possible semantic gotchas.
>
> You're getting burnt because you're unioning a text with a
> varchar. Make the column types the same and it'll work better.
>
> regards, tom lane
>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-06 17:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-06 15:59 UNION index use help Dmitri Bichko <dbichko@aveopharma.com>
2005-10-06 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-06 17:25 Re: UNION index use help Dmitri Bichko <dbichko@aveopharma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox