INSERT/UPDATE statements sometimes choosing not to use PK index?

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?

Responses

Browse pgsql-general by date

  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