Thread: Force specific index disuse
Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like to run EXPLAIN and do timing tests on the queries to see the impact of not having that index available and rewrite the query to efficiently use other indexes if necessary. Cheers, Steve
On 5/20/14, 1:38 PM, Steve Crawford wrote: > Is there a way to force a specific index to be removed from > consideration in planning a single query? hi Steve, What is the query? Or at least a sanitized but complete version? Thanks, Seamus PS. I've had luck "hinting" with OFFSET 0 but it might not help in your use case. http://seamusabshere.github.io/2013/03/29/hinting-postgres-and-mysql-with-offset-and-limit/ > Specifically, on a 60-million-row table I have an index that is a > candidate for removal. I have identified the sets of nightly queries > that use the index but before dropping it I would like to run EXPLAIN > and do timing tests on the queries to see the impact of not having that > index available and rewrite the query to efficiently use other indexes > if necessary. -- Seamus Abshere, SCEA +598 99 54 99 54 https://github.com/seamusabshere
Steve Crawford wrote: > Is there a way to force a specific index to be removed from > consideration in planning a single query? > > Specifically, on a 60-million-row table I have an index that is a > candidate for removal. I have identified the sets of nightly queries > that use the index but before dropping it I would like to run > EXPLAIN and do timing tests on the queries to see the impact of not > having that index available and rewrite the query to efficiently use > other indexes if necessary. If you can afford to lock the table for a while, the easiest is BEGIN; DROP INDEX bothersome_idx; EXPLAIN your_query; ROLLBACK; -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 05/20/2014 09:44 AM, Seamus Abshere wrote: > On 5/20/14, 1:38 PM, Steve Crawford wrote: >> Is there a way to force a specific index to be removed from >> consideration in planning a single query? > > hi Steve, > > What is the query? Or at least a sanitized but complete version? I've now resolved the issue with the one update query I was investigating this morning. But this involved building a test where I removed the index then ran explain and timed the query. The question is actually general as I anticipate reviewing the benefit of dropping more indexes and it would be much more quick and convenient to do something akin to: begin; disable index foodex; explain update bar set baz ....; commit; I'm not sure what would be involved in adding this. It seems that simply hiding an index from the planner would be all that is necessary but I'm sure there are, as always, subtleties. Cheers, Steve
On 05/20/2014 10:44 AM, Alvaro Herrera wrote: > Steve Crawford wrote: >> Is there a way to force a specific index to be removed from >> consideration in planning a single query? >> >> Specifically, on a 60-million-row table I have an index that is a >> candidate for removal. I have identified the sets of nightly queries >> that use the index but before dropping it I would like to run >> EXPLAIN and do timing tests on the queries to see the impact of not >> having that index available and rewrite the query to efficiently use >> other indexes if necessary. > If you can afford to lock the table for a while, the easiest is > > BEGIN; > DROP INDEX bothersome_idx; > EXPLAIN your_query; > ROLLBACK; > Interesting. But what do you mean by "a while?" Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback? What would happen if you did: BEGIN; DROP INDEX bothersome_idx; INSERT INTO indexed_table...; ROLLBACK; Cheers, Steve
On 05/20/2014 11:48 AM, Steve Crawford wrote: > ... > > What would happen if you did: > BEGIN; > DROP INDEX bothersome_idx; > INSERT INTO indexed_table...; > ROLLBACK; > Never mind. Thought it through. Cheers, Steve
Steve Crawford <scrawford@pinpointresearch.com> writes:
> On 05/20/2014 10:44 AM, Alvaro Herrera wrote:
>> If you can afford to lock the table for a while, the easiest is
>>
>> BEGIN;
>> DROP INDEX bothersome_idx;
>> EXPLAIN your_query;
>> ROLLBACK;
> Interesting. But what do you mean by "a while?" Does the above keep the
> index intact (brief lock) or does it have to rebuild it on rollback?
The index doesn't need to be rebuilt; the transaction need take only
as long as your EXPLAIN does.
> What would happen if you did:
> BEGIN;
> DROP INDEX bothersome_idx;
> INSERT INTO indexed_table...;
> ROLLBACK;
The INSERT would insert a tuple lacking any entry in bothersome_idx,
but it doesn't matter since it'll get rolled back.
regards, tom lane
Steve Crawford wrote > On 05/20/2014 10:44 AM, Alvaro Herrera wrote: >> Steve Crawford wrote: >>> Is there a way to force a specific index to be removed from >>> consideration in planning a single query? >>> >>> Specifically, on a 60-million-row table I have an index that is a >>> candidate for removal. I have identified the sets of nightly queries >>> that use the index but before dropping it I would like to run >>> EXPLAIN and do timing tests on the queries to see the impact of not >>> having that index available and rewrite the query to efficiently use >>> other indexes if necessary. >> If you can afford to lock the table for a while, the easiest is >> >> BEGIN; >> DROP INDEX bothersome_idx; >> EXPLAIN your_query; >> ROLLBACK; >> > Interesting. But what do you mean by "a while?" Does the above keep the > index intact (brief lock) or does it have to rebuild it on rollback? > > What would happen if you did: > BEGIN; > DROP INDEX bothersome_idx; > INSERT INTO indexed_table...; > ROLLBACK; DROP INDEX would take a lock, the insert would happen without updating bothersome_idx, then the rollback would revert indexed_table back to the way it was before the DROP INDEX was issued - both data and active indexes. Since the table contents didn't change there is no need to rebuild any associated indexes. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Force-specific-index-disuse-tp5804564p5804591.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Steve Crawford wrote: > On 05/20/2014 10:44 AM, Alvaro Herrera wrote: > >If you can afford to lock the table for a while, the easiest is > > > >BEGIN; > >DROP INDEX bothersome_idx; > >EXPLAIN your_query; > >ROLLBACK; > > > Interesting. But what do you mean by "a while?" Does the above keep > the index intact (brief lock) or does it have to rebuild it on > rollback? Dropped relations are not deleted from disk until transaction commit, so the original index is kept intact, and when ROLLBACK is executed only catalog state changed by the DROP INDEX is "reverted," but it's an instant operation. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Tue, May 20, 2014 at 11:48 AM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
On 05/20/2014 10:44 AM, Alvaro Herrera wrote:Interesting. But what do you mean by "a while?" Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback?Steve Crawford wrote:Is there a way to force a specific index to be removed fromIf you can afford to lock the table for a while, the easiest is
consideration in planning a single query?
Specifically, on a 60-million-row table I have an index that is a
candidate for removal. I have identified the sets of nightly queries
that use the index but before dropping it I would like to run
EXPLAIN and do timing tests on the queries to see the impact of not
having that index available and rewrite the query to efficiently use
other indexes if necessary.
BEGIN;
DROP INDEX bothersome_idx;
EXPLAIN your_query;
ROLLBACK;
Best case, 'A while' means however long it takes the explain (possibly analyze) to run, and for you to then type 'rollback;'
worse case, someone else is already holding an incompatible lock (i.e. any lock) on the table, and is going to hang on to it for a long while, so your drop index hangs forever waiting to acquire the lock and in the process brings all other desired activity (except the one already holding the lock) to a screeching halt because they are not allowed to jump the lock queue.
worser case, you forget to enter 'rollback' at all and accidentally commit the index drop.
I use the begin...drop...rollback on test servers a lot, but rarely on productions servers. If I don't want to lock for that long, you can often alter the query to make the index useless, for example:
explain select * from pgbench_accounts where aid=87;
Becomes:
explain select * from pgbench_accounts where aid+0=87;
Although for complex queries it can be hard to find the antimagic bullet.
Cheers,
Jeff
On Tue, May 20, 2014 at 11:48 AM, Steve Crawford <[hidden email]> wrote:On 05/20/2014 10:44 AM, Alvaro Herrera wrote:Steve Crawford wrote:Is there a way to force a specific index to be removed from
consideration in planning a single query?
Specifically, on a 60-million-row table I have an index that is a
candidate for removal. I have identified the sets of nightly queries
that use the index but before dropping it I would like to run
EXPLAIN and do timing tests on the queries to see the impact of not
having that index available and rewrite the query to efficiently use
other indexes if necessary.If you can afford to lock the table for a while, the easiest is
BEGIN;
DROP INDEX bothersome_idx;
EXPLAIN your_query;
ROLLBACK;Interesting. But what do you mean by "a while?" Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback?Best case, 'A while' means however long it takes the explain (possibly analyze) to run, and for you to then type 'rollback;'worse case, someone else is already holding an incompatible lock (i.e. any lock) on the table, and is going to hang on to it for a long while, so your drop index hangs forever waiting to acquire the lock and in the process brings all other desired activity (except the one already holding the lock) to a screeching halt because they are not allowed to jump the lock queue.worser case, you forget to enter 'rollback' at all and accidentally commit the index drop.I use the begin...drop...rollback on test servers a lot, but rarely on productions servers. If I don't want to lock for that long, you can often alter the query to make the index useless, for example:explain select * from pgbench_accounts where aid=87;Becomes:explain select * from pgbench_accounts where aid+0=87;Although for complex queries it can be hard to find the antimagic bullet.Cheers,Jeff
This whole line of thought is a use-case for Jaime Casanova's recent proposal:
Note that disabling the index as shown, via clobbering a WHERE clause, doesn't help when the concern is how much time is being spent updating indexes during INSERT/UPDATE.
The risk of forgetting to issue the "BEGIN;" (not so much ROLLBACK given typical default behavior if BEGIN is indeed issued) is probably the one that would concern me the most if working on a production server.
David J.
View this message in context: Re: Force specific index disuse
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Jeff Janes wrote: > Best case, 'A while' means however long it takes the explain (possibly > analyze) to run, and for you to then type 'rollback;' > > worse case, someone else is already holding an incompatible lock (i.e. any > lock) on the table, and is going to hang on to it for a long while, so your > drop index hangs forever waiting to acquire the lock and in the process > brings all other desired activity (except the one already holding the lock) > to a screeching halt because they are not allowed to jump the lock queue. > > worser case, you forget to enter 'rollback' at all and accidentally commit > the index drop. I guess you could write a program to do this for you instead of doing it interactively. That way, 1. you never forget BEGIN 2. you never mistake ROLLBACK and type COMMIT instead (oops). 3. you can LOCK TABLE before the DROP, with NOWAIT, and if it fails, just retry later; or you can specify a statement_timeout so that an upper limit to impact on other queries is. (Reset statement_timeout after LOCK TABLE is successful, so that the EXPLAIN can take longer if necessary). I guess you should use a test server, of course, and that would mostly free you from concern (3) anyway. Also: there is, or used to be, a concept of hypothetical indexes in the planner which could be useful to tools attaching to some hook(s) already in core. EDB had an "index advisor" tool way back when; I don't know if it's still alive. I have never tried any of this. I probably wouldn't run it on a production server anyway ... -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
We routinely use plantuner. http://www.sai.msu.su/~megera/wiki/plantuner On Tue, May 20, 2014 at 12:38 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote: > Is there a way to force a specific index to be removed from consideration in > planning a single query? > > Specifically, on a 60-million-row table I have an index that is a candidate > for removal. I have identified the sets of nightly queries that use the > index but before dropping it I would like to run EXPLAIN and do timing tests > on the queries to see the impact of not having that index available and > rewrite the query to efficiently use other indexes if necessary. > > Cheers, > Steve > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general