On 10/27/2010 1:29 PM, Jon Nelson wrote:
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?
How big is your default statistics target? The default is rather small,
it doesn't produce very good or usable histograms.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
