Hi there,
My name is Archibald and I am fairly new to PostGreSQL but I've worked
with databases for a few years on and off and I consider myself to be a
fairly good database designer and I know my SQL statements.
I am currently building a database to represent a card game, where one is
supposed to search through the cards in order to find cards you want.
Each card has certain attributes (cost, name, requirements and such). What
I have done is made one table cards which only includes the name and then
"objectified" the database by using tables connected to the cards table.
Once all the data (and we're talking a small amount of data here, the
whole database is 1.4M)) is in I add my two views and run them. Here is
when my problems start. I did nothing to increase performance by using
VACUUM or CLUSTER but I frequently used EXPLAIN to see how much diskaccess
I had (since I felt that my query went to slow). The numbers went down
drastically and now I have an EXPLAIN output that I think looks very very
good, very few rows affected and the cost is very low (started at around
6000 and up and is now down att 200 so it is a big cut).
The EXPLAIN on my view can be found in the attachment.
How come my query takes almost a minute execute?
My problems actually started when I used VACUUM ANALYZE. After that the
query took forever. A friend suggested I recreate the database and run
VACUUM ANALYZE when the data is just added, something that is also
suggested in various docs and books but that didn't help. So I tried to
CLUSTER some of my tables, it worked better but then suddenly all was slow
again.
We're running this database on a 700Mhz PC running Linux with 396M of RAM,
so for a "hobby" database that is fairly much. The harddrive is a faster
one as well.
The database design follows the normalization rules of designing
databases, I have made som odd choices where I have created a table which
could be ignored and instead added as a column in another table but we're
talking about such small amounts of data that that shouldn't be a problem.
If anyone has any idea at all feel free to respond, I am grateful for any
help I can get on this.
Thanks in advance,
Archie