Обсуждение: Re: Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
Re: Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
От
"Kevin Grittner"
Дата:
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> We've seen a lot of those lately -- Index Scan Backward >> performing far worse than alternatives. > > It's not clear to me that that has anything to do with Tim's > problem. It certainly wouldn't be 20000x faster if it were a > forward scan. Well, that's one way of looking at it. Another would be that the slower plan with the backward scan was only estimated to be 14.5% less expensive than the fast plan, so a pretty moderate modifier would have avoided this particular problem. The fact that the backward scan mis-estimate may be combining multiplicatively with other mis-estimates doesn't make it less important. -Kevin
On 03/16/2011 12:44 PM, Kevin Grittner wrote: > Well, that's one way of looking at it. Another would be that the > slower plan with the backward scan was only estimated to be 14.5% > less expensive than the fast plan, so a pretty moderate modifier > would have avoided this particular problem. I was wondering about that myself. Considering any backwards scan would necessarily be 10-100x slower than a forward scan unless the data was on an SSD, I assumed the planner was already using a multiplier to discourage its use. If not, it seems like a valid configurable. We set our random_page_cost to 1.5 once the DB was backed by NVRAM. I could see that somehow influencing precedence of a backwards index scan. But even then, SSDs and their ilk react more like RAM than even a large RAID... so should there be a setting that passes such useful info to the planner? Maybe a good attribute to associate with the tablespace, if nothing else. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@peak6.com ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email
On Wed, Mar 16, 2011 at 3:34 PM, Shaun Thomas <sthomas@peak6.com> wrote: > If not, it seems like a valid configurable. We set our random_page_cost to > 1.5 once the DB was backed by NVRAM. I could see that somehow influencing > precedence of a backwards index scan. But even then, SSDs and their ilk > react more like RAM than even a large RAID... so should there be a setting > that passes such useful info to the planner? Forgive the naive question... but... Aren't all index scans, forward or backward, random IO?
Re: Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
От
"Kevin Grittner"
Дата:
Claudio Freire <klaussfreire@gmail.com> wrote: > Forgive the naive question... > but... > > Aren't all index scans, forward or backward, random IO? No. Some could approach that; but, for example, an index scan immediately following a CLUSTER on the index would be totally sequential on the heap file access and would tend to be fairly close to sequential on the index itself. It would certainly trigger OS level read-ahead for the heap, and quite possibly for the index. So for a lot of pages, the difference might be between copying a page from the OS cache to the database cache versus a random disk seek. To a lesser degree than CLUSTER you could get some degree of sequencing from a bulk load or even from normal data insert patterns. Consider a primary key which is sequentially assigned, or a timestamp column, or receipt numbers, etc. As Tom points out, some usage patterns may scramble this natural order pretty quickly. Some won't. -Kevin