On Mon, May 21, 2012 at 4:17 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Mon, May 21, 2012 at 1:42 PM, Josh Berkus <j...@agliodbs.com> wrote: >> >>> Earlier you said that this should be an ideal setup for IOS. But it >>> isn't really--the ideal set up is one in which the alternative to an >>> IOS is a regular index scan which makes many uncached scattered reads >>> into the heap. I don't think that that situation can't really be >>> engineered with a where-less query. >> >> Can you give me some suggested comparisons which *would* be ideal, then? > > Are you looking for vaguely real-life examples, or highly contrived > examples used to dissect the server? > > For vaguely real life, take your example of pgbench -i -s200 -F 50, > and I have 2Gig RAM, which seems to be the same as you do. > > With select only work load (pgbench -S -M prepared -T 30), I get > > tps = 193 > > But now enable index-only scans: > > psql -c "create index on pgbench_accounts(aid, abalance);" > > and it goes up to. > > tps = 10137
Right -- the main driver here is that your index fits neatly in ram and the heap does not -- so you're effectively measuring the difference between a buffered and non-buffered access. That's highly contrived as you noted and unlikely to come up all *that* often in the real world. Generally though the real world wins (although the gains will be generally less spectacular) are heavily i/o bound queries where the indexed subset of data you want is nicely packed and the (non clustered) heap records are all over the place. By skipping the semi random heap lookups you can see enormous speedups. I figure 50-90% improvement would be the norm there, but this is against queries that are taking forever, being i/o bound. See here: http://www.devheads.net/database/postgresql/performance/index-all-necessary-columns-postgres-vs-mssql.htm for a 'in the wild' gripe about about not having index scans. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers