Re^3: Musings about a database ecology

I had a closer look at the queries that I've been doing, and it turns out that the query is searching through a 180,000 row table every twenty seconds or so. it's taking 12 seconds to do that .. so now I have a much better idea of where to optimize, and a temporary table holdnig 'jobs to be done' is looking a lot better.

Thanks for your feedback.

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Comment on Re^3: Musings about a database ecology

Replies are listed 'Best First'.
Re^4: Musings about a database ecology by mpeppler (Vicar) on Dec 21, 2004 at 07:27 UTC
Moral: when you have a performance problem and a database is involved, start by looking very hard at the database and what it is doing to provide you with the results. An RDBMSs query optimizer is a complex piece of software, and it can make wrong decisions that generate horrendously slow query plans... Michael	[reply]
Re^4: Musings about a database ecology by tilly (Archbishop) on Dec 21, 2004 at 07:30 UTC
Depending on the search, you'll be fine just putting an index on whatever column it is searching on. Then it just does a lookup on the index and the search is lightning fast.	[reply]
Re^5: Musings about a database ecology by bprew (Monk) on Dec 24, 2004 at 08:05 UTC
This is one of the big myths about database optimizations. Indexes are not always good, and full table scans are not always bad. I have just experienced this at work. We were able to make 300+% gains on our queries by forcing oracle to do a full table scan over a 12 million row table. Ultimately, you have to know what your query is doing before you start optimizing it. With Postgres, start using the EXPLAIN syntax to figure out if you're using indexes or full table scans. (ie EXPLAIN select * from foo where baz = 'baz'). Next, it helps to understand what your query is doing, and perhaps create a different index that is more selective. 12 secs to scan a 180k row table sounds awfully slow, but it depends on hardware and your I/O subsystem. -- Ben "Naked I came from my mother's womb, and naked I will depart."	[reply]
Re^6: Musings about a database ecology by tilly (Archbishop) on Dec 24, 2004 at 08:26 UTC
I'm fully aware of that, which is why I said, Depending on what your query does... In fact not only am I aware of it as a theoretical point, but within the last month I sped a query up from taking around an hour to 3 minutes by providing a hint that, among other things, convinced it to do a full table scan on a large table. However normally, particularly with straightforward queries, indexes are good. The simplest case of all would be that he's querying a large table and doing something like looking for the handful of items in a particular status. If it really is a handful of items that would be returned, then adding an index would certainly be a huge win.	[reply]