From: | Matt <bsg075(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | INSERT/UPDATE statements sometimes choosing not to use PK index? |
Date: | 2013-10-29 06:16:43 |
Message-ID: | CAF=YoAK9cNieQ0R2PdNZHr0=KWDtiMYjtiGf_jhWtPf6yy8gxw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a relatively simple data load script, which upserts (UPDATE existing
rows, INSERT new rows), which should be supported by the primary key index,
the only index on this table:
UPDATE destination SET ... FROM staging WHERE staging.pk =
destination.pk;
INSERT INTO destination SELECT ... FROM staging WHERE NOT EXISTS(SELECT
* FROM destination WHERE pk = staging.pk);
In most cases, EXPLAIN and runtime tell me the index is utilized. However,
sometime on back to back runs (5 minute intervals) the runtime explodes and
EXPLAIN shows me that the PK index is not used, as both the staging table
and destination table a sequentially scanned. The source data maintains the
same pattern, and this load job is the only write activity in this
particular database.
A left anti-join in the above pseudo-query explains the same, and seems to
make the problem occur more frequently.
What could cause PostgreSQL (9.1) to stop using an index in a case like
this, when it normally and expectedly uses it?
From | Date | Subject | |
---|---|---|---|
Next Message | Jov | 2013-10-29 06:34:40 | copy 5.3000000000e+01 to bigint column error while Insert ok |
Previous Message | Bill Moran | 2013-10-29 03:45:46 | Re: Work table |