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
> In summary, the order goes like this:
> 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.
Sr. Oracle DBA
New York, NY 10036
The Leader in Integrated Media Intelligence Solutions
In response to
pgsql-performance by date
|Next:||From: Scott Carey||Date: 2010-10-27 17:48:35|
|Subject: Re: Postgres insert performance and storage requirement
compared to Oracle|
|Previous:||From: Jon Nelson||Date: 2010-10-27 17:29:44|
|Subject: temporary tables, indexes, and query plans|