Re: Help with tuning this query (with explain analyze finally) - Mailing list pgsql-performance

Ken Egervari wrote:

> Josh,
>
> 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.

John
=:->

> 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 majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly