Thread: Which one is faster?
Between Using Limit P, S or using a cursor to start from FETCH ABSOLUTE S and FETCH NEXT for P times, which one is faster? Thanks! -- Wei Weng Network Software Engineer KenCast Inc.
Wei Weng wrote: > Between Using Limit P, S or using a cursor to start from FETCH ABSOLUTE > S and FETCH NEXT for P times, which one is faster? LIMIT is faster because it doesn't need to generate all the result, in some cases. -- Bruce Momjian | http://candle.pha.pa.us 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 wrote: > > Wei Weng wrote: > > Between Using Limit P, S or using a cursor to start from FETCH ABSOLUTE > > S and FETCH NEXT for P times, which one is faster? > > LIMIT is faster because it doesn't need to generate all the result, in > some cases. Neither does using a cursor need to generate all the results first. It's a portal, which is an executor state on hold, and FETCH is implemented as ExecutorRun() with the number of wanted result rows as limitation. Since LIMIT generates internally one more result row than really wanted, I don't think LIMIT would be much faster, but it's for sure less portable. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > Bruce Momjian wrote: > > > > Wei Weng wrote: > > > Between Using Limit P, S or using a cursor to start from FETCH ABSOLUTE > > > S and FETCH NEXT for P times, which one is faster? > > > > LIMIT is faster because it doesn't need to generate all the result, in > > some cases. > > Neither does using a cursor need to generate all the results first. It's > a portal, which is an executor state on hold, and FETCH is implemented > as ExecutorRun() with the number of wanted result rows as limitation. > Since LIMIT generates internally one more result row than really wanted, > I don't think LIMIT would be much faster, but it's for sure less > portable. Oh, I thought the portal had to be materialized before returning a row. Thanks. -- Bruce Momjian | http://candle.pha.pa.us 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