Обсуждение: small table occupies many relpages

Hi,

we log access log data to a PostgreSQL 7.3.4 database. There is this
small table virtual_domains

access_logs2=# \d virtual_domains
                                        Table "public.virtual_domains"
      Column       |  Type   |                                   Modifiers
-------------------+---------+--------------------------------------------------------------------------------
 virtual_domain_id | integer | not null default nextval('public.virtual_domains_virtual_domain_id_seq'::text)
 virtual_domain    | text    |
Indexes: virtual_domains_pkey primary key btree (virtual_domain_id),
         virtual_domains_virtual_domain_key unique btree (virtual_domain)


It has a unique index on virtual_domain. Every day we do <10000 queries
of the form

insert into virtual_domains values (default, 'www.something.de')

to make sure www.something.de has an entry in virtual_domains. The table
virtual_domains does in fact contain only 4 records. The whole database
is vacuumed through pg_autovacuum (with its default parameters). We're
observing a steady growth of the relpages virtual_domains occupies:

access_logs2=# analyze virtual_domains ;
ANALYZE
access_logs2=# select relname, relpages from pg_class where relname like 'virtual_domains%';
                relname                | relpages
---------------------------------------+----------
 virtual_domains                       |      207
 virtual_domains_pkey                  |      257
 virtual_domains_virtual_domain_id_seq |        1
 virtual_domains_virtual_domain_key    |        2
(4 rows)

207 is pretty much for a table with 4 records :). We can fix that by running vacuum full:

access_logs2=# vacuum full verbose  analyze virtual_domains;
INFO:  --Relation public.virtual_domains--
INFO:  Pages 207: Changed 2, reaped 207, Empty 0, New 0; Tup 4: Vac 32191, Keep/VTL 0/0, UnUsed 1, MinLen 48, MaxLen
55;Re-using: Free/Avail. Space 1562616/7340; EndEmpty/Avail. Pages 206/1. 
        CPU 0.00s/0.01u sec elapsed 0.00 sec.
INFO:  Index virtual_domains_pkey: Pages 354; Tuples 4: Deleted 32191.
        CPU 0.02s/0.16u sec elapsed 0.93 sec.
INFO:  Index virtual_domains_virtual_domain_key: Pages 2; Tuples 4: Deleted 1.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Rel virtual_domains: Pages: 207 --> 1; Tuple(s) moved: 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_6130614--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0;
Re-using:Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. 
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_6130614_index: Pages 1; Tuples 0.
        CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  Analyzing public.virtual_domains
VACUUM
access_logs2=# select relname, relpages from pg_class where relname like 'virtual_domains%';
                relname                | relpages
---------------------------------------+----------
 virtual_domains                       |        1
 virtual_domains_pkey                  |      354
 virtual_domains_virtual_domain_id_seq |        1
 virtual_domains_virtual_domain_key    |        2
(4 rows)

The fsm settings are as follows:

max_fsm_relations = 2000        # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 2000000         # min 1000, fsm is free space map, ~6 bytes

The big table size slows down sequential scans significantly. What can
we do to prevent growth of the table? Are the fsm settings appropriate?
Why does the index on the primary key grow?

Thanks

--
Markus Bertheau <twanger@bluetwanger.de>

Markus Bertheau <twanger@bluetwanger.de> writes:
> It has a unique index on virtual_domain. Every day we do <10000 queries
> of the form
> insert into virtual_domains values (default, 'www.something.de')

So you're expecting these inserts to fail because of unique index
violations?

> The big table size slows down sequential scans significantly. What can
> we do to prevent growth of the table?

Vacuum more often.  You have to get rid of the tuples that failed the
index check.  They are already in the table when the index spits up.

            regards, tom lane

В Птн, 07.11.2003, в 16:45, Tom Lane пишет:
> Markus Bertheau <twanger@bluetwanger.de> writes:
> > It has a unique index on virtual_domain. Every day we do <10000 queries
> > of the form
> > insert into virtual_domains values (default, 'www.something.de')
>
> So you're expecting these inserts to fail because of unique index
> violations?

Yes.

>
> > The big table size slows down sequential scans significantly. What can
> > we do to prevent growth of the table?
>
> Vacuum more often.  You have to get rid of the tuples that failed the
> index check.  They are already in the table when the index spits up.

Does pg_autovacuum do something wrong, that it not catches this case?

--
Markus Bertheau <twanger@bluetwanger.de>

В Птн, 07.11.2003, в 21:45, Markus Bertheau написал:
> В Птн, 07.11.2003, в 16:45, Tom Lane пишет:
> > Markus Bertheau <twanger@bluetwanger.de> writes:
> > > It has a unique index on virtual_domain. Every day we do <10000 queries
> > > of the form
> > > insert into virtual_domains values (default, 'www.something.de')
> >
> > So you're expecting these inserts to fail because of unique index
> > violations?
>
> Yes.

Does such a failing insert reflect somehow on the statistics? i.e. could
pg_autovacuum be modified such that it catches that case?

--
Markus Bertheau <bertheau@bab24.de>
Cenes Data GmbH