Re: Fastest way to drop an index?

Thanks for the suggestions.

Following is the SQL query. Actually, this is not merely a DROP INDEX
question. I am also surprised that this straight index query takes
more time than it used to! It would be under 1 second because it's a
one-row conditional match, but not it takes anywhere between 5 to 10
seconds for just one row!

Only change I have made recently is to increase the stats for user_id
to 300. Not for title_encrypted. User_id is varchar(35) and
title_encrypted is varchar(40).

Will this differential statistics on two columns in a WHERE clause
affect query speed? I wonder.




pguser=# explain analyze select title_alias from testimonials where
user_id = 'superman' and title_encrypted = md5('MY TITLE COMES HERE')
;
                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using new_idx_testimonials_userid on testimonials
(cost=0.00..157.78 rows=1 width=9) (actual time=8809.715..8809.715
rows=0 loops=1)
   Index Cond: ((user_id)::text = 'superman'::text)
   Filter: ((title_encrypted)::text = 'b333dc1b0992cb8c70b58a418211389a'::text)
 Total runtime: 8809.750 ms
(4 rows)

Time: 8811.817 ms



pguser=# explain analyze select title_alias from testimonials where
user_id = 'superman' and title_encrypted = md5('MY TITLE COMES HERE')
;
                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using new_idx_testimonials_userid on testimonials
(cost=0.00..157.78 rows=1 width=9) (actual time=1.426..1.426 rows=0
loops=1)
   Index Cond: ((user_id)::text = 'superman'::text)
   Filter: ((title_encrypted)::text = 'b333dc1b0992cb8c70b58a418211389a'::text)
 Total runtime: 1.462 ms
(4 rows)

Time: 2.289 ms