Re: still gin index creation takes forever

[Teodor Sigaev <teodor@xxxxxxxxx>]

> Yeah, I'm not convinced either.  Still, Teodor's theory should be easily
> testable: set synchronize_seqscans to FALSE and see if the problem goes
> away.

Test suit to reproduce the problem:
	DROP TABLE IF EXISTS foo;
	DROP TABLE IF EXISTS footmp;

	CREATE OR REPLACE FUNCTION gen_array()
	RETURNS _int4 AS
	$$
	    SELECT ARRAY(
	        SELECT (random()*1000)::int
	        FROM generate_series(1,10+(random()*90)::int)
	    )
	$$
	LANGUAGE SQL VOLATILE;

	SELECT gen_array() AS v INTO foo FROM generate_series(1,100000);

	VACUUM ANALYZE foo;

	CREATE INDEX fooidx ON foo USING gin (v);
	DROP INDEX fooidx;

	SELECT * INTO footmp FROM foo LIMIT 90000;

	CREATE INDEX fooidx ON foo USING gin (v);
	DROP INDEX fooidx;

On my notebook with HEAD and default postgresql.conf it produce (show only 
interesting part):

   postgres=# CREATE INDEX fooidx ON foo USING gin (v);
   Time: 14961,409 ms
   postgres=# SELECT * INTO footmp FROM foo LIMIT 90000;
   postgres=# CREATE INDEX fooidx ON foo USING gin (v);
   LOG:  checkpoints are occurring too frequently (12 seconds apart)	
   HINT:  Consider increasing the configuration parameter "checkpoint_segments".
   LOG:  checkpoints are occurring too frequently (8 seconds apart)
   HINT:  Consider increasing the configuration parameter "checkpoint_segments".
   LOG:  checkpoints are occurring too frequently (7 seconds apart)
   HINT:  Consider increasing the configuration parameter "checkpoint_segments".
   LOG:  checkpoints are occurring too frequently (10 seconds apart)
   HINT:  Consider increasing the configuration parameter "checkpoint_segments".
   LOG:  checkpoints are occurring too frequently (8 seconds apart)
   HINT:  Consider increasing the configuration parameter "checkpoint_segments".
   CREATE INDEX
   Time: 56286,507 ms

So, time for creation is 4-time bigger after select.
Without "SELECT * INTO footmp FROM foo LIMIT 90000;":
   postgres=# CREATE INDEX fooidx ON foo USING gin (v);
   CREATE INDEX
   Time: 13894,050 ms
   postgres=# CREATE INDEX fooidx ON foo USING gin (v);
   LOG:  checkpoints are occurring too frequently (14 seconds apart)
   HINT:  Consider increasing the configuration parameter "checkpoint_segments".
   CREATE INDEX
   Time: 15087,348 ms

Near to the same time.

With   synchronize_seqscans = off and SELECT:
   postgres=# CREATE INDEX fooidx ON foo USING gin (v);
   CREATE INDEX
   Time: 14452,024 ms
   postgres=# SELECT * INTO footmp FROM foo LIMIT 90000;
   postgres=# CREATE INDEX fooidx ON foo USING gin (v);
   LOG:  checkpoints are occurring too frequently (16 seconds apart)
   HINT:  Consider increasing the configuration parameter "checkpoint_segments".
   CREATE INDEX
   Time: 14557,750 ms

Again, near to the same time.


--
Teodor Sigaev                                   E-mail: teodor@xxxxxxxxx
                                                   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general