From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-hackers@postgresql.org
To: pgsql-performance@postgresql.org
Subject: temporary indexes
Date: Tue, 28 Feb 2006 09:44:08 -0600
Message-ID: <44041B68.EE98.0025.0@wicourts.gov> (raw)
Just a "wouldn't it be nice if" sort of feature request. I'm not sure
how practical it is.
Someone in our organization wrote a data fix query, which has sort of
odd logic, but it does what they need. The problem is that it ran for
14 hours in a test against a copy of the data. I looked at it and
figured it could do better with an extra index. The index took five
minutes to build, and the run time for the query dropped to five
minutes. The index is not needed for production, so it was then
dropped.
It struck me that it would be outstanding if the planner could
recognize this sort of situation, and build a temporary index based on
the snapshot of the data visible to the transaction. It seems to me
that the obvious downside of this would be the explosion in the number
of permutations the planner would need to examine -- based not just on
what indexes ARE there, but which ones it could build. At a minimum,
there would need to be a cost threshold below which it would not even
consider the option. (In this case, as long as the optimizer spent less
than 13 hours and 50 minutes considering its options, we would have come
out ahead.)
I'm not sure the details of this particular incident are that relevant,
but I've attached the query and the two plans.
-Kevin
Attachments:
datafix-Action.txt (application/octet-stream, 892B) download
datafix-plan1.txt (application/octet-stream, 4.0K) download
datafix-plan2.txt (application/octet-stream, 5.7K) download
datafix.qry (application/octet-stream, 2.4K) download
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: Kevin.Grittner@wicourts.gov, pgsql-hackers@postgresql.org
Subject: Re: temporary indexes
In-Reply-To: <44041B68.EE98.0025.0@wicourts.gov>
* 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