Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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: datafix.qry
Description: application/octet-stream (2.4 KB)
Attachment: datafix-plan2.txt
Description: application/octet-stream (5.7 KB)
Attachment: datafix-plan1.txt
Description: application/octet-stream (4.0 KB)
Attachment: datafix-Action.txt
Description: application/octet-stream (892 bytes)

Responses

pgsql-performance by date

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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group