Very low performance on table with only 298 rows

Поиск
Список
Период
Сортировка
От Andreas Joseph Krogh
Тема Very low performance on table with only 298 rows
Дата
Msg-id 200504141248.21783.andreak@officenet.no
обсуждение исходный текст
Ответы Re: Very low performance on table with only 298 rows  (Richard Huxton <dev@archonet.com>)
Re: Very low performance on table with only 298 rows  (PFC <lists@boutiquenumerique.com>)
Список pgsql-sql
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 |                                             |
------------------------+---------------------------------------------+


В списке pgsql-sql по дате отправления:

Предыдущее
От: Bruno Wolff III
Дата:
Сообщение: Re: max question
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: Very low performance on table with only 298 rows