Thanks so much for your comments. They are incredibly insightful and you clearly know your stuff. It's so great that I'm able to learn so much from you. I really appreciate it.
Do you need the interior sort? It's taking ~93ms to get 7k rows from shipment_status, and then another 30ms to sort them. This isn't a lot, so it might be fine.
Running the query without the sort doesn't actually improve performance unfortunately, which I find strange. I think the analyze is giving bad feedback because taking all sorts out completely makes no difference in performance. Dan Tow's book actually said the same thing... how sorting rarely takes up the bulk of the work. Although I know you didn't like his book much, but I had observed that in my experience too.
Also, did you ever try CLUSTER current_status_date_idx ON shipment_status. This groups the rows in shipment_status by their status date, which helps put items with the same date next to eachother. This may effect other portions of the query, or other queries. Also, if you are inserting sequentially, it would seem that the items would already be naturally near eachother based on date.
Yes, this was one of the first things I tried actually and it is currently clustered. Since shipment status comes into our system at real time, the dates are more or less in order as well.
The next big cost is having to merge the 28k rows with the fast hash plan, which takes about 80ms.
I guess the biggest issue is that you are doing a lot of work, and it takes time to do it. Also, I've noticed that this query is being run with exactly the same data. Which is good to compare two methods. But remember to test on multiple potential values. You might be better off one way with this query, but much worse for a different dataset. I noticed that this seems to have fewer rows than what postgres thinks the *average* number would be. (It predicts 60k and you only get 28k rows).
Well, the example where p.id = 355 is an above normal case where performance is typically bad. If a user's company has very few shipments and shipment_status rows, performance isn't going to matter much and those queries usually perform much faster. I really needed to tune this for the larger customers who do have thousands of rows for their entire company and will probably reach 6 digits by the end of next year. For the person 355, they've only been on the system for 3 months and they already have 27700 rows. Even if this makes the smaller customers a bit slower, I think it's worth it if I can speed up cases like this, who all have very similar data distribution.
If this query is performed a lot, and you can be okay with a slight delay in updating, you could always switch to some sort of lazy materialized view.
I thought about this, but it's very important since shipment and shipment_status are both updated in real time 24/7/365. I think I might be able to cache it within the application for 60 seconds at most, but it would make little difference since people tend to refresh within that time anyway. It's very important that real-time inforamtion exists though.
You could also always throw more hardware at it. :) If the shipment_status is one of the bottlenecks, create a 4-disk raid10 and move the table over. I don't remember what your hardware is, but I don't remember it being a quad opteron with 16GB ram, and 20 15k SCSI disks, with the transaction log on a solid state disk. :)
That sounds like an awesome system. I loved to have something like that. Unfortunately, the production server is just a single processor machine with 1 GB ram. I think throwing more disks at it is probably the best bet, moving the shipment and shipment_status tables over as you suggested. That's great advice.
Why do you need the query to be 30ms? ~250ms is still pretty fast. If you are needing updates faster than that, you might look more into *why* and then handle it from a higher level.
30ms is a good target, although I guess I was naive for setting that goal perhaps. I've just taken queries that ran at 600ms and with 1 or 2 indexes, they went down to 15ms.
Let's say we have 200 users signed into the application at the same time. The application refreshes their shipment information automatically to make sure it's up to date on the user's screen. The application will execute the query we are trying to tune every 60 seconds for most of these users. Users can set the refresh time to be higher, but 60 is the lowest amount so I'm just assuming everyone has it at 60.
Anyway, if you have 200 users logged in, that's 200 queries in the 60 second period, which is about 3-4 queries every second. As you can see, it's getting maxed out, and because of bad luck, the queries are bunched together and are being called at the same time, making 8-9 queries in the same second and that's where the performance is starting to degrade. I just know that if I could get this down to 30 ms, or even 100, we'd be okay for a few months without throwing hardware at the problem. Also keep in mind that other application logic and Hibernate mapping is occuring to, so 3-4 queries a second is already no good when everything is running on a single machine.
This isn't the best setup, but it's the best we can afford. We are just a new startup company. Cheaper servers and open source keep our costs low. But money is starting to come in after 10 months of hard work, so we'll be able to replace our server within the next 2 months. It'll be a neccessity because we are signing on some big clients now and they'll have 40 or 50 users for a single company. If they are all logged in at the same time, that's a lot of queries.
And naturally, the most important this is to test it under load. 250ms is pretty good, but if under load it goes back to 6s, then we probably should look for different alternatives. Also, what is the load that is causing the problem? Is it that you have some other big seqscans which are causing all of your tables to go out of cache?
No, this query and another very close to it are probably the most executed in the system. In fact, even checking the page stats on the web server tells us that the pages that use these queries are 80% of the pages viewed in our application. If I can fix this problem, I've fixed our performance problems period. The statistics queries are very slow too, but I don't care about that since nobody goes to them much (maybe once a month. People don't mind waiting for that sort of information anyway).
I'm very interested in those other alternatives since I may have to experiment with them. I'm under the impression that this query is actually performing quite well for what I'm throwing at it and the work that it's doing.
Also, I believe I remember you saying that your production server is a P4, is that a single P4? Because I know postgres prefers Opterons to Pentium Xeons when in a multiprocessor machine. Look through the archives about spinlocks and the context switch bug. (context storm, etc). Plus, since opterons are 64-bit, you can throw a lot more RAM at them. I believe opterons outperform xeons for the same cost, *and* you can scale them up with extra ram.
Yeah, we have nothing of that sort. It's really just a P4 3.0 Ghz processor. Like I mentioned before, we just put computers together from what we had and built our application on them. Our business is new, we don't have a lot of money and we're just starting to actually have a good client base. It's finally growing after all of this time but we are still using the servers we started with.
But remember, the biggest bottleneck is almost *always* the I/O. So put more & faster disks into the system first.
I will price that raid setup you recommended. That will probably be the first adjustment to our server if we don't just replace the entire thing.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend