On Thu, 2017-10-26 at 19:07 -0600, Rob Sargent wrote:
> -> Nested Loop (cost=3799.40..44686205.23 rows=1361304413 width=40)
> (actual time=55.443..89684.451 rows=75577302 loops=1)
> -> Hash Join (cost=3798.98..43611.56 rows=823591 width=32)
> (actual time=55.393..1397.509 rows=823591 loops=1)
> -> Index Scan using marker_chrom_basepos_idx on base.marker m
> (cost=0.42..37.67 rows=1653 width=20)
> (actual time=0.010..0.075 rows=92 loops=823591)
> Index Cond: ((m.chrom = 22) AND (m.basepos >= s.startbase) AND
(m.basepos <= s.endbase))
I think your biggest problem is the join condition on m.basepos between s.startbase and s.endbase
That forces a nested loop join, which cannot be performed efficiently.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general