Обсуждение: Look at heap_beginscan()
Just do a search for heap_beginscan() and look at all those system table heap scans. Clearly, for large installations, we should be doing index scans. Seems like we should consider identifying all of the needed system indexes. I can add all indexes at one time, and people can go around and modify heap scans to index scans with heap_fetches of the tid if required. Comments? -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Just do a search for heap_beginscan() and look at all those system table > heap scans. Clearly, for large installations, we should be doing index > scans. There are a bunch of heap_beginscan() calls, but my first impression from a quick scan is that most of them are in very non-performance- critical paths --- not to mention paths that are deliberately ignoring indexes because they're bootstrap or reindex code. Furthermore, some of the remainder are scans of pretty darn small tables. (Do we need to convert sequential scans of pg_am to indexed scans? Nyet.) I'd be real hesitant to do a wholesale conversion, and even more hesitant to add new system indexes to support indexscans that we have not *proven* to be performance bottlenecks. It's certainly something worth looking at, since we've identified a couple of places like this that are indeed hotspots. But we need to convince ourselves that other places are also hotspots before we add overhead in hopes of making those places faster. regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Just do a search for heap_beginscan() and look at all those system table > > heap scans. Clearly, for large installations, we should be doing index > > scans. > > There are a bunch of heap_beginscan() calls, but my first impression > from a quick scan is that most of them are in very non-performance- > critical paths --- not to mention paths that are deliberately ignoring > indexes because they're bootstrap or reindex code. Furthermore, some > of the remainder are scans of pretty darn small tables. (Do we need > to convert sequential scans of pg_am to indexed scans? Nyet.) > > I'd be real hesitant to do a wholesale conversion, and even more > hesitant to add new system indexes to support indexscans that we > have not *proven* to be performance bottlenecks. Well, how do we know where the critical paths are? Seems adding an index is a cheap way to know we have all paths covered. If the table is not updated, the index is really no overhead except 16k of disk space. > It's certainly something worth looking at, since we've identified > a couple of places like this that are indeed hotspots. But we need > to convince ourselves that other places are also hotspots before > we add overhead in hopes of making those places faster. Are you suggesting that heap scan is faster than index in most of these cases? How many rows does it take for a heap scan to be faster than an index scan? -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026