temporary indexes

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-hackers(at)postgresql(dot)org>,<pgsql-performance(at)postgresql(dot)org>
Subject: temporary indexes
Date: 2006-02-28 15:44:08
Message-ID: 44041B68.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

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

Attachment Content-Type Size
datafix-Action.txt application/octet-stream 892 bytes
datafix-plan1.txt application/octet-stream 4.0 KB
datafix-plan2.txt application/octet-stream 5.7 KB
datafix.qry application/octet-stream 2.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-02-28 15:44:32 Re: character encoding in StartupMessage
Previous Message Tom Lane 2006-02-28 15:35:53 Re: new feature: LDAP database name resolution

Browse pgsql-performance by date

  From Date Subject
Next Message Jim C. Nasby 2006-02-28 16:45:15 Re: temporary indexes
Previous Message Ron 2006-02-28 13:40:56 Re: Different disks for xlogs and data