Skip site navigation (1) Skip section navigation (2)

Re: Why is indexonlyscan so darned slow?

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Ants Aasma <ants(at)cybertec(dot)at>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Why is indexonlyscan so darned slow?
Date: 2012-05-21 19:22:52
Message-ID: CA+U5nMJUK1opc=XDhTj9fWdfob0psvgmdivg3HXkzpkUEnaowQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 21 May 2012 13:41, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Well, if it's not CPU costs, then something else is eating the time,
>> since I'm seeing per-tuple COUNT counts on indexes being 400% more than
>> on heap.
>
> Well, I'm not: as I said, it looks like about 10% here.  Perhaps you're
> testing a cassert-enabled build?
>
>> In the airport you said something about index-only scan not scanning the
>> tuples in leaf page order.   Can you elaborate on that?
>
> If the index is too big to fit in RAM, you'd be looking at random
> fetches of the index pages in most cases (since logical ordering of the
> index pages is typically different from physical ordering), leading to
> it likely being a lot slower per page than a heapscan.  Not sure this
> has anything to do with your test case though, since you said you'd
> sized the index to fit in RAM.

As you point out, this is still an IndexScan even if the heap access is zero.

Surely the way to solve this is by having a new plan node that does a
physical SeqScan of the index relation. It means we wouldn't preserve
the sort order of the rows from the index, but that is just a plan
cost issue.

This is exactly what we do for VACUUM and it works faster there.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

In response to

Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2012-05-21 19:40:52
Subject: Re: heap metapages
Previous:From: Simon RiggsDate: 2012-05-21 19:15:43
Subject: Re: heap metapages

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group