Обсуждение: Very low performance on table with only 298 rows
nbeweb=> EXPLAIN select count(*) from onp_web_index; QUERY PLAN
-----------------------------------------------------------------------------
Hi all.
I have a problem with a table which only has 298 rows in it. A select count(*)
takes forever and CTRL-C also takes a long time canceling the query. There
are only 298 rows in the table when count(*) returns. How come it takes such
a long time counting only 298 rows? Here is EXPLAIN ANALYZE:
nbeweb=> EXPLAIN ANALYZE select count(*) from onp_web_index;
QUERYPLAN
-----------------------------------------------------------------------------------------------------------------------------------Aggregate
(cost=205759.52..205759.52 rows=1 width=0) (actual
time=179748.910..179748.913 rows=1 loops=1) -> Seq Scan on onp_web_index (cost=0.00..205455.41 rows=121641 width=0)
(actual time=179735.956..179747.821 rows=298 loops=1)Total runtime: 179748.993 ms
(3 rows)
Here is the schema:
nbeweb=> \d onp_web_index; Table "public.onp_web_index" Column | Type
| Modifiers
-------------+-------------------+---------------------------------------------------------------id | integer
| not null default
nextval('public.onp_web_index_id_seq'::text)starturl_id | integer | not nulllang_id | integer |
notnullurl_host | character varying | not nullurl_path | character varying | not nulltitle | character
varying| not nullcontent | tsvector | not nullplain_text | character varying | not null
Indexes: "onp_web_index_pkey" primary key, btree (id) "onp_web_index_url_host_key" unique, btree (url_host,
url_path) "onp_web_index_idx" gist (content)
Foreign-key constraints: "$2" FOREIGN KEY (lang_id) REFERENCES code(id) "$1" FOREIGN KEY (starturl_id) REFERENCES
onp_web_index_starturl(id)ON
DELETE CASCADE
Any hints on how I can "fix" this lousy performance?
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Hoffsveien 17 | know how to do a thing and to watch |
PO. Box 425 Skøyen | somebody else doing it wrong, without |
0213 Oslo | comment. |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
Andreas Joseph Krogh wrote: > nbeweb=> EXPLAIN select count(*) from onp_web_index; > QUERY PLAN > ----------------------------------------------------------------------------- > Hi all. > I have a problem with a table which only has 298 rows in it. A select count(*) > takes forever and CTRL-C also takes a long time canceling the query. There > are only 298 rows in the table when count(*) returns. How come it takes such > a long time counting only 298 rows? Here is EXPLAIN ANALYZE: > > nbeweb=> EXPLAIN ANALYZE select count(*) from onp_web_index; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=205759.52..205759.52 rows=1 width=0) (actual > time=179748.910..179748.913 rows=1 loops=1) > -> Seq Scan on onp_web_index (cost=0.00..205455.41 rows=121641 width=0) > (actual time=179735.956..179747.821 rows=298 loops=1) > Total runtime: 179748.993 ms Why does PG think you have 121641 rows in this table? How often do you vacuum it? How often do you analyse it? Does "vacuum full verbose" show a lot of dead rows being removed? I'm suspecting a *lot* of dead rows need to be removed. -- Richard Huxton Archonet Ltd
On Thursday 14 April 2005 13:28, Richard Huxton wrote: > Andreas Joseph Krogh wrote: > > nbeweb=> EXPLAIN select count(*) from onp_web_index; > > QUERY PLAN > > ------------------------------------------------------------------------- > >---- Hi all. > > I have a problem with a table which only has 298 rows in it. A select > > count(*) takes forever and CTRL-C also takes a long time canceling the > > query. There are only 298 rows in the table when count(*) returns. How > > come it takes such a long time counting only 298 rows? Here is EXPLAIN > > ANALYZE: > > > > nbeweb=> EXPLAIN ANALYZE select count(*) from onp_web_index; > > QUERY PLAN > > ------------------------------------------------------------------------- > >---------------------------------------------------------- Aggregate > > (cost=205759.52..205759.52 rows=1 width=0) (actual > > time=179748.910..179748.913 rows=1 loops=1) > > -> Seq Scan on onp_web_index (cost=0.00..205455.41 rows=121641 > > width=0) (actual time=179735.956..179747.821 rows=298 loops=1) > > Total runtime: 179748.993 ms > > Why does PG think you have 121641 rows in this table? > How often do you vacuum it? > How often do you analyse it? > Does "vacuum full verbose" show a lot of dead rows being removed? > > I'm suspecting a *lot* of dead rows need to be removed. I vacuum the whole database every sunday with the command: VACUUM ANALYZE Do I need to issue a VACUUM FULL? I've never VACUUM FULL'ed it.. I have other much larger tables which take lot less time to count(*). Cant it be that this table is hosed somehow? Note: I have a column of type tsvector in there, cat it be that this column causes some unexpected trouble? I'm issuing a VACUUM FULL verbose now... -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment. | NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
> nbeweb=> EXPLAIN ANALYZE select count(*) from onp_web_index; > Total runtime: 179748.993 ms WOW.It's possible your table is bloated.What version of PG are you using ? VACUUM FULL ANALYZE VERBOSE onp_web_index if it solves your problem, good, else post the results from that command.