Hello, I have a very large table that I'm not too fond of. I'm revising the design now.
Up until now its been insert only, storing tracking codes from incoming webtraffic. It has 8m rows It appears to insert fine, but simple updates using psql are hanging. update ONLY traffic_tracking2010 set src_content_type_id = 90 where id = 90322; I am also now trying to remove the constraints, this also hangs. alter table traffic_tracking2010 drop constraint traffic_tracking2010_src_content_type_id_fkey; thanks in advance for any advice. Table "public.traffic_tracking2010" Column | Type | Modifiers ---------------------+--------------------------+------------------------------------------------------------------- id | integer | not null default nextval('traffic_tracking2010_id_seq'::regclass) action_time | timestamp with time zone | not null user_id | integer | content_type_id | integer | object_id | integer | action_type | smallint | not null src_type | smallint | src_content_type_id | integer | src_object_id | integer | http_referrer | character varying(100) | search_term | character varying(50) | remote_addr | inet | not null Indexes: "traffic_tracking2010_pkey" PRIMARY KEY, btree (id) "traffic_tracking2010_content_type_id" btree (content_type_id) "traffic_tracking2010_src_content_type_id" btree (src_content_type_id) "traffic_tracking2010_user_id" btree (user_id) Foreign-key constraints: "traffic_tracking2010_content_type_id_fkey" FOREIGN KEY (content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED "traffic_tracking2010_src_content_type_id_fkey" FOREIGN KEY (src_content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED "traffic_tracking2010_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED This is generated by Django's ORM. The hang may be do having other clients connected, though I have tried doing the update when I know all tracking inserts are stopped. But the other client (the webapp) is still connected. based on this: http://postgresql.1045698.n5.nabble.com/slow-full-table-update-td2070754.html ns=> ANALYZE traffic_tracking2010; ANALYZE ns=> SELECT relpages, reltuples FROM pg_class WHERE relname = 'traffic_tracking2010'; relpages | reltuples ----------+------------- 99037 | 8.38355e+06 and I did vacuum it vacuum verbose traffic_tracking2010; INFO: vacuuming "public.traffic_tracking2010" INFO: scanned index "traffic_tracking2010_pkey" to remove 1057 row versions DETAIL: CPU 0.09s/0.37u sec elapsed 10.70 sec. INFO: scanned index "traffic_tracking2010_user_id" to remove 1057 row versions DETAIL: CPU 0.12s/0.30u sec elapsed 13.53 sec. INFO: scanned index "traffic_tracking2010_content_type_id" to remove 1057 row versions DETAIL: CPU 0.11s/0.28u sec elapsed 13.99 sec. INFO: scanned index "traffic_tracking2010_src_content_type_id" to remove 1057 row versions DETAIL: CPU 0.09s/0.26u sec elapsed 15.57 sec. INFO: "traffic_tracking2010": removed 1057 row versions in 535 pages DETAIL: CPU 0.01s/0.02u sec elapsed 2.83 sec. INFO: index "traffic_tracking2010_pkey" now contains 8315147 row versions in 22787 pages DETAIL: 1057 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "traffic_tracking2010_user_id" now contains 8315147 row versions in 29006 pages DETAIL: 1057 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "traffic_tracking2010_content_type_id" now contains 8315147 row versions in 28980 pages DETAIL: 1057 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "traffic_tracking2010_src_content_type_id" now contains 8315147 row versions in 28978 pages DETAIL: 1057 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "traffic_tracking2010": found 336 removable, 8315147 nonremovable row versions in 99035 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 25953 pages contain useful free space. 0 pages are entirely empty. CPU 0.78s/1.49u sec elapsed 100.43 sec. INFO: vacuuming "pg_toast.pg_toast_165961" INFO: index "pg_toast_165961_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.01 sec. INFO: "pg_toast_165961": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.06 sec.