At 12:41 PM 4/23/02 -0400, Bruce Momjian wrote: >This is an interesting point, that an index scan may fit in the cache >while a sequential scan may not. I can see cases where even a index >scan of a large percentage of the table may win over an sequential scan. >Interesting.
Yes and if it fits in the cache the random access costs drop by orders of magnitude as shown by a recent benchmark someone posted where a Solaris box cached gigs of data[1]. That's why it might be useful to know what the crossover points for index scan vs sequential scans for various random page cost values. e.g. set random page cost to 1 means optimizer will use sequential scan if it thinks an index scan will return 50% or more rows. set to 0.5 for 75% or more and so on. That's probably very simplistic, but basically some idea of what the optimizer will do given a random page cost could be helpful. Thanks, Link. [1] Mark Pritchard's benchmark where you can see 3rd try onwards random is actually faster than sequential after caching (TWICE as fast too!). Machine = jedi (Sun E420, 3gb ram, dual 400s, test on single scsi disk) Sequential Bytes Read Time Bytes / Sec 2097152000 65.19 32167675.28 2097152000 65.22 32154114.65 2097152000 65.16 32182561.99 2097152000 65.12 32206105.12 2097152000 64.67 32429463.26 32227984.06 (avg) Random Bytes Read Time Bytes / Sec 4194304000 1522.22 2755394.79 4194304000 278.18 15077622.05 4194304000 91.43 45874730.07 4194304000 61.43 68273795.19 4194304000 54.55 76890231.51 41774354.72 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html