Re: [GENERAL] Force specific index disuse
We routinely use plantuner. http://www.sai.msu.su/~megera/wiki/plantuner On Tue, May 20, 2014 at 12:38 PM, Steve Crawford scrawf...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
Steve Crawford scrawf...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
On Tue, May 20, 2014 at 11:48 AM, Steve Crawford scrawf...@pinpointresearch.com 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
Re: [GENERAL] Force specific index disuse
On Tue, May 20, 2014 at 3:20 PM, Jeff Janes [via PostgreSQL] ml-node+s1045698n580459...@n5.nabble.com wrote: On Tue, May 20, 2014 at 11:48 AM, Steve Crawford [hidden email]http://user/SendEmail.jtp?type=nodenode=5804596i=0 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: http://postgresql.1045698.n5.nabble.com/WIP-showing-index-maintenance-on-EXPLAIN-td5803106.html 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: http://postgresql.1045698.n5.nabble.com/Force-specific-index-disuse-tp5804564p5804601.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Force specific index disuse
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
