Re: [PERFORM] slow self-join query

On Sun, Mar 18, 2012 at 08:30, Scott Marlowe wrote: > Why are you joining twice to the parent table?  If you're trying to > recurse without a with clause, then wouldn't you join the last table > to the one before it? I'm FAR from being an SQL expert; there's a significant chance that I'm not thin

Re: [PERFORM] slow self-join query

also also wik On Sun, Mar 18, 2012 at 8:37 AM, Robert Poor wrote: > On Sat, Mar 17, 2012 at 23:07, Scott Marlowe > wrote: >> >> Yeah try setting [work_mem] to something absurd like 500MB and see if the >> plan changes. > > > Suweet!  Sorting now runs in-memory, and that makes a big difference, e

Re: [PERFORM] slow self-join query

On Sun, Mar 18, 2012 at 8:37 AM, Robert Poor wrote: > On Sat, Mar 17, 2012 at 23:12, Scott > Marlowe  wrote: > >> >> Also also this looks like it's the most expensive operation: >> >> Seq Scan on followings f2 (cost=0.00..93523.95 rows=5534395 width=8) >> (actual time=0.041..19365.834 rows=5535964

Re: [PERFORM] slow self-join query

On 03/18/2012 10:37 AM, Robert Poor wrote: On Sat, Mar 17, 2012 at 23:09, Scott Marlowe > wrote: Also it looks like you're still not using the index on this: Subquery Scan u1 (cost=0.00..313.55 rows=50 width=4) (actual time=0.030..147.136 rows=100

Re: [PERFORM] slow self-join query

On Sat, Mar 17, 2012 at 23:07, Scott Marlowe wrote: > Yeah try setting [work_mem] to something absurd like 500MB and see if the > plan changes. > Suweet! Sorting now runs in-memory, and that makes a big difference, even when groveling over 1M records (under 12 seconds rather than 7 hours). Resu

Re: [PERFORM] Shared memory for large PostGIS operations

Hi Kevin, List, others... On Mar 16, 2012, at 7:17 AM, Kevin Grittner wrote: Andy Colson wrote: I tried shared_buffers at both 2400M and 18000M, and it took 4.5 hours both times. ... (weak attempts at humor omitted) All else being the same, adjusting shared_buffers affects how much o