Thread: Tuning, configuration for 7.3.5 on a Sun E4500

Env:  Sun E4500 with 8 gig of RAM in total.  Database is stored
locally (not on a network storage devise).  A copy of the
postgresql.conf file is attached.

When running queries we are experiencing much bigger result times than
anticipated.

Attached is a copy of our postgresql.conf file and of our the table
definitions and row counts.

Below is an example of SQL and the explain plans.

Any help/pointers/tips/etc.  for getting this speed up would be great!!

Cheers


SELECT C.component_id, I.cli,
                        BL.ncos_value, BL.description,
                        SG.switch_group_code, SG.servcom_name,
                        S.description AS status,
                        RC.description AS process_status,
                        OT.description AS order_type,
                        P.party_name,
                        RDCR.consumer_ref AS consumer_ref,
                        C.raised_dtm AS created_dtm,
                        (SELECT dtm FROM orders.communication WHERE
component_id = C.component_id ORDER BY dtm DESC LIMIT 1) AS status_dtm
                         FROM  (SELECT * FROM parties.party WHERE
party_id = 143 AND is_active = true) P
                         JOIN orders.commercial_order CO ON
CO.party_id = P.party_id
                         JOIN (SELECT raised_dtm, component_id,
last_supplier_status, component_type_id, current_status_id_fr,
commercial_order_id FROM orders.component WHERE raised_dtm BETWEEN
'2003-01-01 00:00:00'::timestamp AND '2005-01-01 23:59:59'::timestamp
AND component_type_id IN (3, 2, 1)) C ON C.commercial_order_id =
CO.commercial_order_id
                         JOIN (SELECT * FROM orders.ida WHERE cli IS
NOT NULL  ) I ON C.component_id = I.component_id
                         --Get the consumer reference if there is one
                          LEFT JOIN parties.consumer_ref  RDCR ON
CO.consumer_ref = RDCR.consumer_ref_id
                         --May or may not have barring level or ncos
dependant on the order type
                         LEFT JOIN line_configs.ida_barring_level BL
ON I.ida_barring_level_id = BL.ida_barring_level_id
                         LEFT JOIN line_configs.switch_group SG ON
I.switchgroup_id = SG.switch_group_id
                         --Get the order type
                         JOIN business_rules.component_type CT ON
C.component_type_id = CT.component_type_id
                         JOIN business_rules.order_type OT ON
OT.order_type_id = CT.order_type_id
                         --Get the status
                         LEFT JOIN orders.status S ON S.status_id =
C.current_status_id_fr
                         --Get the process status
                         LEFT JOIN orders.response_code RC ON
RC.response_code_id = C.last_supplier_status


QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Hash Join  (cost=18.02..16067.46 rows=1158 width=277) (actual
time=639100.57..957020.42 rows=34638 loops=1)
   Hash Cond: ("outer".last_supplier_status = "inner".response_code_id)
   ->  Hash Join  (cost=9.29..16038.49 rows=1158 width=218) (actual
time=639084.27..937250.67 rows=34638 loops=1)
         Hash Cond: ("outer".current_status_id_fr = "inner".status_id)
         ->  Hash Join  (cost=8.17..16017.14 rows=1158 width=197)
(actual time=639083.19..931508.95 rows=34638 loops=1)
               Hash Cond: ("outer".order_type_id = "inner".order_type_id)
               ->  Hash Join  (cost=6.99..15995.69 rows=1158
width=180) (actual time=639082.01..926146.92 rows=34638 loops=1)
                     Hash Cond: ("outer".component_type_id =
"inner".component_type_id)
                     ->  Hash Join  (cost=5.47..15973.91 rows=1158
width=172) (actual time=639080.29..921574.75 rows=34638 loops=1)
                           Hash Cond: ("outer".switchgroup_id =
"inner".switch_group_id)
                           ->  Hash Join  (cost=1.49..15949.66
rows=1158 width=147) (actual time=639074.90..917437.55 rows=34638
loops=1)
                                 Hash Cond:
("outer".ida_barring_level_id = "inner".ida_barring_level_id)
                                 ->  Merge Join  (cost=0.00..15927.90
rows=1158 width=112) (actual time=639073.24..914042.15 rows=34638
loops=1)
                                       Merge Cond:
("outer".consumer_ref = "inner".consumer_ref_id)
                                       ->  Nested Loop
(cost=0.00..2630554.06 rows=1158 width=91) (actual
time=639072.57..909395.62 rows=34638 loops=1)
                                             ->  Nested Loop
(cost=0.00..2626789.68 rows=1244 width=66) (actual
time=639053.64..902100.16 rows=34638 loops=1)
                                                   ->  Nested Loop
(cost=0.00..2599576.29 rows=7041 width=38) (actual
time=2073.94..891860.92 rows=46376 loops=1)
                                                         Join Filter:
("outer".party_id = "inner".party_id)
                                                         ->  Index
Scan using commercial_order_consumer_ref_ix on commercial_order co
(cost=0.00..19499.42 rows=725250 width=12) (actual time=8.62..30310.16
rows=725250 loops=1)
                                                         ->  Seq Scan
on party  (cost=0.00..3.54 rows=1 width=26) (actual time=0.62..1.16
rows=1 loops=725250)
                                                               Filter:
((party_id = 143) AND (is_active = true))
                                                   ->  Index Scan
using component_commercial_order_id_ix on component  (cost=0.00..3.85
rows=1 width=28) (actual time=0.17..0.18 rows=1 loops=46376)
                                                         Index Cond:
(component.commercial_order_id = "outer".commercial_order_id)
                                                         Filter:
((raised_dtm >= '2003-01-01 00:00:00'::timestamp without time zone)
AND (raised_dtm <= '2005-01-01 23:59:59'::timestamp without time zone)
AND ((component_type_id = 3) OR (component_type_id = 2) OR
(component_type_id = 1)))
                                             ->  Index Scan using
ida_pkey on ida  (cost=0.00..3.01 rows=1 width=25) (actual
time=0.12..0.14 rows=1 loops=34638)
                                                   Index Cond:
("outer".component_id = ida.component_id)
                                                   Filter: (cli IS NOT NULL)
                                       ->  Index Scan using
consumer_ref_pk on consumer_ref rdcr  (cost=0.00..24.31 rows=937
width=21) (actual time=0.48..0.48 rows=1 loops=1)
                                 ->  Hash  (cost=1.39..1.39 rows=39
width=35) (actual time=1.07..1.07 rows=0 loops=1)
                                       ->  Seq Scan on
ida_barring_level bl  (cost=0.00..1.39 rows=39 width=35) (actual
time=0.07..0.76 rows=39 loops=1)
                           ->  Hash  (cost=3.59..3.59 rows=159
width=25) (actual time=4.54..4.54 rows=0 loops=1)
                                 ->  Seq Scan on switch_group sg
(cost=0.00..3.59 rows=159 width=25) (actual time=0.09..3.13 rows=159
loops=1)
                     ->  Hash  (cost=1.41..1.41 rows=41 width=8)
(actual time=0.90..0.90 rows=0 loops=1)
                           ->  Seq Scan on component_type ct
(cost=0.00..1.41 rows=41 width=8) (actual time=0.08..0.64 rows=41
loops=1)
               ->  Hash  (cost=1.15..1.15 rows=15 width=17) (actual
time=0.43..0.43 rows=0 loops=1)
                     ->  Seq Scan on order_type ot  (cost=0.00..1.15
rows=15 width=17) (actual time=0.08..0.31 rows=15 loops=1)
         ->  Hash  (cost=1.09..1.09 rows=9 width=21) (actual
time=0.29..0.29 rows=0 loops=1)
               ->  Seq Scan on status s  (cost=0.00..1.09 rows=9
width=21) (actual time=0.08..0.22 rows=9 loops=1)
   ->  Hash  (cost=7.99..7.99 rows=299 width=59) (actual
time=8.69..8.69 rows=0 loops=1)
         ->  Seq Scan on response_code rc  (cost=0.00..7.99 rows=299
width=59) (actual time=0.16..5.94 rows=299 loops=1)
   SubPlan
     ->  Limit  (cost=21.23..21.23 rows=1 width=8) (actual
time=0.45..0.46 rows=1 loops=34638)
           ->  Sort  (cost=21.23..21.27 rows=16 width=8) (actual
time=0.44..0.44 rows=1 loops=34638)
                 Sort Key: dtm
                 ->  Index Scan using communication_component_id_ix on
communication  (cost=0.00..20.90 rows=16 width=8) (actual
time=0.12..0.14 rows=1 loops=34638)
                       Index Cond: (component_id = $0)
 Total runtime: 957091.40 msec
(47 rows)



SELECT raised_dtm, component_id, last_supplier_status,
component_type_id, current_status_id_fr, commercial_order_id FROM
orders.component WHERE raised_dtm BETWEEN '2003-01-01
00:00:00'::timestamp AND '2005-01-01 23:59:59'::timestamp  AND
component_type_id IN (3, 2, 1)

 QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using component_raised_dtm_ix on component
(cost=0.00..17442.38 rows=128571 width=28) (actual time=1.04..20781.05
rows=307735 loops=1)
   Index Cond: ((raised_dtm >= '2003-01-01 00:00:00'::timestamp
without time zone) AND (raised_dtm <= '2005-01-01 23:59:59'::timestamp
without time zone))
   Filter: ((component_type_id = 3) OR (component_type_id = 2) OR
(component_type_id = 1))
 Total runtime: 21399.79 msec
(4 rows)


SELECT * FROM orders.ida WHERE cli IS NOT NULL;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on ida  (cost=0.00..12420.24 rows=677424 width=25) (actual
time=0.15..16782.27 rows=677415 loops=1)
   Filter: (cli IS NOT NULL)
 Total runtime: 17885.80 msec
(3 rows)

Tsarevich,

> When running queries we are experiencing much bigger result times than
> anticipated.
>
> Attached is a copy of our postgresql.conf file and of our the table
> definitions and row counts.

Looks like you haven't run ANALYZE on the database anytime recently.  Try that
and re-run.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Analyze has been run on the database quite frequently during the
course of us trying to figure out this performance issue.  It is also
a task that is crontabbed nightly.


On Mon, 7 Mar 2005 09:31:06 -0800, Josh Berkus <josh@agliodbs.com> wrote:
> Tsarevich,
>
> > When running queries we are experiencing much bigger result times than
> > anticipated.
> >
> > Attached is a copy of our postgresql.conf file and of our the table
> > definitions and row counts.
>
> Looks like you haven't run ANALYZE on the database anytime recently.  Try that
> and re-run.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

Tsarevich,

> Analyze has been run on the database quite frequently during the
> course of us trying to figure out this performance issue.  It is also
> a task that is crontabbed nightly.

Hmmm.  Then you probably need to up the STATISTICS levels on the target
column, because PG is mis-estimating the number of rows returned
significantly.   That's done by:

ALTER TABLE {table} ALTER COLUMN {column} SET STATISTICS {number}

Generally, I find that if mis-estimation occurs, you need to raise statistics
to at least 250.

Here's where I see the estimation issues with your EXPLAIN:

                                                   ->  Index Scan
using component_commercial_order_id_ix on component  (cost=0.00..3.85
rows=1 width=28) (actual time=0.17..0.18 rows=1 loops=46376)
                                                         Index Cond:
(component.commercial_order_id = "outer".commercial_order_id)
                                                         Filter:
((raised_dtm >= '2003-01-01 00:00:00'::timestamp without time zone)
AND (raised_dtm <= '2005-01-01 23:59:59'::timestamp without time zone)
AND ((component_type_id = 3) OR (component_type_id = 2) OR
(component_type_id = 1)))

                 ->  Index Scan using communication_component_id_ix on
communication  (cost=0.00..20.90 rows=16 width=8) (actual
time=0.12..0.14 rows=1 loops=34638)
                       Index Cond: (component_id = $0)

So it looks like you need to raise the stats on communication.component_id and
component.commercial_order_id,raised_dtm,component_type_id.   You also may
want to consider a multi-column index on the last set.

BTW, if you have any kind of data update traffic at all, ANALYZE once a day is
not adequate.

--
Josh Berkus
Aglio Database Solutions
San Francisco