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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
