| From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
|---|---|
| To: | Rob Sargent <robjsargent(at)gmail(dot)com> |
| Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: query not scaling |
| Date: | 2017-10-30 22:57:34 |
| Message-ID: | 1509404254.3584.2.camel@cybertec.at |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2017-10-30 23:21:51 | Re: Make "(composite).function_name" syntax work without search_path changes? |
| Previous Message | rakeshkumar464 | 2017-10-30 22:48:57 | pgaduit - is there a way to audit a role |