[PERFORM] Query runs 38 seconds for small database!
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
Re: [PERFORM] Query runs 38 seconds for small database!
Andrus [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Query runs 38 seconds for small database!
- 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 ---(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
Re: [PERFORM] Query runs 38 seconds for small database!
Andrus [EMAIL PROTECTED] 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
Re: [PERFORM] Query runs 38 seconds for small database!
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Query runs 38 seconds for small database!
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Query runs 38 seconds for small database!
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 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
Re: [PERFORM] Query runs 38 seconds for small database!
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Query runs 38 seconds for small database!
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 [EMAIL PROTECTED] 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
Re: [PERFORM] Query runs 38 seconds for small database!
Jan de Visser [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Query runs 38 seconds for small database!
Tom Lane wrote: Jan de Visser [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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
