On Wed, Oct 15, 2014 at 2:03 AM, Alban Hertroys <haram...@gmail.com> wrote:
> > On 15 Oct 2014, at 4:33, Abelard Hoffman <abelardhoff...@gmail.com> wrote: > > > I believe this query is well optimized, but it's slow if the all the > blocks aren't already in memory. > > > > Here's example explain output. You can see it takes over 7 seconds to > run when it needs to hit the disk, and almost all of it is related to > checking if the user has "messages." > > > > http://explain.depesz.com/s/BLT > > From that plan it is obvious that the index scan takes the most time. It > looks like you have 3315 rows matching to_id = users.id, of which only 10 > match your query conditions after applying the filter. > > With your current setup, the database first needs to find candidate rows > in the index and then has to check the other conditions against the table, > which is likely to involve some disk access. > > > On a second run, it's extremely fast (< 50ms). So I'm thinking it's a > lack of clustering on the "Index Cond: (to_id = users.user_id)" that's the > culprit. > > That probably means that the relevant parts of the table were still in > memory, which means the scan did not need to visit the disk to load the > matched rows to filter the NULL conditions in your query. > > > I'm afraid of using CLUSTER due to the exclusive lock, but I found > pg_repack while researching: > > http://reorg.github.io/pg_repack/ > > A CLUSTER would help putting rows with the same to_id together. Disk > access would be less random that way, so it would help some. > > According to your query plan, accessing disks (assuming that’s what made > the difference) was 154 (7700 ms / 50 ms) times slower than accessing > memory. I don’t have the numbers for your disks or memory, but that doesn’t > look like an incredibly unrealistic difference. That begs the question, how > random was that disk access and how much can be gained from clustering that > data? > > Did you try a partial index on to_id with those NULL conditions? That > should result in a much smaller index size, which in turn makes it faster > to scan - much so if the index is difficult to keep in memory because of > its size. More importantly though, the scan wouldn’t need to visit the > table to verify those NULL fields. > No, I haven't tried a more constrained index. Good point, makes sense. > > Does it seem likely that doing an --order-by on the to_id column would > have a significant impact in this case? pg_repack seems pretty stable and > safe at this point? > > Not being familiar with pg_repack I can’t advise on that. > > > I am going to try and test this in a dev environment first but wanted > feedback if this seemed like a good direction? > > You can try that CLUSTER or the approach with pg_repack regardless of my > suggestion for the partial index. It should speed disk access to those > records up regardless of how they are indexed. > I tried pg_repack in dev and it did make a dramatic improvement (pg_repack took ~ 65 minutes to run). After the repack, I couldn't get the query to take longer than 750ms. Should be much, much faster in production too. It seems like maybe the partial index is a better long-term fix though. And thank you, Josh, about the tip on table bloat. I'll take a look at that too. -- Best, AH
