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

Re: Not Picking Index

From: "George Pavlov" <gpavlov(at)mynewplace(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Not Picking Index
Date: 2007-02-16 18:33:22
Message-ID: 8C5B026B51B6854CBE88121DBF097A8684DA43@ehost010-33.exch010.intermedia.net (view raw or flat)
Thread:
Lists: pgsql-performance
> Note the DROP INDEX will acquire exclusive lock on the table, so this
> might not be the greatest thing to do in a production environment.
> In PG 8.2 and up there is a sneakier way to do it that won't acquire
> any more lock than the statement-under-test does:
> 
> 	begin;
> 	update pg_index set indisvalid = false
> 	  where indexrelid = 'soandso'::regclass;
> 	explain analyze ...;
> 	rollback;

this really smacks of that four-letter word that starts with h... -- i
am glad we have finally come around on the subject :-) 

seriously, this is a great technique and an enormous time saver during
query optimization. thanks for sharing!

george

In response to

Responses

pgsql-performance by date

Next:From: Mark StosbergDate: 2007-02-16 19:25:21
Subject: Re: reindex vs 'analyze'
Previous:From: Steinar H. GundersonDate: 2007-02-16 18:32:04
Subject: Re: Not Picking Index

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