Re: Performance differences 7.1 to 7.3 - Mailing list pgsql-general

On the 'old' Red Hat AS 2.1 here is the results of explain and the query:

[ ~]$ time /usr/bin/psql mpt -c"explain select count(*) from tpv;"
NOTICE:  QUERY PLAN:

Aggregate  (cost=4563.87..4563.87 rows=1 width=56)
  ->  Nested Loop  (cost=870.92..4563.01 rows=342 width=56)
        ->  Hash Join  (cost=870.92..3869.17 rows=342 width=44)
              ->  Seq Scan on treatment_plan  (cost=0.00..956.66
rows=14844 width=28)
              ->  Hash  (cost=602.33..602.33 rows=24033 width=16)
                    ->  Seq Scan on treatment_plan_header
(cost=0.00..602.33 rows=24033 width=16)
        ->  Index Scan using ada_code_pkey on ada_code  (cost=0.00..2.01
rows=1 width=12)

EXPLAIN
0.010u 0.000s 0:00.03 33.3%    0+0k 0+0io 332pf+0w
[~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;"
 count
-------
 33439
(1 row)

0.010u 0.000s 0:03.10 0.3%    0+0k 0+0io 332pf+0w
[japsey@DCFRAID ~]$

On the 'new' system:

[ ~]$  /usr/bin/psql mpt -c"explain select count(*) from tpv;"
                                                        QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=202529.15..202529.15 rows=1 width=45)
   ->  Nested Loop  (cost=6262.46..202496.78 rows=12948 width=45)
         Join Filter: ("outer".service_code = ("inner".ada_code)::text)
         ->  Merge Join  (cost=6262.46..6754.54 rows=12933 width=36)
               Merge Cond: (("outer".appointment_order =
"inner".appointment_order) AND ("outer".pat_id = "inner".pat_id))
               ->  Sort  (cost=2335.37..2395.35 rows=23992 width=14)
                     Sort Key: treatment_plan_header.appointment_order,
treatment_plan_header.pat_id
                     ->  Seq Scan on treatment_plan_header
(cost=0.00..589.92 rows=23992 width=14)
               ->  Sort  (cost=3927.09..4016.27 rows=35672 width=22)
                     Sort Key: treatment_plan.appointment_order,
treatment_plan.pat_id
                     ->  Seq Scan on treatment_plan  (cost=0.00..800.60
rows=35672 width=22)
                           Filter: (amount IS NULL)
         ->  Seq Scan on ada_code  (cost=0.00..10.06 rows=406 width=9)
(13 rows)
[ ~]$

Where do I go to get clues about the results of "explain"?

Jim Apsey


Dann Corbit wrote:

>I assume that the schema is identical on both systems.
>
>After running vacuum on both systems [for each of the underlying tables
>in tpv], what does explain say about the queries?
>
>Are the shared memory buffers identical on both systems?
>
>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jimmie H. Apsey
>Sent: Monday, December 13, 2004 2:43 PM
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] Performance differences 7.1 to 7.3
>
>Hello all,
>I have just loaded Postgresql 7.3.6-7 onto a new server on the
>recommendation of Tom Lane. It is part of Red Hat AS 3.
>I have Postgresql 7.1.3-5 running on Red Hat AS 2.1.
>I have a simple view from which I select on both systems.  The 7.3.6-7
>version requires 18+ seconds to do a select from a particular view.
>The 7.1.3-5 version requires 3+ seconds to select from the same view.
>
>On the 7.1.3-5 version I do:
>[japsey@DCFRAID ~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;"
> count
>-------
> 33377
>(1 row)
>
>0.000u 0.010s 0:03.55 0.2%    0+0k 0+0io 332pf+0w
>[japsey@DCFRAID ~]$
>
>And on 7.3.6-7 version I do:
>[japsey@DCFRAID ~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;"
> count
>-------
> 33377
>(1 row)
>
>0.010u 0.000s 0:18.38 0.0%    0+0k 0+0io 362pf+0w
>[japsey@DCFRAID ~]$
>
>Does anyone have any clues as to where I should be looking for
>tuning/whatever?
>
>Jim Apsey
>------------------------------------------------------------------------
>----------------------------------
>
>
>---------------------------(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
>
>
>