Hi, All
I faced this odd situation when I was migrating my data from 8.3.7 to
8.4.1. After setting up instance, applying schema dump w/o and indexes
and loading data I was trying to create this indexes and got a lot of
multiply messages "picksplit method for column 2 of index ... doesn't
support secondary split" after each gist index containing 2+ columns.
Finally I got this message "index row requires 10440 bytes, maximum
size is 8191" after creation of one complex index.
Test-case:
1. Install PG 8.4.1 + btree_gist, set log_statement = 'all', create test_db
2. Create test table
CREATE TABLE test_table
( obj_id bigint NOT NULL, obj_status_did smallint NOT NULL DEFAULT 5, obj_created timestamp with time zone NOT NULL
DEFAULTnow(), obj_main_pic_obj_id bigint, obj_tsvector tsvector NOT NULL DEFAULT ''::tsvector, person_photo_is_best
booleanNOT NULL DEFAULT false, person_vislvl smallint NOT NULL DEFAULT 9, CONSTRAINT pk_test_table PRIMARY KEY
(obj_id)
);
3. Load a data into the table
Data dump is here http://drop.io/rdccygi (it was created with COPY
(SELECT...) TO '...' using psql from 8.3.7)
COPY test_table FROM '/tmp/data.dump';
4. Try to create this indexes
CREATE INDEX i_test_table__created_tsvector ON test_table USING gist
(obj_created, obj_tsvector) WHERE obj_status_did = 1;
CREATE INDEX i_test_table__tsvector_vislvl_by_photo_created ON
test_table USING btree (obj_tsvector, person_vislvl,
(COALESCE(person_photo_is_best::integer, 0)) DESC,
sign(COALESCE(obj_main_pic_obj_id, 0::bigint)::double precision) DESC,
obj_created DESC) WHERE obj_status_did = 1;
And you will see something like this http://drop.io/5tla8sg
p.s. One thing I have forgotten to write - I tried it on Ubuntu 9.04,
PG was built from sources.
--
Regards,
Sergey Konoplev