Unfortunately the appilcation I was using had so many queries it was
difficult to EXPLAIN each of them. I ended up measuring and logging
execution time of each query in the application itself and then just
sorting by times and going in and fixing indices on the slow queries.
I am having another related issue though (and I'm trying to use EXPLAIN
VERBOSE to figure it out). I have a large table with a foreign key into a
smaller table; I do very frequent inserts into the large table. I think
the query optmizer is electing to use a sequential scan on the foreign key
in the smaller table since its small, but I think this is degrading
performance as locking is worse on concurrent sequential scans (I'm not
really sure of any of this stuff by the way). Does anybody know how to
check if this is the case (understanding the output of EXPLAIN VERBOSE
is non-trivial) and how to force or suggest an index lookup rather than a
sequential scan?
Thanks,
Sheer
On Mon, 29 Oct 2001, Thomas Lockhart wrote:
> ...
> > The reason I mention foreign keys above is that I imagine that foreign
> > keys might complicate locking issues and I use them a _lot_. I also
> > noticed that the database slows down significantly as it gets populated.
> > When I get to about 8000 transactions (~8000 records in my primary table)
> > it is much slower, and by 19k it takes up to 5-15 seconds to do an insert.
> > A "vacuum analyze" didn't help here.
>
> But appropriate indices should help. Updates which require sequential
> scans are expensive as tables get bigger, and the scaling problem you
> describe is the usual symptom of missing indices.
>
> Use "explain" and examine your queries to tune the performance.
>
> - Thomas
>
