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: 4CC864F8.3060801@vmsinfo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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:
>
> 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

In response to

Responses

Browse pgsql-performance by date

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