Date: Mon, 22 Nov 2010 09:59:30 +0100
From: Martin Boese <boesemar@gmx.de>
To: pgsql-performance@postgresql.org
Subject: Slow SELECT on small table
Message-ID: <20101122095930.3e8b1d6c@leno>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="MP_/gjyB2EclpbADsTRvHmPXJAp"

--MP_/gjyB2EclpbADsTRvHmPXJAp
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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


--MP_/gjyB2EclpbADsTRvHmPXJAp
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename=postgresql-log.txt

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


--MP_/gjyB2EclpbADsTRvHmPXJAp--