Hi,
I have two tables, one has 25000 rows and the other
has 6.5 million rows.
(25000 rows)
table1
(id text,
start int,
stop int)
with seperate index on three individual fiels.
6.5 million rows
table2
(id text,
start int,
stop int)
with seperate index on three individual fields.
When I query this two table and try to find overlaped
records, I have used this query:
**************************************************************************************************
select count(distinct(table1.id))
from table1, table2
where table1.id=table2.id
and ( (table2.start>=table1.start and table2.start <=
table1.stop)or (table2.start <= table1.start and table1.start <=
table2.stop) );
***************************************************************************************************
when I do a explain, I got this back:
************************************************************************************************
Aggregate (cost=353859488.21..353859488.21 rows=1
width=78) -> Merge Join (cost=1714676.02..351297983.38
rows=1024601931 width=78) -> Index Scan using genescript_genomseqid on
genescript (cost=0.00..750.35 rows=25115 width=62) -> Sort (cost=1714676.02..1714676.02
rows=6801733 width=16) -> Seq Scan on mouseblathuman
(cost=0.00..153685.33 rows=6801733 width=16)
EXPLAIN
*************************************************************************************************
My question is: 1) Why the query start a seq scan on
a much bigger table from beginning? I think it should
start to scan the first table and use index for the
bigger table. 2) The query itself takes
forever, is there a way to speed up it? 3) Does this has anything to
do with query planner?
This is kind of a urgent project, so your prompt help
is greatly appreciated. Thanks.
Jim
__________________________________________________
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
