From: Martin Boese <boesemar@gmx.de>
To: pgsql-performance@postgresql.org
Subject: Slow SELECT on small table
Date: Mon, 22 Nov 2010 09:59:30 +0100
Message-ID: <20101122095930.3e8b1d6c@leno> (raw)
Hi,
I am using Postgresql: 9.01, PostGIS 1.5 on FreeBSD 7.0. I have at
least one table on which SELECT's turn terribly slow from time to time.
This happened at least three times, also on version 8.4.
The table has only ~1400 rows. A count(*) takes more than 70 seconds.
Other tables are fast as usual.
When this happens I can also see my system's disks are suffering.
'systat -vm' shows 100% disk load at ~4MB/sec data rates.
A simple VACUUM does *not* fix it, a VACUUM FULL however does. See the
textfile attached.
My postgresql.conf is untouched as per distribution.
Can someone hint me how I can troubleshoot this problem?
Thanks!
Martin
Attachments:postgresql-log.txt (text/plain, 5.9K) download
mbms=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.1 on amd64-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit
(1 row)
mbms=# EXPLAIN ANALYZE select count(*) from circuit;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=22213.20..22213.21 rows=1 width=0) (actual time=72847.131..72847.133 rows=1 loops=1)
-> Seq Scan on circuit (cost=0.00..22209.56 rows=1456 width=0) (actual time=15527.799..72844.633 rows=1456 loops=1)
Total runtime: 72922.484 ms
(3 rows)
mbms=# VACUUM FULL VERBOSE ANALYZE ;
(..snip..)
INFO: vacuuming "public.circuit"
INFO: analyzing "public.circuit"
INFO: "circuit": scanned 41 of 41 pages, containing 1456 live rows and 0 dead rows; 1456 rows in sample, 1456 estimated total rows
VACUUM
mbms=# EXPLAIN ANALYZE select count(*) from circuit;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=59.20..59.21 rows=1 width=0) (actual time=4.416..4.418 rows=1 loops=1)
-> Seq Scan on circuit (cost=0.00..55.56 rows=1456 width=0) (actual time=0.013..2.302 rows=1456 loops=1)
Total runtime: 4.470 ms
(3 rows)
mbms=# \d circuit
Table "public.circuit"
Column | Type | Modifiers
-----------------------------+-----------------------------+------------------------------------------------------
id | integer | not null default nextval('circuit_id_seq'::regclass)
router_id | integer |
customer_id | integer |
site | character varying(256) |
service | character varying(48) |
last_ifdescr | character varying(256) |
last_ifalias | character varying(256) |
bandwidth_ul | integer | default 0
bandwidth_dl | integer | default 0
last_seen | timestamp without time zone |
last_ifindex | integer | default 0
termination_point_id | integer |
last_adminstatus | smallint | default 1
status | character(1) | default 'A'::bpchar
location | geometry |
circuit_type | character(1) | default 'R'::bpchar
p_local | character varying(256) |
p_remote | character varying(256) |
p_technology | character varying(64) |
x_provider | character varying(256) |
x_provider_reference | character varying(128) |
termination_point_reference | character varying(64) |
is_monitored | boolean | default false
temp_config | boolean | default false
activation_date | timestamp without time zone |
s_last_did | character varying(64) |
td_cache_class | character varying(128) |
td_cache_id | integer |
td_cache_lastupdate | timestamp without time zone |
commercial_service_id | integer |
Indexes:
"circuit_id_key" UNIQUE, btree (id)
"id_circuit_status" btree (status)
"idx_circuit_customer_id" btree (customer_id)
"idx_circuit_site" btree (site)
Check constraints:
"enforce_dims_location" CHECK (ndims(location) = 2)
"enforce_geotype_location" CHECK (geometrytype(location) = 'POINT'::text OR location IS NULL)
"enforce_srid_location" CHECK (srid(location) = 4326)
Foreign-key constraints:
"circuit_commercial_service_id_fkey" FOREIGN KEY (commercial_service_id) REFERENCES commercial_services(id) ON UPDATE CASCADE ON DELETE CASCADE
"circuit_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(id) ON UPDATE CASCADE ON DELETE CASCADE
"circuit_router_id_fkey" FOREIGN KEY (router_id) REFERENCES router(id) ON UPDATE CASCADE ON DELETE CASCADE
"circuit_termination_point_id_fkey" FOREIGN KEY (termination_point_id) REFERENCES termination_point(id) ON UPDATE CASCADE ON DELETE SET NULL
Referenced by:
TABLE "call_log" CONSTRAINT "call_log_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "circuit_states" CONSTRAINT "circuit_states_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "circuit_traffic" CONSTRAINT "circuit_traffic_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "cpe" CONSTRAINT "cpe_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "ip_networks" CONSTRAINT "ip_networks_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "monitoring_settings" CONSTRAINT "monitoring_settings_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "stock" CONSTRAINT "stock_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE RESTRICT
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: boesemar@gmx.de
Subject: Re: Slow SELECT on small table
In-Reply-To: <20101122095930.3e8b1d6c@leno>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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