Thread: Performance differences 7.1 to 7.3

Performance differences 7.1 to 7.3

From

"Jimmie H. Apsey"

Date:

14 December 2004, 01:53:00

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
----------------------------------------------------------------------------------------------------------

Re: Performance differences 7.1 to 7.3

From

"Dann Corbit"

Date:

14 December 2004, 02:11:22

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

Re: Performance differences 7.1 to 7.3

From

"Jimmie H. Apsey"

Date:

15 December 2004, 00:28:23

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
>
>
>

Re: Performance differences 7.1 to 7.3

From

"Dann Corbit"

Date:

15 December 2004, 00:57:11


-----Original Message-----
From: Jimmie H. Apsey [mailto:japsey@futuredental.com]
Sent: Tuesday, December 14, 2004 1:18 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance differences 7.1 to 7.3

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"?
>>
The command syntax:
http://www.postgresql.org/docs/current/static/sql-explain.html
A brief explanation:
http://www.freebsddiary.org/postgresql-analyze.php
A longer explanation:
http://www.postgresql.org/docs/7.4/interactive/performance-tips.html
A nuts and bolts look at how PostgreSQL performs queries:
http://candle.pha.pa.us/main/writings/pgsql/performance.pdf

<<

Re: Performance differences 7.1 to 7.3

From

"Jimmie H. Apsey"

Date:

15 December 2004, 02:00:34

I think my indexes are OK.

I have no settings for 'shared memory buffers' in postgresql.conf on either system. Both systems have same, i.e.
#sort_mem = 512
#shared_buffers = 2*max_connections # min 16
#fsync = true

Do you, or any of you, have any other suggestions for why such a simple "select count(*) from tpv;" should take six times as long on our latest and greatest dual processor server with almost the latest and greatest postgresql? View tpv is a three table join which takes 3.38 seconds on the 'old' system and 18.09 seconds on the 'new' system.

Jim Apsey
----------------------------------------------------------------------------------------------------------------------------------
Dann Corbit wrote:

-----Original Message-----
From: Jimmie H. Apsey [mailto:japsey@futuredental.com] 
Sent: Tuesday, December 14, 2004 1:18 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance differences 7.1 to 7.3

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"?

The command syntax:
http://www.postgresql.org/docs/current/static/sql-explain.html
A brief explanation:
http://www.freebsddiary.org/postgresql-analyze.php
A longer explanation:
http://www.postgresql.org/docs/7.4/interactive/performance-tips.html
A nuts and bolts look at how PostgreSQL performs queries:
http://candle.pha.pa.us/main/writings/pgsql/performance.pdf

<<

Re: Performance differences 7.1 to 7.3

From

Scott Marlowe

Date:

15 December 2004, 02:10:12

On Tue, 2004-12-14 at 16:50, Jimmie H. Apsey wrote:
> I think my indexes are OK.
>
> I have no settings for 'shared memory buffers' in postgresql.conf on
> either system.  Both systems have same, i.e.
> #sort_mem = 512
> #shared_buffers = 2*max_connections # min 16
> #fsync = true
>
> Do you, or any of you, have any other suggestions for why such a
> simple "select count(*) from tpv;" should take six times as long on
> our latest and greatest dual processor server with almost the latest
> and greatest postgresql?  View tpv is a three table join which takes
> 3.38 seconds on the 'old' system and 18.09 seconds on the 'new'
> system.

You really need to do an "explain analyze <query>" where <query> is the
query used to create the view.  Post the output of that here.  My guess
is one is using indexes to match up rows, the other is using seq scans.
Or something like that.

Re: Performance differences 7.1 to 7.3

From

Tom Lane

Date:

15 December 2004, 02:19:10

"Jimmie H. Apsey" <japsey@futuredental.com> writes:
> On the 'old' Red Hat AS 2.1 here is the results of explain and the query:

The major problem seems to be that the old system is using a nestloop
with inner indexscan on ada_code:

>   ->  Nested Loop  (cost=870.92..4563.01 rows=342 width=56)
>         ...
>         ->  Index Scan using ada_code_pkey on ada_code  (cost=0.00..2.01 rows=1 width=12)

where the new system is using an inner seqscan:

>    ->  Nested Loop  (cost=6262.46..202496.78 rows=12948 width=45)
>          Join Filter: ("outer".service_code = ("inner".ada_code)::text)
>          ...
>          ->  Seq Scan on ada_code  (cost=0.00..10.06 rows=406 width=9)

The planner is well aware that this is a bad plan (note the much higher
cost estimate) --- I can only suppose that it is not able to select an
indexscan, most likely because of a datatype compatibility problem.
The cast to text appearing in the join condition is a tad suspicious
in this context.  What are the data types of service_code and ada_code,
and why aren't they the same?

IIRC, 7.3 is a lot less cavalier than 7.1 about the semantic differences
between char(n) and varchar(n)/text comparisons.  It's fairly likely
that the 7.1 plan is playing fast and loose with the comparison
semantics in order to generate an indexscan plan.  7.3 won't do that.
You need to make the column types the same to get good performance in
7.3 ... but if this is a foreign-key-reference arrangement, they ought
to be the same anyway.

            regards, tom lane

Re: Performance differences 7.1 to 7.3

From

Neil Conway

Date:

15 December 2004, 03:35:39

On Mon, 2004-12-13 at 17:43 -0500, Jimmie H. Apsey wrote:
> 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.

Have you run ANALYZE recently?

If so, take a look at the query plans produced by 7.1 and 7.3 (Using
EXPLAIN and EXPLAIN ANALYZE). Likely the planner is making an incorrect
decision -- EXPLAIN should help you figure out why. You can also post
the EXPLAIN / EXPLAIN ANALYZE output to the list and someone can give
you some advice. For more info:

http://www.postgresql.org/docs/7.4/static/performance-tips.html#USING-EXPLAIN

-Neil

Re: Performance differences 7.1 to 7.3

From

"Jimmie H. Apsey"

Date:

15 December 2004, 21:17:10

Thank you Tom, your suggestion was exactly what I needed.

Two tables in view "tpv" were being joined on a column with different data types.
One was "text" and the other one was "varchar(10)". The 'old' system did not complain.
The 'new' system does not allow this sloppyness on my part. When I made both table columns
the same, i.e. "varchar(10)" the time to execute the command became less on the 'new' system than on the 'old' system.

Then I went to postgresql.org to make a contribution but the PayPal thing ALWAYS trips me up.
When this PayPal thing is resolved, I will make a contribution.
It would be much better if postgresql.org accepted credit cards rather than PayPal.

Anyway, I am most grateful to this pgsql_general list for answering my questions which are often 'stupid' and you will soon
see my contribution.

Thank you,

Jim Apsey
-------------------------------------------
Tom Lane wrote:

"Jimmie H. Apsey" <japsey@futuredental.com> writes:

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

The major problem seems to be that the old system is using a nestloop
with inner indexscan on ada_code:

  ->  Nested Loop  (cost=870.92..4563.01 rows=342 width=56)       ...       ->  Index Scan using ada_code_pkey on ada_code  (cost=0.00..2.01 rows=1 width=12)

where the new system is using an inner seqscan:

   ->  Nested Loop  (cost=6262.46..202496.78 rows=12948 width=45)        Join Filter: ("outer".service_code = ("inner".ada_code)::text)        ...        ->  Seq Scan on ada_code  (cost=0.00..10.06 rows=406 width=9)

The planner is well aware that this is a bad plan (note the much higher
cost estimate) --- I can only suppose that it is not able to select an
indexscan, most likely because of a datatype compatibility problem.
The cast to text appearing in the join condition is a tad suspicious
in this context.  What are the data types of service_code and ada_code,
and why aren't they the same?

IIRC, 7.3 is a lot less cavalier than 7.1 about the semantic differences
between char(n) and varchar(n)/text comparisons.  It's fairly likely
that the 7.1 plan is playing fast and loose with the comparison
semantics in order to generate an indexscan plan.  7.3 won't do that.
You need to make the column types the same to get good performance in
7.3 ... but if this is a foreign-key-reference arrangement, they ought
to be the same anyway.
		regards, tom lane