Re: Why is indexonlyscan so darned slow? - Mailing list pgsql-hackers

From Joshua Berkus
Subject Re: Why is indexonlyscan so darned slow?
Date
Msg-id 250538564.300581.1337257332564.JavaMail.root@mail-1.01.com
Whole thread Raw
In response to Re: Why is indexonlyscan so darned slow?  (Ants Aasma <ants@cybertec.at>)
Responses Re: Why is indexonlyscan so darned slow?
List pgsql-hackers
Ants,

Well, that's somewhat better, but again hardly the gain in performance I'd expect to see ... especially since this is
idealcircumstances for index-only scan.   

bench2=# select count(*) from pgbench_accounts; count
----------20000000
(1 row)

Time: 3827.508 ms

bench2=# set enable_indexonlyscan=off;
SET
Time: 0.241 ms
bench2=# select count(*) from pgbench_accounts; count
----------20000000
(1 row)

Time: 16012.444 ms

For some reason counting tuples in an index takes 5X as long (per tuple) as counting them in a table.  Why?

----- Original Message -----
> On Thu, May 17, 2012 at 6:08 AM, Joshua Berkus <josh@agliodbs.com>
> wrote:
> > As you can see, the indexonlyscan version of the query spends 5% as
> > much time reading the data as the seq scan version, and doesn't
> > have to read the heap at all.  Yet it spends 20 seconds doing ...
> > what, exactly?
> >
> > BTW, kudos on the new explain analyze reporting ... works great!
>
> Looks like timing overhead. Timing is called twice per tuple which
> gives around 950ns per timing call for your index only result. This
> is
> around what is expected of hpet based timing. If you are on Linux you
> can check what clocksource you are using by running cat
> /sys/devices/system/clocksource/clocksource0/current_clocksource
>
> You can verify that it is due to timing overhead by adding timing off
> to the explain clause. Or use the pg_test_timing utility to check the
> timing overhead on your system. With hpet based timing I'm seeing
> 660ns timing overhead and 26.5s execution for your query, with timing
> off execution time falls to 2.1s. For reference, tsc based timing
> gives 19.2ns overhead and 2.3s execution time with timing.
>
> Ants Aasma
> --
> Cybertec Schönig & Schönig GmbH
> Gröhrmühlgasse 26
> A-2700 Wiener Neustadt
> Web: http://www.postgresql-support.de
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


pgsql-hackers by date:

Previous
From: Ants Aasma
Date:
Subject: Re: Why is indexonlyscan so darned slow?
Next
From: Joachim Wieland
Date:
Subject: Re: "could not open relation with OID" errors after promoting the standby to master