As you can see, we have duplicates within the table (heap) of a primary
key value. The index itself only references one of these tuples.
Nearly all data inserted into this table is wrapped in a subtransaction,
and is created a single tuple per subtransaction. About 20% of entries
are duplicate, so we catch the UNIQUE VIOLATION and restore to the
savepoint.
I did keep a copy of the table. Compressed it is about 24MB.
After trying everything below, I also gave it a run with vacuum full. It
did not change the output.
ssdb=# select version();
version
---------------------------------------------------------------------------
PostgreSQL 8.0.3 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC)
3.4.2
(1 row)
ssdb=# \d feature_keyword_supply_google
Table "public.feature_keyword_supply_google"
Column | Type |
Modifiers
----------------------------------------------+-----------------------+-----------
account_instance_id | integer |
not null
keyword_id | integer |
not null
feature_keyword_supply_google_score | natural_number |
not null
feature_keyword_supply_google_last_collected | ss_timestamp_recorded |
not null
Indexes:
"feature_keyword_supply_google_pkey" PRIMARY KEY, btree
(account_instance_id, keyword_id)
Foreign-key constraints:
"feature_keyword_supply_google_account_instance_id_fkey" FOREIGN KEY
(account_instance_id, keyword_id) REFERENCES
feature_keyword(account_instance_id, keyword_id) ON UPDATE CASCADE ON
DELETE CASCADE
ssdb=# set enable_indexscan TO off;
SET
ssdb=# select ctid, xmin, cmin, xmax, account_instance_id = 11916,
keyword_id = 1985374 from feature_keyword_supply_google where keyword_id
= 1985374 and account_instance_id = 11916;
ctid | xmin | cmin | xmax | ?column? | ?column?
-----------+-----------+------+-----------+----------+----------
(4277,60) | 506766160 | 3593 | 744608069 | t | t
(4277,72) | 397750949 | 4828 | 506766160 | t | t
(2 rows)
ssdb=# set enable_indexscan TO on;
SET
ssdb=# select ctid, xmin, cmin, xmax, account_instance_id = 11916,
keyword_id = 1985374 from feature_keyword_supply_google where keyword_id
= 1985374 and account_instance_id = 11916;
ctid | xmin | cmin | xmax | ?column? | ?column?
-----------+-----------+------+-----------+----------+----------
(4277,60) | 506766160 | 3593 | 744608069 | t | t
(1 row)
ssdb=# vacuum feature_keyword_supply_google;
VACUUM
ssdb=# set enable_indexscan = off;
SET
ssdb=# select ctid, xmin, cmin, xmax, account_instance_id = 11916,
keyword_id = 1985374 from feature_keyword_supply_google where keyword_id
= 1985374 and account_instance_id = 11916;
ctid | xmin | cmin | xmax | ?column? | ?column?
-----------+-----------+------+-----------+----------+----------
(4277,60) | 506766160 | 3593 | 744608069 | t | t
(4277,72) | 397750949 | 4828 | 506766160 | t | t
(2 rows)
[EMAIL PROTECTED] rbt]# pg_controldata --version
pg_controldata (PostgreSQL) 8.0.3
[EMAIL PROTECTED] rbt]# pg_controldata /var/opt/pgsql/data
pg_control version number: 74
Catalog version number: 200411041
Database system identifier: 4769850195962887551
Database cluster state: in production
pg_control last modified: Wed Nov 09 10:10:26 2005
Current log file ID: 860
Next log file segment: 170
Latest checkpoint location: 35C/A14C8D60
Prior checkpoint location: 35C/908D6470
Latest checkpoint's REDO location: 35C/A1440E20
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 745383235
Latest checkpoint's NextOID: 30513944
Time of latest checkpoint: Wed Nov 09 09:42:53 2005
Database block size: 8192
Blocks per segment of large relation: 131072
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum number of function arguments: 32
Date/time type storage: floating-point numbers
Maximum length of locale name: 128
LC_COLLATE: en_US
LC_CTYPE: en_US
--
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
