pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ken Egervari <ken@upfactor.com>
To: Ragnar Hafstaư <gnari@simnet.is>
To: pgsql-performance@postgresql.org
Subject: Re: Help with tuning this query (with explain analyze finally)
Date: Wed, 2 Mar 2005 15:06:58 -0500
Message-ID: <000601c51f63$63c8bb10$6a01a8c0@KEN> (raw)
References: <001001c51ef4$3849eeb0$6a01a8c0@KEN>
	<1109787227.31084.12.camel@localhost.localdomain>
	<000901c51f55$aa4b7400$6a01a8c0@KEN>
	<1109789394.31084.20.camel@localhost.localdomain>

>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 




view thread (26+ messages)  latest in thread

Message-ID: <000601c51f63$63c8bb10$6a01a8c0@KEN>
Permalink:  ../000601c51f63$63c8bb10$6a01a8c0@KEN/
Also on:    postgresql.org/message-id/000601c51f63$63c8bb10$6a01a8c0@KEN

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ken@upfactor.com, gnari@simnet.is
  Subject: Re: Help with tuning this query (with explain analyze finally)
  In-Reply-To: <000601c51f63$63c8bb10$6a01a8c0@KEN>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox