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.


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 DEFAULT now(),
  obj_main_pic_obj_id bigint,
  obj_tsvector tsvector NOT NULL DEFAULT ''::tsvector,
  person_photo_is_best boolean NOT 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.

Sergey Konoplev

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to