On 7.3.2015 03:26, Jeff Janes wrote: > On Fri, Mar 6, 2015 at 5:38 PM, Tom Lane <tgl@xxxxxxxxxxxxx > <mailto:tgl@xxxxxxxxxxxxx>> wrote: > > But the actual query is using a seq scan, and so it would hint the > table in efficient sequential order, rather than hinting the table > haphazardly in index order like probing the endpoint does. I think this has nothing to do with the plan itself, but with the estimation in optimizer - that looks up the range from the index in some cases, and that may generate random I/O to the table. > >> Also, it's less than clear why only this particular query is >> showing any stress. Dead rows should be a hazard for anything, >> especially if there are enough of them to require hours to re-hint. >> And why wouldn't autovacuum get to them first? > > > Say the timing of this query is such that 10% of the parent turns > over between invocations of this query, and that this 10% is all at > the end of some index but random over the table heap. If autovac > kicks in at 20% turn over, then half the time autovac does get to > them first, and half the time it doesn't. It would be interesting to > know if this query is bad every time it is planner, or just > sometimes. Yeah, this might be the reason. Another possibility is that this is part of some large batch, and autovacuum simply did not have change to do the work. regards -- Tomas Vondra http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
