Re: Help with tuning this query (with explain analyze finally) - Mailing list pgsql-performance
From | Ken Egervari |
---|---|
Subject | Re: Help with tuning this query (with explain analyze finally) |
Date | |
Msg-id | 000601c51f63$63c8bb10$6a01a8c0@KEN Whole thread Raw |
In response to | Help with tuning this query ("Ken Egervari" <ken@upfactor.com>) |
Responses |
Re: Help with tuning this query (with explain analyze finally)
Re: Help with tuning this query (with explain analyze finally) |
List | pgsql-performance |
>it might help the planner estimate better the number of cs rows >affected. whether this improves performance depends on whether >the best plans are sensitive to this. I managed to try this and see if it did anything. Unfortunately, it made no difference. It's still 250 milliseconds. It was a good suggestion though. I believed it work too. > an EXPLAIN ANALYSE might reduce the guessing. Okay, here is the explain analyze I managed to get from work. It came out to 312ms here, but without the analyze it actually runs at ~250ms. It is using indexes, so my guess is that there are too many joins or it's not driving on person fast enough. Release code is such a small table that I dont think that sequencial scan matters. Thanks for taking the time to analyze this. Sort (cost=1902.27..1902.31 rows=17 width=91) (actual time=312.000..312.000 rows=39 loops=1) Sort Key: ss.date -> Hash Join (cost=617.07..1901.92 rows=17 width=91) (actual time=234.000..312.000 rows=39 loops=1) Hash Cond: ("outer".carrier_code_id = "inner".id) -> Merge Join (cost=602.54..1882.73 rows=870 width=91) (actual time=234.000..312.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..2552.13 rows=60327 width=66) (actual time=0.000..61.000 rows=27711 loops=1) Filter: (is_purged = false) -> Sort (cost=602.54..607.21 rows=1866 width=25) (actual time=125.000..125.000 rows=6934 loops=1) Sort Key: ss.id -> Hash Join (cost=1.11..501.17 rows=1866 width=25) (actual time=0.000..78.000 rows=6934 loops=1) Hash Cond: ("outer".release_code_id = "inner".id) -> Index Scan using current_status_date_idx on shipment_status ss (cost=0.00..406.78 rows=14924 width=25) (actual time=0.000..47.000 rows=15053 loops=1) Index Cond: (date >= (('now'::text)::date - 31)) Filter: (id IS NOT NULL) -> 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.53..14.53 rows=2 width=4) (actual time=0.000..0.000 rows=0 loops=1) -> Nested Loop (cost=4.92..14.53 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=1) -> Index Scan using person_pkey on person p (cost=0.00..5.75 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=8) (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=16) (actual time=0.000..0.000 rows=0 loops=1) -> Hash Join (cost=3.04..4.92 rows=2 width=16) (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=8) (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: 312.000 ms Ken
pgsql-performance by date: