Hi,
Using PostgreSQL 7.4.2 on Solaris. I'm trying to
improve performance on some queries to my databases so
I wanted to try out various index structures.
Since I'm going to be running my performance tests
repeatedly, I created some SQL scripts to delete and
recreate various index configurations. One of the
scripts contains the commands for recreating the
'original' index configuration (i.e. the one I've
already got some numbers for). Only thing is now
when I delete and recreate the original indexes then
run the query, I'm finding the performance has gone
completely down the tubes compared to what I
originally had. A query that used to take 5 minutes
to complete now takes hours to complete.
For what it's worth my query looks something like:
select * from tbl_1, tbl_2 where tbl_1.id = tbl_2.id
and tbl_2.name like 'x%y%' and tbl_1.x > 1234567890123
order by tbl_1.x;
tbl_1 is very big (> 2 million rows)
tbl_2 is relatively small (7000 or so rows)
tbl_1.x is a numeric(13)
tbl_1.id & tbl_2.id are integers
tbl_2.name is a varchar(64)
I've run 'VACUUM ANALYZE' on both tables involved in
the query. I also used 'EXPLAIN' and observed that
the query plan is completely changed from what it
was originally.
Any idea why this would be? I would have thougth
that a freshly created index would have better
performance not worse. I have not done any inserts
or updates since recreating the indexes.
thanks in advance,
Bill C
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com