From: | Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | temporary tables, indexes, and query plans |
Date: | 2010-10-27 17:29:44 |
Message-ID: | AANLkTinyFRBAR52m7a43-aUuxpLzwirtp5FWwfOYP2vT@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mladen Gogala | 2010-10-27 17:44:24 | Re: temporary tables, indexes, and query plans |
Previous Message | André Volpato | 2010-10-27 17:05:09 | Re: AIX slow buffer reads |