Join of small table with large table

The big problem with the query probably isn't the scans due to your
dataset and the way indexes work.
I'm actually rather surprised it chose an index in the smaller table.

It's the agregate thats taking the time. Which means, faster CPU or
simpler aggregate will do the trick. Ie. Do you really need that
DISTINCT part?

--
Rod
----- Original Message -----
From: "large scale" <largescale_1999@yahoo.com>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, May 10, 2002 2:04 PM
Subject: [HACKERS] Join of small table with large table

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

---------------------------(end of

broadcast)---------------------------

Of course, something else you may want to do is is allow postgresql to
use a whack load more sort space in ram -- assumming you have ram
free.

Its probably hitting the disk alot for temporary storage space.

http://www.ca.postgresql.org/docs/momjian/hw_performance/
http://www.argudo.org/postgresql/soft-tuning.html

--
Rod
----- Original Message -----
From: "large scale" <largescale_1999@yahoo.com>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, May 10, 2002 2:04 PM
Subject: [HACKERS] Join of small table with large table

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

---------------------------(end of

broadcast)---------------------------

large scale <largescale_1999@yahoo.com> writes:

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)

That plan seems odd to me too. Have you done VACUUM ANALYZE on these
tables?

I would think that a hash join would be preferable. You might need to
increase the SORT_MEM parameter to let the whole smaller table be
stuffed into memory before the planner will think so, though.
Try setting it to 10000 or so (ie, 10 MB).

regards, tom lane

On Fri, 10 May 2002, large scale wrote:

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.

We'll start with the standard questions: Have you
vacuum analyzed? What version are you running? (if
it's less than 7.2, you may want to see about
upgrading) If you do a set enable_seqscan=false;
what does the explain show then? I'd be interested
in know if 1024601931 is even remotely a valid number
of rows from that join as well (which is about
.5% of an entire cartesian join if my math is right).

Perhaps some exists style thing would be faster since
that would at least presumably be able to stop when
it found a matching table2 row for a particular table1
id.