SEO

I have small database. However the following query takes 38 (!) seconds to
run.
How to speed it up (preferably not changing table structures but possibly
creating indexes) ?

Andrus.

set search_path to public,firma1;
explain analyze select bilkaib.summa from BILKAIB join KONTO CRKONTO ON
bilkaib.cr=crkonto.kontonr AND
crkonto.iseloom='A'
join KONTO DBKONTO ON bilkaib.db=dbkonto.kontonr AND
dbkonto.iseloom='A'
left join klient on bilkaib.klient=klient.kood
where ( bilkaib.cr LIKE '3'||'%' OR
bilkaib.db LIKE '3'||'%' )
AND bilkaib.kuupaev BETWEEN '2006-01-01' AND '2006-03-31'
AND ( kuupaev='20060101' OR (cr!='00' and db!='00'))
AND ( 3 IN(2,3) or (NOT bilkaib.ratediffer and (
TRIM(bilkaib.masin)='' or bilkaib.masin IS NULL or
bilkaib.alusdok not in ('KV', 'DU', 'DJ') or
bilkaib.andmik is NULL or bilkaib.alusdok is NULL or
substring(andmik from 1 for 9)!='Kursivahe'
))) and
( position(bilkaib.laustyyp IN 'x')=0 or
bilkaib.laustyyp is null or bilkaib.laustyyp=' ')


"Nested Loop Left Join (cost=23.30..1964.10 rows=1 width=10) (actual
time=7975.470..38531.724 rows=3151 loops=1)"
" -> Nested Loop (cost=23.30..1958.08 rows=1 width=26) (actual
time=7975.407..37978.718 rows=3151 loops=1)"
" Join Filter: ("inner".cr = "outer".kontonr)"
" -> Seq Scan on konto crkonto (cost=0.00..23.30 rows=1 width=44)
(actual time=0.135..13.913 rows=219 loops=1)"
" Filter: (iseloom = 'A'::bpchar)"
" -> Hash Join (cost=23.30..1934.64 rows=11 width=40) (actual
time=1.650..155.734 rows=3151 loops=219)"
" Hash Cond: ("outer".db = "inner".kontonr)"
" -> Index Scan using bilkaib_kuupaev_idx on bilkaib
(cost=0.00..1897.10 rows=2826 width=54) (actual time=1.628..111.216
rows=3151 loops=219)"
" Index Cond: ((kuupaev >= '2006-01-01'::date) AND
(kuupaev <= '2006-03-31'::date))"
" Filter: (((cr ~~ '3%'::text) OR (db ~~ '3%'::text)) AND
((kuupaev = '2006-01-01'::date) OR ((cr <> '00'::bpchar) AND (db <>
'00'::bpchar))) AND (("position"('x'::text, (laustyyp)::text) = 0) OR
(laustyyp IS NULL) OR (laustyyp = ' '::bpc (..)"
" -> Hash (cost=23.30..23.30 rows=1 width=44) (actual
time=2.278..2.278 rows=219 loops=1)"
" -> Seq Scan on konto dbkonto (cost=0.00..23.30 rows=1
width=44) (actual time=0.017..1.390 rows=219 loops=1)"
" Filter: (iseloom = 'A'::bpchar)"
" -> Index Scan using klient_pkey on klient (cost=0.00..6.01 rows=1
width=52) (actual time=0.138..0.158 rows=1 loops=3151)"
" Index Cond: ("outer".klient = klient.kood)"
"Total runtime: 38561.745 ms"





CREATE TABLE firma1.bilkaib
(
id int4 NOT NULL DEFAULT nextval('bilkaib_id_seq'::regclass),
kuupaev date NOT NULL,
db char(10) NOT NULL,
dbobjekt char(10),
cr char(10) NOT NULL,
crobjekt char(10),
summa numeric(14,2) NOT NULL,
raha char(3) NOT NULL,
masin char(5),
klient char(12),
alusdok char(2),
dokumnr int4 NOT NULL DEFAULT nextval('bilkaib_dokumnr_seq'::regclass),
db2objekt char(10),
cr2objekt char(10),
db3objekt char(10),
db4objekt char(10),
db5objekt char(10),
db6objekt char(10),
db7objekt char(10),
db8objekt char(10),
db9objekt char(10),
cr3objekt char(10),
cr4objekt char(10),
cr5objekt char(10),
cr6objekt char(10),
cr7objekt char(10),
cr8objekt char(10),
cr9objekt char(10),
exchrate numeric(13,8),
doknr char(25),
andmik text,
laustyyp char(1),
ratediffer ebool,
adoknr char(25),
jarjeknr numeric(7),
CONSTRAINT bilkaib_pkey PRIMARY KEY (id),
CONSTRAINT bilkaib_alusdok_fkey FOREIGN KEY (alusdok)
REFERENCES firma1.alusdok (alusdok) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr2objekt_fkey FOREIGN KEY (cr2objekt)
REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr3objekt_fkey FOREIGN KEY (cr3objekt)
REFERENCES firma1.yksus3 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr4objekt_fkey FOREIGN KEY (cr4objekt)
REFERENCES firma1.yksus4 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr5objekt_fkey FOREIGN KEY (cr5objekt)
REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr6objekt_fkey FOREIGN KEY (cr6objekt)
REFERENCES firma1.yksus6 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr7objekt_fkey FOREIGN KEY (cr7objekt)
REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr8objekt_fkey FOREIGN KEY (cr8objekt)
REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr9objekt_fkey FOREIGN KEY (cr9objekt)
REFERENCES firma1.yksus9 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr_fkey FOREIGN KEY (cr)
REFERENCES firma1.konto (kontonr) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_crobjekt_fkey FOREIGN KEY (crobjekt)
REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db2objekt_fkey FOREIGN KEY (db2objekt)
REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db3objekt_fkey FOREIGN KEY (db3objekt)
REFERENCES firma1.yksus3 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db4objekt_fkey FOREIGN KEY (db4objekt)
REFERENCES firma1.yksus4 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db5objekt_fkey FOREIGN KEY (db5objekt)
REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db6objekt_fkey FOREIGN KEY (db6objekt)
REFERENCES firma1.yksus6 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db7objekt_fkey FOREIGN KEY (db7objekt)
REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db8objekt_fkey FOREIGN KEY (db8objekt)
REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db9objekt_fkey FOREIGN KEY (db9objekt)
REFERENCES firma1.yksus9 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db_fkey FOREIGN KEY (db)
REFERENCES firma1.konto (kontonr) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_dbobjekt_fkey FOREIGN KEY (dbobjekt)
REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_klient_fkey FOREIGN KEY (klient)
REFERENCES firma1.klient (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_raha_fkey FOREIGN KEY (raha)
REFERENCES raha (raha) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_id_check CHECK (id > 0)
)
WITHOUT OIDS;

CREATE INDEX bilkaib_dokumnr_idx ON firma1.bilkaib USING btree (dokumnr);

CREATE INDEX bilkaib_kuupaev_idx ON firma1.bilkaib USING btree (kuupaev);


CREATE TABLE firma1.konto
(
kontonr char(10) NOT NULL,
tyyp char(1) NOT NULL,
klienkaupa ebool,
arvekaupa ebool,
objekt1 char(1),
objekt2 char(1),
objekt3 char(1),
objekt4 char(1),
objekt5 char(1),
objekt6 char(1),
objekt7 char(1),
objekt8 char(1),
objekt9 char(1),
tekst char(55),
rustekst char(55),
engtekst char(55),
fintekst char(55),
lvltekst char(55),
raha char(3) NOT NULL,
kontoklass char(10),
grupp char(13),
klient char(12),
iseloom char(1),
kontokl2 char(10),
kontokl3 char(10),
eelklassif char(10),
klassif8 char(10),
rid3obj char(1),
rid4obj char(1),
koondkonto char(10),
kaibedrida char(6),
CONSTRAINT konto_pkey PRIMARY KEY (kontonr),
CONSTRAINT konto_klassif8_fkey FOREIGN KEY (klassif8)
REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_klient_fkey FOREIGN KEY (klient)
REFERENCES firma1.klient (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_kontokl2_fkey FOREIGN KEY (kontokl2)
REFERENCES bilskeem2 (kontoklass) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_kontokl3_fkey FOREIGN KEY (kontokl3)
REFERENCES bilskeem3 (kontoklass) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_kontoklass_fkey FOREIGN KEY (kontoklass)
REFERENCES bilskeem1 (kontoklass) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_raha_fkey FOREIGN KEY (raha)
REFERENCES raha (raha) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
)
WITHOUT OIDS;

CREATE TRIGGER konto_trig BEFORE INSERT OR UPDATE OR DELETE
ON firma1.konto FOR EACH STATEMENT EXECUTE PROCEDURE setlastchange();


CREATE TABLE firma1.klient
(
kood char(12) NOT NULL DEFAULT nextval('klient_kood_seq'::regclass),
nimi char(70),
a_a char(35),
p_kood char(10),
regnr char(12),
vatpayno char(15),
piirkond char(30),
postiindek char(10),
tanav char(30),
kontaktisi char(30),
telefon char(25),
faks char(25),
email char(60),
infomail char(60),
wwwpage char(50),
liik char(10),
viitenr char(20),
riik char(20),
riik2 char(2),
riigikood char(3),
hinnak char(5),
erihinnak char(5),
myygikood char(4),
objekt2 char(10),
objekt5 char(10),
objekt7 char(10),
maksetin char(5),
omakseti char(5),
krediit numeric(12,2),
ostukredii numeric(12,2),
masin char(5),
info text,
maksja char(12),
"timestamp" char(14) NOT NULL DEFAULT to_char(now(),
'YYYYMMDDHH24MISS'::text),
atimestamp char(14) NOT NULL DEFAULT to_char(now(),
'YYYYMMDDHH24MISS'::text),
elanikud numeric(3),
pindala numeric(7,2),
grmaja char(10),
apindala numeric(7,2),
kpindala numeric(7,2),
idmakett char(36),
tulemus char(100),
omandisuhe char(1),
username char(10),
changedby char(10),
parool char(20),
hinnaale char(4),
mitteakt ebool,
kontakteer date,
klikaart char(16),
mhprotsent numeric(5,1),
aadress text,
swift char(20),
pankaad char(20),
_nimi char(70),
CONSTRAINT klient_pkey PRIMARY KEY (kood),
CONSTRAINT klient_changedby_fkey FOREIGN KEY (changedby)
REFERENCES kasutaja (kasutaja) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_grmaja_fkey FOREIGN KEY (grmaja)
REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_hinnak_fkey FOREIGN KEY (hinnak)
REFERENCES firma1.hkpais (hinnak) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_idmakett_fkey FOREIGN KEY (idmakett)
REFERENCES makett (guid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_liik_fkey FOREIGN KEY (liik)
REFERENCES klliik (liik) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_maksetin_fkey FOREIGN KEY (maksetin)
REFERENCES maksetin (maksetin) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_maksja_fkey FOREIGN KEY (maksja)
REFERENCES firma1.klient (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_myygikood_fkey FOREIGN KEY (myygikood)
REFERENCES firma1.myygikoo (myygikood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_objekt2_fkey FOREIGN KEY (objekt2)
REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_objekt5_fkey FOREIGN KEY (objekt5)
REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_objekt7_fkey FOREIGN KEY (objekt7)
REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_omakseti_fkey FOREIGN KEY (omakseti)
REFERENCES maksetin (maksetin) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_p_kood_fkey FOREIGN KEY (p_kood)
REFERENCES pank (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_riik2_fkey FOREIGN KEY (riik2)
REFERENCES riik (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_username_fkey FOREIGN KEY (username)
REFERENCES kasutaja (kasutaja) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_email_check CHECK (rtrim(email::text) ~*
E'^[^@]*@(?:[^@]*\\.)?[a-z0-9_-]+\\.(?:a[defgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmoz]|e[ceghrst]|f[ijkmorx]|g[abdefhilmnpqrstuwy]|h[kmnrtu]|i[delnoqrst]|j[mop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrtwy]|qa|r[eouw]|s[abcdeghijklmnortvyz]|t[cdfghjkmnoprtvwz]|u[agkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|edu|com|net|org|gov|mil|info|biz|coop|museum|aero |name|pro|mobi|arpa)$'::text)
)
WITHOUT OIDS;


CREATE UNIQUE INDEX klient_nimi_unique_idx
ON firma1.klient USING btree (lower(nimi::text));


Server:

"PostgreSQL 8.1.3 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2
[FreeBSD] 20040728"

Client: ODBC driver in XP

"Andrus" <eetasoft@online.ee> writes:
> I have small database. However the following query takes 38 (!) seconds to
> run.
> How to speed it up (preferably not changing table structures but possibly
> creating indexes) ?

ANALYZE would probably help.

> " -> Seq Scan on konto dbkonto (cost=0.00..23.30 rows=1
> width=44) (actual time=0.017..1.390 rows=219 loops=1)"
> " Filter: (iseloom = 'A'::bpchar)"

Anytime you see a row estimate that far off about a simple single-column
condition, it means your statistics are out-of-date.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>> " -> Seq Scan on konto dbkonto (cost=0.00..23.30
>> rows=1
>> width=44) (actual time=0.017..1.390 rows=219 loops=1)"
>> " Filter: (iseloom = 'A'::bpchar)"
>
> Anytime you see a row estimate that far off about a simple single-column
> condition, it means your statistics are out-of-date.

Than you. I addded ANALYZE command and now query works fast.

I see autovacuum: processing database "mydb" messages in log file and I have

stats_start_collector = on
stats_row_level = on

in config file. Why statistics was out-of-date ?

Andrus.


My postgres.conf file (only uncommented settings are listed):

listen_addresses = '*'
max_connections = 40
shared_buffers = 1000
log_destination = 'stderr'
redirect_stderr = on # Enable capturing of stderr into log
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
log_rotation_age = 1440 # Automatic rotation of logfiles will
log_rotation_size = 10240 # Automatic rotation of logfiles will
log_min_error_statement = 'warning' # Values in order of increasing
severity:
silent_mode = on
log_line_prefix = "'%t %u %d %h %p %i %l %x %q'"
stats_start_collector = on
stats_row_level = on
autovacuum = on # enable autovacuum subprocess?
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting

"Andrus" <eetasoft@online.ee> writes:
> I see autovacuum: processing database "mydb" messages in log file and I have
> stats_start_collector = on
> stats_row_level = on
> in config file. Why statistics was out-of-date ?

The default autovac thresholds are not very aggressive; this table was
probably not large enough to get selected for analysis.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> The default autovac thresholds are not very aggressive; this table was
> probably not large enough to get selected for analysis.

Tom,

thank you.
Excellent.

Andrus.

On Mon, May 08, 2006 at 08:03:38PM +0300, Andrus wrote:
> > The default autovac thresholds are not very aggressive; this table was
> > probably not large enough to get selected for analysis.
>
> Tom,
>
> thank you.
> Excellent.

BTW, you might want to cut all the autovac thresholds in half; that's
what I typically do.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> BTW, you might want to cut all the autovac thresholds in half; that's
> what I typically do.

I added ANALYZE command to my procedure which creates and loads data to
postgres database
from other DBMS. This runs only onvce after installing my application. I
hope this is sufficient.
If default threshold is so conservative values I expect there is some reason
for it.

Andrus.

On Mon, May 08, 2006 at 08:36:42PM +0300, Andrus wrote:
> > BTW, you might want to cut all the autovac thresholds in half; that's
> > what I typically do.
>
> I added ANALYZE command to my procedure which creates and loads data to
> postgres database
> from other DBMS. This runs only onvce after installing my application. I
> hope this is sufficient.
> If default threshold is so conservative values I expect there is some reason
> for it.

The only reason for being so conservative that I'm aware of was that it
was a best guess. Everyone I've talked to cuts the defaults down by at
least a factor of 2, sometimes even more.

BTW, these parameters are already tweaked from what we started with in
contrib/pg_autovacuum. It would allow a table to grow to 2x larger than
it should be before vacuuming, as opposed to the 40% that the current
settings allow. But even there, is there any real reason you want to
have 40% bloat? To make matters worse, those settings ensure that all
but the smallest databases will suffer runaway bloat unless you bump up
the FSM settings.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> The only reason for being so conservative that I'm aware of was that it
> was a best guess. Everyone I've talked to cuts the defaults down by at
> least a factor of 2, sometimes even more.

Can we ask that Tom will change default values to 2 times smaller in 8.1.4 ?

> BTW, these parameters are already tweaked from what we started with in
> contrib/pg_autovacuum. It would allow a table to grow to 2x larger than
> it should be before vacuuming, as opposed to the 40% that the current
> settings allow. But even there, is there any real reason you want to
> have 40% bloat? To make matters worse, those settings ensure that all
> but the smallest databases will suffer runaway bloat unless you bump up
recprd> the FSM settings.

I created empty table konto and loaded more that 219 records to it during
database creation.
So it seems that if table grows from zero to more than 219 times larger then
it was still not processed.

Andrus.

On Monday 08 May 2006 14:10, Andrus wrote:
> > The only reason for being so conservative that I'm aware of was that it
> > was a best guess. Everyone I've talked to cuts the defaults down by at
> > least a factor of 2, sometimes even more.
>
> Can we ask that Tom will change default values to 2 times smaller in 8.1.4
> ?
>
> > BTW, these parameters are already tweaked from what we started with in
> > contrib/pg_autovacuum. It would allow a table to grow to 2x larger than
> > it should be before vacuuming, as opposed to the 40% that the current
> > settings allow. But even there, is there any real reason you want to
> > have 40% bloat? To make matters worse, those settings ensure that all
> > but the smallest databases will suffer runaway bloat unless you bump up
>
> recprd> the FSM settings.
>
> I created empty table konto and loaded more that 219 records to it during
> database creation.
> So it seems that if table grows from zero to more than 219 times larger
> then it was still not processed.

That's because you need at least 500 rows for analyze and 100 for a vacuum,
(autovacuum_vacuum_threshold = 1000, autovacuum_analyze_threshold = 500).

>
> Andrus.

jan

>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
--------------------------------------------------------------
Jan de Visser * * * * * * * * * * jdevisser@digitalfairway.com

* * * * * * * * Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match