Re: Join of small table with large table - Mailing list pgsql-hackers

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)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>