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: [EMAIL PROTECTED]
                                                   WWW: http://www.sigaev.ru/

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

Reply via email to