I have an app which imports a lot of data into a temporary table, does a number of updates, creates some indexes, and then does a bunch more updates and deletes, and then eventually inserts some of the columns from the transformed table into a permanent table. Things were not progressing in a performant manner - specifically, after creating an index on a column (INTEGER) that is unique, I expected statements like this to use an index scan: update foo set colA = 'some value' where indexed_colB = 'some other value' but according to the auto_explain module (yay!) the query plan (always) results in a sequential scan, despite only 1 row getting the update. In summary, the order goes like this: BEGIN; CREATE TEMPORARY TABLE foo ...; copy into foo .... UPDATE foo .... -- 4 or 5 times, updating perhaps 1/3 of the table all told CREATE INDEX ... -- twice - one index each for two columns ANALYZE foo; -- didn't seem to help UPDATE foo SET ... WHERE indexed_column_B = 'some value'; -- seq scan? Out of 10 million rows only one is updated! ... What might be going on here? -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
