Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> writes: > OK. This is a highly distilled example that shows the behavior. > BEGIN; > CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b, > ''::text AS c from generate_series(1,500) AS x; > UPDATE foo SET c = 'foo' WHERE b = 'A' ; > CREATE INDEX foo_b_idx on foo (b); > [ and the rest of the transaction can't use that index ] OK, this is an artifact of the "HOT update" optimization. Before creating the index, you did updates on the table that would have been executed differently if the index had existed. When the index does get created, its entries for those updates are incomplete, so the index can't be used in transactions that could in principle see the unmodified rows. You could avoid this effect either by creating the index before you do any updates on the table, or by not wrapping the entire process into a single transaction. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
