Re: [GENERAL] Force specific index disuse

2014-05-21 Thread Oleg Bartunov
We routinely use plantuner. http://www.sai.msu.su/~megera/wiki/plantuner On Tue, May 20, 2014 at 12:38 PM, 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

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Alvaro Herrera
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, s

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread David G Johnston
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] > > wrote: > >> On 05/20/2014 10:44 AM, Alvaro Herrera wrote:

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Jeff Janes
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

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Alvaro Herrera
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 i

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread David G Johnston
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 rem

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Tom Lane
Steve Crawford 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 > in

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Steve Crawford
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 yo

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Steve Crawford
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 nightl

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Steve Crawford
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 iss

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Alvaro Herrera
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 befo

Re: [GENERAL] Force specific index disuse

2014-05-20 Thread Seamus Abshere
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 no

[GENERAL] Force specific index disuse

2014-05-20 Thread Steve Crawford
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