Skip site navigation (1) Skip section navigation (2)

Re: temporary tables, indexes, and query plans

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: temporary tables, indexes, and query plans
Date: 2010-10-27 17:44:24
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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:
> 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
The Leader in Integrated Media Intelligence Solutions

In response to


pgsql-performance by date

Next:From: Scott CareyDate: 2010-10-27 17:48:35
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Previous:From: Jon NelsonDate: 2010-10-27 17:29:44
Subject: temporary tables, indexes, and query plans

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group