Re: [HACKERS] Index creation takes for ever

On Mon, 01 Sep 2003 08:46:09 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:
>ohp(at)pyrenet(dot)fr writes:
>> it took 69 minutes to finish, 75% of this time was devoted to create 2
>> indexes on varchar(2) with value being 'O', 'N' or null;
>
>I still say it's either strcoll or qsort's fault.

If qsort is to blame, then maybe this patch could help. It sorts
equal key values on item pointer. And if it doesn't help index
creation speed, at least the resulting index has better correlation.

Test script:
CREATE TABLE t (i int NOT NULL, t text NOT NULL);
INSERT INTO t VALUES (1, 'lajshdflasjhdflajhsdfljhasdlfjhasdf');
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t VALUES (100, 's,dmfa.,smdn.famsndfamdnsbfmansdbf');
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
ANALYZE t;
CREATE INDEX t_i ON t(i);
SET enable_seqscan = 0;
SELECT ctid FROM t WHERE i=100 LIMIT 10;

Result without patch:
ctid
----------
(153,14)
(306,23)
(305,80)
(152,91)
(76,68)
(38,34)
(153,34)
(305,50)
(9,62)
(305,40)
(10 rows)

Result with patch:
ctid
--------
(0,5)
(0,10)
(0,15)
(0,20)
(0,25)
(0,30)
(0,35)
(0,40)
(0,45)
(0,50)
(10 rows)

For testing purposes I have made a second patch that provides a
boolean GUC variable sort_index. It is available here:
http://www.pivot.at/pg/23.test-IdxTupleSort.diff

Servus
Manfred