Re: [GENERAL] query not scaling

2017-10-31 Thread Rob Sargent
On 10/31/2017 03:12 AM, Laurenz Albe wrote: Rob Sargent wrote: 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. Agree! 800,000 * 4,000 = 3,200,000,000. It's just that

Re: [GENERAL] query not scaling

2017-10-30 Thread Laurenz Albe
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) >

Re: [GENERAL] query not scaling

2017-10-27 Thread Merlin Moncure
On Thu, Oct 26, 2017 at 10:01 AM, Tom Lane wrote: > Laurenz Albe writes: >> Also, to have PostgreSQL inline the function, which would be good >> for performance, it should be declared IMMUTABLE. > > Actually, if you hope to have a SQL function be

Re: [GENERAL] query not scaling

2017-10-26 Thread Rob Sargent
On 10/26/2017 09:01 AM, Tom Lane wrote: Laurenz Albe writes: Also, to have PostgreSQL inline the function, which would be good for performance, it should be declared IMMUTABLE. Actually, if you hope to have a SQL function be inlined, it's better not to decorate it

Re: [GENERAL] query not scaling

2017-10-26 Thread Rob Sargent
On 10/26/2017 09:01 AM, Tom Lane wrote: Laurenz Albe writes: Also, to have PostgreSQL inline the function, which would be good for performance, it should be declared IMMUTABLE. Actually, if you hope to have a SQL function be inlined, it's better not to decorate it

Re: [GENERAL] query not scaling

2017-10-26 Thread Tom Lane
Laurenz Albe writes: > Also, to have PostgreSQL inline the function, which would be good > for performance, it should be declared IMMUTABLE. Actually, if you hope to have a SQL function be inlined, it's better not to decorate it at all --- not with IMMUTABLE, and not

Re: [GENERAL] query not scaling

2017-10-26 Thread Rob Sargent
> On Oct 26, 2017, at 1:02 AM, Laurenz Albe wrote: > > Rob Sargent wrote: >> I have a query I cannot tame and I'm wondering if there's an alternative >> to the "between" clause I'm using. Perhaps a custom type could do >> better? I've tried the "<@" orperator

Re: [GENERAL] query not scaling

2017-10-26 Thread Laurenz Albe
Rob Sargent wrote: > I have a query I cannot tame and I'm wondering if there's an alternative > to the "between" clause I'm using. Perhaps a custom type could do > better? I've tried the "<@" orperator and that changes the query plan > significantly but the execution cost/time is not

[GENERAL] query not scaling

2017-10-25 Thread Rob Sargent
I have a query I cannot tame and I'm wondering if there's an alternative to the "between" clause I'm using. Perhaps a custom type could do better? I've tried the "<@" orperator and that changes the query plan significantly but the execution cost/time is not improved. Any suggestion or