On 28/12/2011 19:41, Matteo Beccati wrote: > On 28/12/2011 19:07, Claudio Freire wrote: >> On Wed, Dec 28, 2011 at 3:02 PM, Matteo Beccati <php@xxxxxxxxxxx> wrote: >>> The query eventually completed in more than 18h. For comparison a normal >>> run doesn't take more than 1m for that specific step. >>> >>> Do you think that bad stats and suboptimal plan alone could explain such >>> a behaviour? >> >> Did you get the explain analyze output? > > Unfortunately I stopped it as I thought it wasn't going to return > anything meaningful. I've restarted the import process and it will break > right before the problematic query. Let's see if I can get any more info > tomorrow. So, I'm running again the EXPLAIN ANALYZE, although I don't expect it to return anytime soon. However I've discovered a few typos in the index creation. If we add it to the fact that row estimates are off for this specific query, I can understand that the chosen plan might have been way far from optimal with some badly picked statistics. This is the explain analyze of the query with proper indexes in place. As you can see estimates are still off, even though run time is ~20s: http://explain.depesz.com/s/1UY For comparison, here is the old explain output: http://explain.depesz.com/s/TqD The case is closed and as Tom pointed out already the lseek-only activity is due to the fact that the table is fully cached in the shared buffers and a sequential scan inside a nested loop is consistent with it. Sorry for the noise. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
