Thread

pgbench -i order of vacuum

Jeff Janes <jeff.janes@gmail.com> — 2012-07-20T00:05:36Z

Is there a reason to vacuum the pgbench_* tables after the indexes on
them are built, rather than before?

Since the indexes are on fresh tables, they can't have anything that
needs to be cleaned.

I don't think the current order accomplishes anything, except to slow
down large initializations by ~25%.

The attached patch moves the vacuums up.

I also made -n skip the vacuums altogether.  Since -n is allowed under
-i, it would be nice if it did something, and there is only one
intuitive thing for it to do.  I don't know what the use case for is,
but I think I've heard grumbling about it before.

Cheers,

Jeff

Re: pgbench -i order of vacuum

Amit Kapila <amit.kapila@huawei.com> — 2012-07-20T14:57:21Z

> From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Jeff Janes
> Sent: Friday, July 20, 2012 5:36 AM


> Is there a reason to vacuum the pgbench_* tables after the indexes on them
are built, rather than before?

> Since the indexes are on fresh tables, they can't have anything that needs
to be cleaned.

The command it executes is "vacuum analyze ..", so it will do analyze also
on table which means
it will collect stats corresponding to table and index. So if you do it
before creation of index pgbench might behave 
different.
In specific, from function do_analyze_rel(), it will not call
compute_index_stats() if you execute the command before
Creation of index.

With Regards,
Amit Kapila.

Re: pgbench -i order of vacuum

Jeff Janes <jeff.janes@gmail.com> — 2012-07-20T15:37:08Z

On Fri, Jul 20, 2012 at 7:57 AM, Amit Kapila <amit.kapila@huawei.com> wrote:
>> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Jeff Janes
>> Sent: Friday, July 20, 2012 5:36 AM
>
>
>> Is there a reason to vacuum the pgbench_* tables after the indexes on them
> are built, rather than before?
>
>> Since the indexes are on fresh tables, they can't have anything that needs
> to be cleaned.
>
> The command it executes is "vacuum analyze ..", so it will do analyze also
> on table which means
> it will collect stats corresponding to table and index.

Are there stats collected on indexes?  I thought all stats were on
tables only, and the only reason to visit the index was to remove
all-visible-dead entries.

Cheers,

Jeff

Re: pgbench -i order of vacuum

Tom Lane <tgl@sss.pgh.pa.us> — 2012-07-20T16:26:44Z

Jeff Janes <jeff.janes@gmail.com> writes:
> On Fri, Jul 20, 2012 at 7:57 AM, Amit Kapila <amit.kapila@huawei.com> wrote:
>> The command it executes is "vacuum analyze ..", so it will do analyze also
>> on table which means
>> it will collect stats corresponding to table and index.

> Are there stats collected on indexes?

Only for expression indexes, which there aren't any of in the standard
pgbench scenario.  I don't see a reason not to change the ordering
as you suggest.

			regards, tom lane

Re: pgbench -i order of vacuum

Robert Haas <robertmhaas@gmail.com> — 2012-07-23T18:45:01Z

On Fri, Jul 20, 2012 at 12:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jeff Janes <jeff.janes@gmail.com> writes:
>> On Fri, Jul 20, 2012 at 7:57 AM, Amit Kapila <amit.kapila@huawei.com> wrote:
>>> The command it executes is "vacuum analyze ..", so it will do analyze also
>>> on table which means
>>> it will collect stats corresponding to table and index.
>
>> Are there stats collected on indexes?
>
> Only for expression indexes, which there aren't any of in the standard
> pgbench scenario.  I don't see a reason not to change the ordering
> as you suggest.

OK, done.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company