pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow select, insert, update
5+ messages / 5 participants
[nested] [flat]
* Slow select, insert, update
@ 2004-08-09 16:54 Paul Langard <pjl@intercellsolutions.com>
0 siblings, 3 replies; 5+ messages in thread
From: Paul Langard @ 2004-08-09 16:54 UTC (permalink / raw)
To: pgsql-performance
Having trouble with one table (see time to count records below!).
Fairly new to postgres so any help much appreciated.
It only contains 9,106 records - as you can see from:
select count(id) from project
count
9106
1 row(s)
Total runtime: 45,778.813 ms
There are only 3 fields:
id
integer
nextval('id'::text)
projectnumber
text
description
text
There is one index:
id_project_ukey
CREATE UNIQUE INDEX id_project_ukey ON project USING btree (id)
... the database is regularly vaccuumed.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow select, insert, update
@ 2004-08-10 18:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paul Langard <pjl@intercellsolutions.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-08-10 18:18 UTC (permalink / raw)
To: Paul Langard <pjl@intercellsolutions.com>; +Cc: pgsql-performance
Paul Langard <pjl@intercellsolutions.com> writes:
> select count(id) from project
> count
> 9106
> 1 row(s)
> Total runtime: 45,778.813 ms
Yipes. The only explanation I can think of is tremendous table bloat.
What do you get from "vacuum verbose project" --- in particular, how
many pages in the table?
> ... the database is regularly vaccuumed.
Not regularly enough, perhaps ... or else you need to increase the free
space map size parameters. In any case you'll probably need to do one
round of "vacuum full" to get this table back within bounds.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow select, insert, update
@ 2004-08-10 18:56 Bill Montgomery <billm@lulu.com>
parent: Paul Langard <pjl@intercellsolutions.com>
2 siblings, 0 replies; 5+ messages in thread
From: Bill Montgomery @ 2004-08-10 18:56 UTC (permalink / raw)
To: Paul Langard <pjl@intercellsolutions.com>; +Cc: pgsql-performance
Paul,
Paul Langard wrote:
> Having trouble with one table (see time to count records below!).
>
> Fairly new to postgres so any help much appreciated.
>
> It only contains 9,106 records - as you can see from:
>
>
> select count(id) from project
>
> *count
> *9106
> 1 row(s)
> Total runtime: 45,778.813 ms
<snip>
> ... the database is regularly vaccuumed.
Have you tried doing a VACUUM FULL, CLUSTER, or drop/restore on the
table? This sounds symptomatic of a table with a bunch of dead tuples
not in the FSM (free space map). Only tuples in the FSM are reclaimed by
a regular VACUUM. If your FSM parameters in postgresql.conf are not big
enough for your ratio of UPDATE/DELETE operations to VACUUM frequency,
you will end up with dead tuples that will only be reclaimed by a VACUUM
FULL.
To prevent this problem in the future, look at increasing your FSM size
and possibly vacuuming more frequently or using pg_autovacuum.
Good Luck,
Bill Montgomery
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow select, insert, update
@ 2004-08-10 20:48 Doug McNaught <doug@mcnaught.org>
parent: Paul Langard <pjl@intercellsolutions.com>
2 siblings, 1 reply; 5+ messages in thread
From: Doug McNaught @ 2004-08-10 20:48 UTC (permalink / raw)
To: pgsql-performance
Paul Langard <pjl@intercellsolutions.com> writes:
> Having trouble with one table (see time to count records below!).
>
> Fairly new to postgres so any help much appreciated.
>
> It only contains 9,106 records - as you can see from:
>
>
> select count(id) from project
>
> count
> 9106
> 1 row(s)
> Total runtime: 45,778.813 ms
> ... the database is regularly vaccuumed.
Hmm. You might try a VACUUM FULL and a REINDEX on the table (you
don't say what version you are running--REINDEX is sometimes needed on
7.3 and below).
Also, use EXPLAIN ANALYZE on your query and post the result--that's
helpful diagnostic information.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow select, insert, update
@ 2004-08-10 21:44 Litao Wu <litaowu@yahoo.com>
parent: Doug McNaught <doug@mcnaught.org>
0 siblings, 0 replies; 5+ messages in thread
From: Litao Wu @ 2004-08-10 21:44 UTC (permalink / raw)
To: Doug McNaught <doug@mcnaught.org>; pgsql-performance
Does that mean reindex is not needed
for PG version 7.4?
In what kind situations under PG 7.4,
reindex is worthwhile?
Thanks,
Here is doc from 7.3:
PostgreSQL is unable to reuse B-tree index pages in
certain cases. The problem is that if indexed rows are
deleted, those index pages can only be reused by rows
with similar values. For example, if indexed rows are
deleted and newly inserted/updated rows have much
higher values, the new rows can't use the index space
made available by the deleted rows. Instead, such new
rows must be placed on new index pages. In such cases,
disk space used by the index will grow indefinitely,
even if VACUUM is run frequently.
As a solution, you can use the REINDEX command
periodically to discard pages used by deleted rows.
There is also contrib/reindexdb which can reindex an
entire database.
The counterpart of 7.4 is:
In some situations it is worthwhile to rebuild indexes
periodically with the REINDEX command. (There is also
contrib/reindexdb which can reindex an entire
database.) However, PostgreSQL 7.4 has substantially
reduced the need for this activity compared to earlier
releases.
--- Doug McNaught <doug@mcnaught.org> wrote:
> Paul Langard <pjl@intercellsolutions.com> writes:
>
> > Having trouble with one table (see time to count
> records below!).
> >
> > Fairly new to postgres so any help much
> appreciated.
> >
> > It only contains 9,106 records - as you can see
> from:
> >
> >
> > select count(id) from project
> >
> > count
> > 9106
> > 1 row(s)
> > Total runtime: 45,778.813 ms
>
> > ... the database is regularly vaccuumed.
>
> Hmm. You might try a VACUUM FULL and a REINDEX on
> the table (you
> don't say what version you are running--REINDEX is
> sometimes needed on
> 7.3 and below).
>
> Also, use EXPLAIN ANALYZE on your query and post the
> result--that's
> helpful diagnostic information.
>
> -Doug
> --
> Let us cross over the river, and rest under the
> shade of the trees.
> --T. J. Jackson, 1863
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-08-10 21:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-09 16:54 Slow select, insert, update Paul Langard <pjl@intercellsolutions.com>
2004-08-10 18:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-10 18:56 ` Bill Montgomery <billm@lulu.com>
2004-08-10 20:48 ` Doug McNaught <doug@mcnaught.org>
2004-08-10 21:44 ` Litao Wu <litaowu@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox