Thread: Query runs 38 seconds for small database!

Query runs 38 seconds for small database!

From
"Andrus"
Date:
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



Re: Query runs 38 seconds for small database!

From
Tom Lane
Date:
"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

Re: Query runs 38 seconds for small database!

From
"Andrus"
Date:
>> "                    ->  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



Re: Query runs 38 seconds for small database!

From
Tom Lane
Date:
"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

Re: Query runs 38 seconds for small database!

From
"Andrus"
Date:
> 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.



Re: Query runs 38 seconds for small database!

From
"Jim C. Nasby"
Date:
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

Re: Query runs 38 seconds for small database!

From
"Andrus"
Date:
> 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.



Re: Query runs 38 seconds for small database!

From
"Jim C. Nasby"
Date:
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

Re: Query runs 38 seconds for small database!

From
"Andrus"
Date:
> 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.



Re: Query runs 38 seconds for small database!

From
Jan de Visser
Date:
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!
--------------------------------------------------------------

Re: Query runs 38 seconds for small database!

From
Tom Lane
Date:
Jan de Visser <jdevisser@digitalfairway.com> writes:
> On Monday 08 May 2006 14:10, Andrus wrote:
>> 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).

This crystallizes something that's been bothering me for awhile,
actually: why do the "threshold" variables exist at all?  If we took
them out, or at least made their default values zero, then the autovac
criteria would simply be "vacuum or analyze if at least X% of the table
has changed" (where X is set by the "scale_factor" variables).  Which
seems intuitively reasonable.  As it stands, the thresholds seem to bias
autovac against ever touching small tables at all ... but, as this
example demonstrates, a fairly small table can still kill your query
performance if the planner knows nothing about it.

            regards, tom lane

Re: Query runs 38 seconds for small database!

From
Alvaro Herrera
Date:
Tom Lane wrote:
> Jan de Visser <jdevisser@digitalfairway.com> writes:
> > On Monday 08 May 2006 14:10, Andrus wrote:
> >> 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).
>
> This crystallizes something that's been bothering me for awhile,
> actually: why do the "threshold" variables exist at all?

Matthew would know about that -- he invented them.  I take no
responsability :-)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support