From: Eric Comeau <ecomeau@signiant.com>
To: pgsql-performance@postgresql.org
Subject: How to get FK to use new index without restarting the database
Date: Thu, 16 Dec 2010 07:12:03 -0500
Message-ID: <iecvmj$1j93$1@news.hub.org> (raw)
Is there a way force the db to re-evaluate its execution plan for a FK
without bouncing the DB?
PostgreSQL 8.1.17
In our latest release our developers have implemented some new foreign
keys but forgot to create indexes on these keys.
The problem surfaced at one of our client installs where a maintenance
DELETE query was running for over 24 hrs. We have since then identified
the missing indexes and have sent the client a script to create them,
but in our testing we could not been able to get postgres to use the new
index for the FK cascade delete without bouncing the database.
Here is an example of an added fk but missing index....
ALTER TABLE scheduled_job_arg ADD CONSTRAINT sjr_scheduled_job_id_fk
FOREIGN KEY (scheduled_job_id) REFERENCES scheduled_job (id)
ON UPDATE CASCADE ON DELETE CASCADE;
Thanks in Advance,
Eric
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ecomeau@signiant.com
Subject: Re: How to get FK to use new index without restarting the database
In-Reply-To: <iecvmj$1j93$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox