Thread: Dumb question - how to tell if autovacuum is doing its job in 8.2.x

Hi,

I couldnt find this on google, the archives, or the manual.  But with the changes to what the autovacuum daemon logs, how can we verify it's doing its thing?  Is there a way to query the last time a table was vacuumed?  But I don't see that in the system catalog.

Thanks!

---

Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.

Sic Transit Gloria Mundi <sitrglmu@yahoo.com> writes:
> I couldnt find this on google, the archives, or the manual.  But with the changes to what the autovacuum daemon logs,
howcan we verify it's doing its thing?  Is there a way to query the last time a table was vacuumed?  But I don't see
thatin the system catalog. 

Try the pg_stat views.

            regards, tom lane

pg_stat_all_table view should help you:

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Shoaib Mir wrote:

> pg_stat_all_table view should help you:
>
> select last_autovacuum, last_autoanalyze from pg_stat_all_tables;

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
  last_autovacuum | last_autoanalyze
-----------------+------------------
                  |
...snip lots of identically blank lines...
                  |
                  |
(939 rows)

Does that mean it's working or not configured right?

Thanks,
Walter

Ashish Karalkar wrote:
> Hello List,
>
> I want to append column of two different tables in a single column of  a view .
>
> data type of tow column of two diffrent tables will be same.
>
> WHAT I WANT TO DO IS:
>
>       Table 1
>       ID DESC
>       1 A
>       2 B
>       3 C
>
>       Table 2
>       ID DESC
>       1 D
>       2 E
>       3 F
>
>       View(Table1|| Table 2)
>       ID_view Desc
>
>       1 A
>       2 B
>       3 C
>       4 D
>       5 E
>       6 F
>
> Is there any way???

Looks like a UNION ALL to me.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Ashish Karalkar wrote:
> Hello List,
>
> I want to append column of two different tables in a single column of  a view .
>
> data type of tow column of two diffrent tables will be same.
>
> WHAT I WANT TO DO IS:
>
>       Table 1
>       ID DESC
>       1 A
>       2 B
>       3 C
>
>
>
>
>
>       Table 2
>       ID DESC
>       1 D
>       2 E
>       3 F
>
>
>
>       View(Table1|| Table 2)
>       ID_view Desc
>
>       1 A
>       2 B
>       3 C
>       4 D
>       5 E
>       6 F
>
>
>
> Is there any way???

A union -

SELECT id,desc FROM table1
UNION
SELECT id,desc FROM table2;

This will give you

ID_view Desc

1 A
2 B
3 C
1 D
2 E
3 F


If you actually want the id_view column to show 1 through 6 then you
will want to generate a sequence that is shown for that column instead
of the original id column. Or generate the id_view in the client, such
as use the row position in the returned set.




--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

I guess UNION ALL should work good here instead of a UNION for the exact same kind of output he needs:

SELECT id,desc FROM table1
UNION ALL
SELECT id,desc FROM table2;

---+---
 1 | A
 2 | B
 3 | C
 1 | D
 2 | E
 3 | F


As UNION gave me a little different output, like this:

---+--
 1 | A
 1 | D
 2 | B
 2 | E
 3 | C
 3 | F

--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)

Shoaib Mir wrote:
> I guess UNION ALL should work good here instead of a UNION for the exact
> same kind of output he needs:

That would be UNION ordering the results to remove duplicate rows which
UNION ALL doesn't do. Technically the results from any query can come
back in any order unless an ORDER BY is included.

> SELECT id,desc FROM table1
> UNION ALL
> SELECT id,desc FROM table2;
>
> ---+---
> 1 | A
> 2 | B
> 3 | C
> 1 | D
> 2 | E
> 3 | F
>
>
> As UNION gave me a little different output, like this:
>
> ---+--
> 1 | A
> 1 | D
> 2 | B
> 2 | E
> 3 | C
> 3 | F
>
> --
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
> On 2/13/07, Shane Ambler <pgsql@007marketing.com> wrote:
>>
>> Ashish Karalkar wrote:
>> > Hello List,
>> >
>> > I want to append column of two different tables in a single column
>> of  a
>> view .
>> >
>> > data type of tow column of two diffrent tables will be same.
>> >
>> > WHAT I WANT TO DO IS:
>> >
>> >       Table 1
>> >       ID DESC
>> >       1 A
>> >       2 B
>> >       3 C
>> >
>> >
>> >
>> >
>> >
>> >       Table 2
>> >       ID DESC
>> >       1 D
>> >       2 E
>> >       3 F
>> >
>> >
>> >
>> >       View(Table1|| Table 2)
>> >       ID_view Desc
>> >
>> >       1 A
>> >       2 B
>> >       3 C
>> >       4 D
>> >       5 E
>> >       6 F
>> >
>> >
>> >
>> > Is there any way???
>>
>> A union -
>>
>> SELECT id,desc FROM table1
>> UNION
>> SELECT id,desc FROM table2;
>>
>> This will give you
>>
>> ID_view Desc
>>
>> 1 A
>> 2 B
>> 3 C
>> 1 D
>> 2 E
>> 3 F
>>
>>
>> If you actually want the id_view column to show 1 through 6 then you
>> will want to generate a sequence that is shown for that column instead
>> of the original id column. Or generate the id_view in the client, such
>> as use the row position in the returned set.
>>
>>
>>
>>
>> --
>>
>> Shane Ambler
>> pgSQL@007Marketing.com
>>
>> Get Sheeky @ http://Sheeky.Biz
>>
>


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

So hmm a UNION with an ORDERY BY should be good for this scenario...

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Shoaib Mir wrote:
> So hmm a UNION with an ORDERY BY should be good for this scenario...

Only if the order matters to the OP, but he can always perform an ORDER
BY on the queries on his view. I don't really see the point.

The main difference between UNION and UINION ALL is that the latter
allows for duplicates, which removes the need to unduplicate the results
of the UNION (which requires ordering and is therefore relatively
expensive).

In short; if duplicates don't matter (or are desirtable even) UNION ALL
is faster.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

It means auto vacuum/analyze did not trigger on any of the
tables. You may want to try:

SELECT name, setting from pg_settings where name like  '%autovacuum%';

to get the settings of autovacuum and check if autovacuum is turned on or not.

Thanks,
Pavan

--

EnterpriseDB     http://www.enterprisedb.com

Make sure you have stats collector enabled, if auto vacuum is doing the analyze and vacuum it should be recording that info in this view. For details on this you can have a look at --> http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html

Just for a test try doing a VACUUM or ANALYZE manually and see if that gets updated in the last_vacuum of pg_stats_all_tables.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)