Ken Egervari wrote:
I did everything you said and my query does perform a bit better. I've been getting speeds from 203 to 219 to 234 milliseconds now. I tried increasing the work mem and the effective cache size from the values you provided, but I didn't see any more improvement. I've tried to looking into setting the shared buffers for Windows XP, but I'm not sure how to do it. I'm looking in the manual at: http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html#SYSVIPC-PARAMETERS
You probably don't need to change anything for Windows. If you set shared_buffers too high, then postgres won't start. If it is starting, then you don't need to modify the OS to get more shared buffers. (For instance, on my Mac, I can't get shared_buffers > 500 without changing things, but on windows I run with 3000 and no modification).
It doesn't mention windows at all. Does anyone have any ideas on have to fix this?
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.
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.
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).
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.
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. :)
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.
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?
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.
But remember, the biggest bottleneck is almost *always* the I/O. So put more & faster disks into the system first.
Here is the new explain analyze.
Sort (cost=1996.21..1996.26 rows=17 width=165) (actual time=297.000..297.000 rows=39 loops=1) Sort Key: ss.date -> Merge Right Join (cost=1951.26..1995.87 rows=17 width=165) (actual time=297.000..297.000 rows=39 loops=1) Merge Cond: ("outer".id = "inner".driver_id) -> Index Scan using driver_pkey on driver d (cost=0.00..42.16 rows=922 width=43) (actual time=0.000..0.000 rows=922 loops=1) -> Sort (cost=1951.26..1951.30 rows=17 width=122) (actual time=297.000..297.000 rows=39 loops=1) Sort Key: s.driver_id -> Hash Join (cost=586.48..1950.91 rows=17 width=122) (actual time=219.000..297.000 rows=39 loops=1) Hash Cond: ("outer".carrier_code_id = "inner".id) -> Merge Join (cost=571.97..1931.95 rows=830 width=87) (actual time=219.000..297.000 rows=310 loops=1) Merge Cond: ("outer".current_status_id = "inner".id) -> Index Scan using shipment_current_status_id_idx on shipment s (cost=0.00..2701.26 rows=60307 width=66) (actual time=0.000..62.000 rows=27711 loops=1) Filter: (is_purged = false) -> Sort (cost=571.97..576.38 rows=1766 width=21) (actual time=125.000..156.000 rows=6902 loops=1) Sort Key: ss.id -> Hash Join (cost=1.11..476.72 rows=1766 width=21) (actual time=0.000..93.000 rows=6902 loops=1) Hash Cond: ("outer".release_code_id = "inner".id) -> Index Scan Backward using current_status_date_idx on shipment_status ss (cost=0.00..387.35 rows=14122 width=21) (actual time=0.000..16.000 rows=14925 loops=1) Index Cond: (date >= (('now'::text)::date - 31)) -> Hash (cost=1.10..1.10 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1) -> Seq Scan on release_code rc (cost=0.00..1.10 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) Filter: ((number)::text = '9'::text) -> Hash (cost=14.51..14.51 rows=2 width=35) (actual time=0.000..0.000 rows=0 loops=1) -> Nested Loop (cost=4.92..14.51 rows=2 width=35) (actual time=0.000..0.000 rows=2 loops=1) -> Index Scan using person_pkey on person p (cost=0.00..5.73 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (id = 355) -> Hash Join (cost=4.92..8.75 rows=2 width=39) (actual time=0.000..0.000 rows=2 loops=1) Hash Cond: ("outer".id = "inner".carrier_id) -> Seq Scan on carrier c (cost=0.00..3.54 rows=54 width=4) (actual time=0.000..0.000 rows=54 loops=1) -> Hash (cost=4.92..4.92 rows=2 width=43) (actual time=0.000..0.000 rows=0 loops=1) -> Hash Join (cost=3.04..4.92 rows=2 width=43) (actual time=0.000..0.000 rows=2 loops=1) Hash Cond: ("outer".carrier_id = "inner".carrier_id) -> Seq Scan on carrier_code cc (cost=0.00..1.57 rows=57 width=35) (actual time=0.000..0.000 rows=57 loops=1) -> Hash (cost=3.04..3.04 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1) -> Index Scan using carrier_to_person_person_id_idx on carrier_to_person ctp (cost=0.00..3.04 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (355 = person_id) Total runtime: 297.000 ms
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Description: OpenPGP digital signature