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

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